Need help in sorting out the following issue when attribute has a comma and enclosed with "" quotes.

srijanch0810

Hi, currently we are using vertica version 12.0.4 we are having a attribute with comma in between while we do export to delimited

EXPORT TO DELIMITED (directory='s3://{bucket}/vertica-data-exporter/5d35eaf2-2e34-4ee2-8007-0b7647e4281c_25a2c07b-bc8f-4ea7-847f-1e84a699667e_180050_broadcast_753_0_IDEN_whatsapp_false_false_true_webhook'  , filename = 'test', addHeader='true'  , enclosedBy='"' ,delimiter=',') AS SELECT * FROM srijan;

vertica db data

 select * from srijan;
  userid | mobile | email | foreignkey | att146
--------------------------------------------------
2792045|919344700896||919344700896|4,00,00
2792046|918870355952||918870355952|2,47,741

we get rows like this

[tmpsrijan.chakraborty@port3 ~]$ cat test.csv 
userid,mobile,email,foreignkey,att146
2792045,"919344700896","","919344700896","4\,00\,00"
2792046,"918870355952","","918870355952","2\,47\,741"

where column att146 has backslash before comma.
How to avoid this backslash? (version upgrade to other version is not the option telling in advance)

SruthiA

\ is used to to escape the comma character and it is expected. Are you facing issue to load it using COPY?

srijanch0810

The issue is while displaying the csv records the following attribute att146 is diplayed with backslash comma character. How to display the following att146 without backslash behind comma. ie. instead of 4\,00\,000 to 4,00,000 using export to delimited

mosheg

Try this:

create table srijan (userid int, mobile int, email varchar(50), foreignkey int, att146 varchar(10));

COPY srijan FROM STDIN DELIMITER '|' ABORT ON ERROR;
2792045|919344700896||919344700896|4,00,00
2792046|918870355952||918870355952|2,47,741
\.

select * from srijan order by 1;
 userid  |    mobile    | email |  foreignkey  |  att146
---------+--------------+-------+--------------+----------
 2792045 | 919344700896 |       | 919344700896 | 4,00,00
 2792046 | 918870355952 |       | 918870355952 | 2,47,741
(2 rows)

\! rm -f /YourPath/test.csv

EXPORT TO DELIMITED (
        directory='/YourPath',
        delimiter=',',
        escapeAs='',
        enclosedBy='"',
        addHeader='true',
        filename='test',
        nullAs='REALNULL',
        ifDirExists='overwrite')
AS SELECT * from srijan;

\! cat /YourPath/test.csv
userid,mobile,email,foreignkey,att146
2792045,919344700896,"REALNULL",919344700896,"4,00,00"
2792046,918870355952,"REALNULL",918870355952,"2,47,741"

truncate table srijan;

COPY srijan
     FROM '/YourPath/test.csv'
     ON v_eevdb_node0001
     SKIP 1
     DELIMITER ','
     ENCLOSED BY '"'
     null 'REALNULL'
     ABORT ON ERROR;

select * from srijan order by 1;
 userid  |    mobile    |  email   |  foreignkey  |  att146
---------+--------------+----------+--------------+----------
 2792045 | 919344700896 | REALNULL | 919344700896 | 4,00,00
 2792046 | 918870355952 | REALNULL | 918870355952 | 2,47,741
(2 rows)

Or use "|" as delimiter:
For example:

EXPORT TO DELIMITED (
        directory='/YourPath',
        delimiter='|',
        escapeAs='\',
        addHeader='true',
        filename='test',
        nullAs='REALNULL',
        ifDirExists='overwrite')
AS SELECT * from srijan;

cat \! cat /YourPath/test.csv
userid|mobile|email|foreignkey|att146
2792045|919344700896|REALNULL|919344700896|4,00,00
2792046|918870355952|REALNULL|918870355952|2,47,741