Discussion :

[xoum89]

Bonsoir,

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

En fessant un

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
sed 's/^"/INSERT INTO maTable VALUES ("/' monFichier.CSV > fichierVierge.SQL

Celle qui ne fonctionne pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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.

[FRUiT]

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.

[FRUiT]

Code awk :

Sélectionner tout - Visualiser dans une fenêtre à part

awk ... plop

Pour chaque ligne du fichier plop,

Code awk :

Sélectionner tout - Visualiser dans une fenêtre à part

'{ print "INSERT INTO matable VALUES ("

écrire la string "INSERT INTO mytable VALUES("

Code awk :

Sélectionner tout - Visualiser dans une fenêtre à part

$0

suivie de la ligne entière en question

Code awk :

Sélectionner tout - Visualiser dans une fenêtre à part

");" }'

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.

[lennelei]

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