sed sur un CSV [Résolu]

[xoum89]

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.

[zipe31]

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","-","-","-","-","-","-","-","-","-");
$

[xoum89]

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

);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 :

"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

[FRUiT]

Code awk :

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.

[xoum89]

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

[zipe31]

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.

[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
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

[FRUiT]

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.

[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.

[zipe31]

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 :

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

[FRUiT]

Code awk :

awk ... plop

Pour chaque ligne du fichier plop,

Code awk :

'{ print "INSERT INTO matable VALUES ("

écrire la string "INSERT INTO mytable VALUES("

Code awk :

$0

suivie de la ligne entière en question

Code awk :

");" }'

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.

[xoum89]

Merci beaucoup à vous deux.

Bonne journée.

[Alek-C]

Question con : tu n'as pas un utilitaire avec ta bdd qui permettrait de faire ça proprement ?

[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

[Alek-C]

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...