Nous utilisons des cookies pour vous garantir la meilleure expérience sur notre site. Si vous continuez à utiliser ce dernier, nous considèrerons que vous acceptez l'utilisation des cookies. J'ai compris ! ou En savoir plus !.
banniere

Le portail francophone de la géomatique


Toujours pas inscrit ? Mot de passe oublié ?
Nom d'utilisateur    Mot de passe              Toujours pas inscrit ?   Mot de passe oublié ?

Annonce

Suite à un problème technique intervenu entre le 22 et le 23 mars, nous avons du procéder dans la soirée du 25 mars, à la restauration de la base de données du 24 mars (matinée).

En clair, nous avons perdu vos contributions et inscriptions du dimanche 24 et du lundi 25 mars.
Nous vous prions de nous excuser.

#1 Tue 26 February 2019 14:49

Lsam
Participant assidu
Date d'inscription: 27 Nov 2013
Messages: 157

[PostgreSQL] import de données avec valeurs de champs multilignes

Bonjour,

Avec psql, je n'arrive pas à importer des données au format SQL avec des champs texte comportant des retours à la ligne. Voici la commande utilisée :

Code:

PGOPTIONS='-c search_path=schema -c synchronous_commit=off' psql -d base -f données.sql

Je ne comprends pas pourquoi, ces valeurs de champs sont pourtant bien délimitées par des guillemets simples.

Voici où échoue psql dans le fichier SQL :

