Failed to load CSV file which as line break in quoted column

Comments

• Navin_C Vertica Customer ✭

  August 2013

  Hello Vijay, Welcome to Vertica forums. The solution to your problem can be in tow steps. Step 1 If you file is as mentioned pattern, then you need a record terminator on every alternate line Either you can do this in Linux using a simple awk command

    1, "1289 cobb parkway   Bufford", "ATLANTA",34343  2, "1234 IVY lane   Decatur", "ATLANTA",23435    awk '!(NR%2){$(NF+1)=";"}1' filename    1, "1289 cobb parkway   Bufford", "ATLANTA",34343;  2, "1234 IVY lane   Decatur", "ATLANTA",23435;  

  I have placed a record terminator ';' on every alternate line. Step2 Now coming to Vertica, as you have record terminator defined in the csv file now, you can use the record terminator function with the COPY command this way.

    COPY tempdb.test_cust  FROM LOCAL 'test.dat'  DELIMITER ','  RECORD TERMINATOR ';'  ENCLOSED BY '"'  NO ESCAPE  SKIP 1  DIRECT;    

  This should do the trick for you. Note - If doing this in two step might not be a good idea, then there is a package called "External Filter" on Github which we can use to do this in a single COPY command. With external filter you have full access to command line tools while using the COPY command. Hope this helps..

  0
• njvijay

  August 2013

  Thanks Navin. I looked at record set. Not all quoted strings have line break. Some records have multiple line break in quoted string. I thought "enclosed by" phrase would not consider \n as record delimiter. Is there any other alternative?

  0
• [Deleted User]

  August 2013

  Hi, "Enclosed by" with the default DELIMITED parser does not enclose record terminators. (There are reasons for this involving some interesting bits of cleverness; unfortunately I can't go into more detail right at the moment.) However, you can escape newlines. Add a backslash right before each newline (so as the last character on the line), rather than surrounding it with quotes. Alternatively, you can tell Vertica to use use a different parser. If you're comfortable compiling source code, our SDK examples (installed to /opt/vertica/sdk/examples/ by default) includes the Rfc4180 parser, which parses CSV files formatted according to the IETF RFC-4180 standard (including enclosed record terminators among various other things). After you've loaded the parser into Vertica, you call it from the COPY statement with "COPY ... WITH PARSER [new parser that you want to use]"; see the .sql files in the above directory for examples. Yes, we know this process could be simpler :-) Adam

  0
• njvijay

  August 2013

  Adam, I could compile SDK examples and use RFC4180 Parser. Thanks for your help.

  0