Le portail francophone de la géomatique

Lsam · Tue 26 February 2019 14:49

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

tumasgiu · Tue 26 February 2019 16:04

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)

Lsam · Tue 26 February 2019 16:16

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)

Lsam · Tue 26 February 2019 16:55

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

tumasgiu · Tue 26 February 2019 17:33

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)

tumasgiu · Tue 26 February 2019 17:47

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)

Lsam · Wed 27 February 2019 10:18

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)

tumasgiu · Wed 27 February 2019 12:17

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)

Communauté	Ressources	L'emploi	Le marché	L'association	Fils RSS
Forum Blogs Wiki Planet Sites amis	Agenda Biblio Fiches AMO-CNIG Paris PCGIS Téléchargements	Offres d'emploi Banque de CV Formations Démarche Métiers	Geo-Entreprises Geo-Communiqué Appels d'offres	L'association Qui sommes-nous ? Nous aider Bienfaiteurs Partenaires Historique Statuts	Tout le site Job Géomatique Services GeoBlogs ...	Faire un don Mentions légales

Le portail francophone de la géomatique

Annonce

#1 Tue 26 February 2019 14:49

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

Code:

#2 Tue 26 February 2019 16:04

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

#3 Tue 26 February 2019 16:16

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

Code:

#4 Tue 26 February 2019 16:55

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

Code:

#5 Tue 26 February 2019 17:33

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

Code:

Code:

#6 Tue 26 February 2019 17:47

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

Code:

#7 Wed 27 February 2019 10:18

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

Code:

Code:

#8 Wed 27 February 2019 12:17

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

Code:

Pied de page des forums