sed sur un CSV

Version imprimable

30/03/2011, 21h30
xoum89

sed sur un CSV
Bonsoir,

J'ai un fichier très lourd de plus de 100 mo en CSV.

En fessant un

Code:

sed 1q

sur mon fichier voici ce que j'ai

"0","-","-","-","-","-","-","-","-","-"

Toutes les lignes son pareil avec des donnée différent bien entendu :).

Je voudrai les insérer dans une base de donnée donc voila mes commandes que j'ai faite et qui ne fonctionne pas toutes très bien.
La première fonctionnel :
Code:

1 2 sed 's/^"/INSERT INTO maTable VALUES ("/' monFichier.CSV > fichierVierge.SQL
Celle qui ne fonctionne pas :
Code:

1 2 sed 's/"$/");/' monFichier > fichierVierge
Visiblement je n'arrive pas à trouver le dernier caractère de chaque ligne qui correspond à mon fichier CSV, puisque aucun remplacement ne s'effectue.

Même si avant je fais un cat monFichier.CSV > fichier.sql cela ne change rien.

Merci de votre aide.

Edit : peut être que vous avez un moyen plus simple pour rentrer ce gros fichier dans une base de donnée.
Mais j'ai pensé à celle là qui est assez rapide si elle marche.

Salut,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
$ cat plop
"0","-","-","-","-","-","-","-","-","-"
"0","-","-","-","-","-","-","-","-","-"
"0","-","-","-","-","-","-","-","-","-"

$ sed -i.bak 's/.*/INSERT INTO maTable VALUES (&);/' plop

$ cat plop
INSERT INTO maTable VALUES ("0","-","-","-","-","-","-","-","-","-");
INSERT INTO maTable VALUES ("0","-","-","-","-","-","-","-","-","-");
INSERT INTO maTable VALUES ("0","-","-","-","-","-","-","-","-","-");
$

;)

30/03/2011, 23h10
xoum89

Merci de ta réponse rapide mais je me retrouve pour les 10 premieres lignes avec :

