|
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' ,"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. 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'';';
The function call :
EXECUTE request; 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 De : Tom Lane <tgl@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxxxxx> 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 |