INSERT INTO "TCCitations" ("Unique_Inventaire", "Unique_Citation", "Classe", "Espèce", "Sexe", "Age", "NbrInd", "NbrVol", "Distance_Contact", "Précision_NbrInd", "Qualité", "Commentaire", "Origine_Statut_Reproduction", "Statut_Reproduction", "Statut_Doublon", "Espece_Confirmee_par_observateur", "Présence_Régulière_Période_Nidification", "Déterminateur", "Référence_Import", "Num_Groupe", "Antériorité_indice", "Origine_Antériorité_indice", "Absence") VALUES (18098,58293,'O','LOXCUR','?','VOL',14,NULL,NULL,'0','N','Au - trois mâles avec des barres alaires blanchâtres assez étroites mais très nettes.
Se nourrissent
de graines d''Epicea dans les jardins et parcs du secteur.',1,0,0,0,0,NULL,NULL,NULL,0,1,0);


Je ne me souviens pas avoir rencontré ce problème auparavant alors que je manipule souvent les mêmes types de données. Une petite astuce pour m'éviter d'utiliser sed ? Car l'élimination des retours à la ligne prend énormément de temps sur ce fichier de 827Mo sur mon petit Raspberry Pi 3.

Merci.

LSam

Hors ligne

 

#2 Tue 26 February 2019 16:04

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [PostgreSQL] import de données avec valeurs de champs multilignes

Salut,
pouvez vous poster le message d'erreur,
et quelques elements de contexte telles que :
origine du fichier données.sql?
si oui avec quelle version de postgresql ?
Version du serveur de postgresql  ou vous voulez importer les données ?
encodages des bases et du fichier sql?

Merci.

Dernière modification par tumasgiu (Tue 26 February 2019 16:12)

Hors ligne

 

#3 Tue 26 February 2019 16:16

Lsam
Participant assidu
Date d'inscription: 27 Nov 2013
Messages: 157

Re: [PostgreSQL] import de données avec valeurs de champs multilignes

Le message d'erreur n'était pas clair :

cannot allocate memory for output buffer


J'ai simplement trouvé en regardant combien de données étaient importées avant l'erreur et en regardant quelle pouvait en être l'origine à la ligne correspondante dans le fichier SQL.

Ce fichier SQL a été créé à partir d'une base Access grâce à l'outil mdb-export (du projet MDB Tools) :

Code:

mdb-export -q "'" -D '%F %T' -H -I postgres Base.MDB > données.sql

Le fichier généré est en UTF-8, de même que ma base. La version du serveur PostgreSQL est 9.6.11.

Merci.

[Edit] ça fait 1h30 que sed tourne sur le fichier pour remplacer les retours à la ligne à l'intérieur des champs, et ce n'est pas fini...

Dernière modification par Lsam (Tue 26 February 2019 16:19)

Hors ligne

 

#4 Tue 26 February 2019 16:55

Lsam
Participant assidu
Date d'inscription: 27 Nov 2013
Messages: 157

Re: [PostgreSQL] import de données avec valeurs de champs multilignes

J'ai finalement trouvé une solution plus simple avec sed.

En effet le fichier SQL généré par mdb-export comporte des commandes INSERT INTO séparées par des points-virgules, et des sauts de ligne (LF).

Les retours à la ligne à l'intérieur des champs sont en fait des retours chariots (CR, code hexadécimal : 0d) suivis de sauts de ligne (LF, code hexadécimal : 0a).

Donc il est alors simple de remplacer les CR-LF par des CR uniquement avec sed :

Code:

sed -i 's/\x0d\x0a/\x0d/g' données.sql

Ça ne prend que 7 minutes, ça me va. Mais s'il y a une autre solution, je suis preneur. L'import se passe bien dorénavant avec psql.

LSam

Hors ligne

 

#5 Tue 26 February 2019 17:33

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [PostgreSQL] import de données avec valeurs de champs multilignes

Il y a aussi dos2unix pour convertir les sauts de lignes DOS en saut de ligne unix.

Ou tr :

Code:

tr -d '\r' < dosfile > unixfile

C'est plutôt étrange, je n'arrive pas à reproduire.
De plus le message est apparemment sans rapport direct avec le style de saut de ligne.
Et si vous n'importez qu'un sous ensemble de vos données ?
C'est possible d'avoir le fichier ? Ou un extrait ?

Sinon peut être que vous pourriez accélérer votre import,
en vous servant  de la commande copy, comme suit :

Code:

tr -d '\r' < donnees.sql | psql [mes options de connexion] -c "COPY matable FROM STDIN CSV HEADER"

Le seul préalable est de convertir votre base MDB en CSV plutôt qu'en DDL,
et de lancer une requête au préalable pour créer la table avant la copie.

Merci pour la découverte de mdb-tools, je ne connaissais pas.

Dernière modification par tumasgiu (Tue 26 February 2019 17:42)

Hors ligne

 

#6 Tue 26 February 2019 17:47

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [PostgreSQL] import de données avec valeurs de champs multilignes

Vous pouvez aussi carrément zapper l’écriture intermédiaire dans données.sql :

Code:

mdb-export -q "'" -D '%F %T' -H postgres Base.MDB \
| tr -d '\r' \
| psql [mes options de connexion] -c "COPY matable FROM STDIN CSV"

Dernière modification par tumasgiu (Tue 26 February 2019 17:51)

Hors ligne

 

#7 Wed 27 February 2019 10:18

Lsam
Participant assidu
Date d'inscription: 27 Nov 2013
Messages: 157

Re: [PostgreSQL] import de données avec valeurs de champs multilignes

Ok merci pour l'astuce,

Mais votre commande tr supprime tous les retours chariots. Or je veux supprimer les sauts de ligne au sein des champs dans le fichier généré par mdb-export, pour qu'ils soient bien différenciés de ceux séparant les commandes. Je garde les retours-chariots. Du coup :

Code:

sed -E 's/\r\n/\r/'

Et la commande COPY importe un fichier CSV alors que mdb-export exporte ici au format SQL. Donc il faut simplement ôter l'option -I postgres de mdb-export.

Ce qui donnerait au final (merci de me corriger éventuellement) :

Code:

mdb-export -D '%F %T' -H Base.MDB \
| sed -E 's/\r\n/\r/'
| psql [mes options de connexion] -c "COPY matable FROM STDIN CSV"

Sinon, j'ai déjà crée la table au préalable.

Merci !

Dernière modification par Lsam (Wed 27 February 2019 10:26)

Hors ligne

 

#8 Wed 27 February 2019 12:17

tumasgiu
Membre
Lieu: Ajaccio
Date d'inscription: 5 Jul 2010
Messages: 1129

Re: [PostgreSQL] import de données avec valeurs de champs multilignes

La commande a l'air correcte.
Simple précision, mais dans la commande COPY,
les colonnes sont omises donc on postule que l'ordre
des colonnes de l'export mdb-export et de votre table postgres
sont les même.

La commande tr enlève les retour chariots,
pour être conforme aux sauts de lignes unix.
Pour rappel, pour effectuer un saut de ligne,
selon l'OS :

Code:

OSX:  \r 
DOS: \r\n
*NIX: \n

Dernière modification par tumasgiu (Thu 28 February 2019 15:56)

Hors ligne

 

Pied de page des forums

Powered by FluxBB