Citation:

);SERT INTO maTable VALUES ("0","255","-","-","-","-","-","-","-","-"
);SERT INTO maTable VALUES ("256","16777215","-","-","-","-","0.000000","0.000000","-","-"
INSERT INTO maTable VALUES ("16777216","16777471","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+);:00"
);SERT INTO maTable VALUES ("16777472","16843007","-","-","-","-","0.000000","0.000000","-","-"
INSERT INTO maTable VALUES ("16843008","16843263","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+);:00"
);SERT INTO maTable VALUES ("16843264","16909055","-","-","-","-","0.000000","0.000000","-","-"
INSERT INTO maTable VALUES ("16909056","16909311","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+);:00"
);SERT INTO maTable VALUES ("16909312","17039359","-","-","-","-","0.000000","0.000000","-","-"
INSERT INTO maTable VALUES ("17039360","17039615","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+);:00"
);SERT INTO maTable VALUES ("17039616","17367039","-","-","-","-","0.000000","0.000000","-","-"

Si je te fais un sed 10q sur le fichier d'origine cela me donne :

Citation:

"0","255","-","-","-","-","-","-","-","-"
"256","16777215","-","-","-","-","0.000000","0.000000","-","-"
"16777216","16777471","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"
"16777472","16843007","-","-","-","-","0.000000","0.000000","-","-"
"16843008","16843263","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"
"16843264","16909055","-","-","-","-","0.000000","0.000000","-","-"
"16909056","16909311","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"
"16909312","17039359","-","-","-","-","0.000000","0.000000","-","-"
"17039360","17039615","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"
"17039616","17367039","-","-","-","-","0.000000","0.000000","-","-"

Merci de ton aide

Code:

1
2
3
4
> awk '{ print "INSERT INTO matable VALUES ("$0");" }' plop
INSERT INTO matable VALUES ("0","-","-","-","-","-","-","-","-","-");
INSERT INTO matable VALUES ("0","-","-","-","-","-","-","-","-","-");
INSERT INTO matable VALUES ("0","-","-","-","-","-","-","-","-","-");

Ca marche aussi sur l'extrait réel de 10 lignes que tu as fourni.

30/03/2011, 23h50
xoum89

Merci FRUIT mais je ne connais vraiment rien avec la commande awk.

Voila ce que j'obtiens sur ton exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
$ sed 's/.*/INSERT INTO maTable VALUES (&);/' plop
INSERT INTO maTable VALUES ("0","255","-","-","-","-","-","-","-","-");
INSERT INTO maTable VALUES ("256","16777215","-","-","-","-","0.000000","0.000000","-","-");
INSERT INTO maTable VALUES ("16777216","16777471","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00");
INSERT INTO maTable VALUES ("16777472","16843007","-","-","-","-","0.000000","0.000000","-","-");
INSERT INTO maTable VALUES ("16843008","16843263","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00");
INSERT INTO maTable VALUES ("16843264","16909055","-","-","-","-","0.000000","0.000000","-","-");
INSERT INTO maTable VALUES ("16909056","16909311","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00");
INSERT INTO maTable VALUES ("16909312","17039359","-","-","-","-","0.000000","0.000000","-","-");
INSERT INTO maTable VALUES ("17039360","17039615","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00");
INSERT INTO maTable VALUES ("17039616","17367039","-","-","-","-","0.000000","0.000000","-","-");

Peux-tu afficher le résultat de la commande "cat -A ton_fichier_exemple" s'il te plaît, merci.

Donc avec la commande cat -A en en arrêtant peu de temps après l’exécution car il doit y avoir plus de 1 million de ligne voici les nouvelle lignes qui apparaisse :

Code:

1
2
3
4
5
6
7
8
9
10
"0","255","-","-","-","-","-","-","-","-"^M$
"256","16777215","-","-","-","-","0.000000","0.000000","-","-"^M$
"16777216","16777471","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"^M$
"16777472","16843007","-","-","-","-","0.000000","0.000000","-","-"^M$
"16843008","16843263","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"^M$
"16843264","16909055","-","-","-","-","0.000000","0.000000","-","-"^M$
"16909056","16909311","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"^M$
"16909312","17039359","-","-","-","-","0.000000","0.000000","-","-"^M$
"17039360","17039615","AU","AUSTRALIA","QUEENSLAND","MILTON","-35.316667","150.433333","-","+10:00"^M$
"17039616","17367039","-","-","-","-","0.000000","0.000000","-","-"^M$

Comment je peux l'ajouter a la commande ?

Merci de ton aide

31/03/2011, 15h20
FRUiT

Citation:

Envoyé par xoum89

Merci FRUIT mais je ne connais vraiment rien avec la commande awk.

De toute façon avec les retours chariots microsoft ^M, elle ne marche pas mieux que le sed...

Il faudrait préalablement traiter ton fichier avec par exemple ceci ou cela
Oublie pas de faire des backups.
31/03/2011, 15h24
xoum89

A mais je ne dis pas le contraire mais c'était juste pour te dire qu'il faudrait que tu m'explique un peu plus comment ta commande fonctionne car je ne connais rien.
31/03/2011, 15h26
zipe31
Citation:
Envoyé par xoum89

Donc avec la commande cat -A en en arrêtant peu de temps après l’exécution car il doit y avoir plus de 1 million de ligne voici les nouvelle lignes qui apparaisse :

Code:

1 2 "0","255","-","-","-","-","-","-","-","-"^M$ "256","16777215","-","-","-","-","0.000000","0.000000","-","-"^M$

Comment je peux l'ajouter a la commande ?

Merci de ton aide
La commande était là juste pour afficher les fins de ligne et constater que c'est des fins de ligne à la Micro$oft ;-((

Il faut convertir ton fichier en mode Unix avant de lancer les commandes (sed ou awk).
Voir la FAQ
31/03/2011, 15h29
FRUiT

Code:

awk ... plop

Pour chaque ligne du fichier plop,

Code:

'{ print "INSERT INTO matable VALUES ("

écrire la string "INSERT INTO mytable VALUES("

Code:

$0

suivie de la ligne entière en question

Code:

");" }'

suivie de la string ");"

Les accolades {} signifient de faire une action sur la ligne (ici ajouter du texte avant et après).

Les apostrophes simples ' ' délimitent le script awk à exécuter sur le fichier plop.
31/03/2011, 16h52
xoum89

Merci beaucoup à vous deux.

Bonne journée.
31/03/2011, 17h03
lennelei

Question con : tu n'as pas un utilitaire avec ta bdd qui permettrait de faire ça proprement ? :)
31/03/2011, 18h56
xoum89

Va faire cela sur une machine linux sans interface graphique ainsi qu'un fichier de plus de 100 MO

Au total plus de 2 million de lignes :)
01/04/2011, 13h27
lennelei

Ben justement, utiliser un truc comme mysqlimport sous MySQL ou autre me paraît vachement plus sûr que de transformer 2 millions de lignes avec sed pour en faire des INSERT... pour peu que tu aies un " qui traîne, un saut de ligne au mauvais moment ou que sais-je, ça va foirer... alors qu'un outil spécifique fera des contrôles à priori...