Thanks,
As it's write in the page, the file format is more a convention than a standard.
With collegues, we've find the solution with a regex in sed
sed -e 's/\([^,]\)"\([^,]\)/\1\2/g' -e 's/\([^,]\)"\([^,]\)/\1\2/g'
Because file contains values like
,"hostname1.fqdn.ad|\"\"\"\"\"\"\"\"\"\"0Clean|OK"
But, other csv file, that contains this value ","NAME=\"UBUNTU\"","| works well. I find this value in destination column |NAME=\UBUNTU\| in the destination table.
The main lines in the plpgsql function are :
CREATE OR REPLACE FUNCTION insert_into_db_sources_csv(
filename character varying,
tablename character varying,
delimiter character varying,
header character varying)
RETURNS void AS
....
request := 'TRUNCATE ' || tablename || '; COPY ' || tablename || ' FROM ''' || filename || ''' CSV ' || header || ' DELIMITER ''' || delimiter || ''' ENCODING ''UTF-8'';';
EXECUTE request;
The function call :
select insert_into_db_sources_csv('/DATA/input/files/Extract_software.csv', 't_m03_software', ',', 'HEADER');
If the import fails, TRUNCATE isn't executed. The previous data's that was in table remains the same.
_________________________________Cordialement, Pascal CROZET
DBA • www.qualis-consulting.com • 04 78 22 74 90
• Le Bois des Côtes 1 – Bâtiment A
• 300 Route Nationale 6 – 69760 LIMONEST
_________________________________
De : Tom Lane <tgl@sss.pgh.pa.us>
Envoyé : jeudi 10 octobre 2019 00:31
À : PASCAL CROZET
Cc : PG-General Mailing List
Objet : Re: plpgsql copy import csv double quotes
PASCAL CROZET <pascal.crozet@qualis-consulting.com> writes:
> I’ve experience issues with double quotes \34 inside fields, in a csv file.
> Ex :
> "value1","some text","other text with "double quotes" inside","last field"
I don't know of any definition of CSV format by which that's legal data.
The typical rule is that double quotes that are data must be doubled;
at least, that's what COPY expects by default. You can also get COPY
to handle variants like backslash-quote.
regards, tom lane