All Forums Tools
Koentje 23 posts Joined 09/04
08 Apr 2014
Fastloading csv file with double quotes

How are you fast loading a csv file where the fields are enclosed by double quotes?
Eg.
“Field 1”;”Field 2”;”Field 3”
“Fi””eld 1”;”Field “”2”””;”Field 3”
What is the method you’re using?

SuSeSi 61 posts Joined 10/08
14 Apr 2014

If you are using 14.00 or later, you can have fastload using following syntax:
SET RECORD VARTEXT DELIMITER ';' QUOTE YES '"';
 

-Sudhansu

Koentje 23 posts Joined 09/04
15 Apr 2014

Hi Sudhansu,
thank you very much.
We installed the version some days ago. This is great news.
Koen.
 

Koentje 23 posts Joined 09/04
29 Apr 2014

After testing with data, we encountered following situation:
Snapshot of input data:

"D9F570F6-538B-4795-98A4-021D99C03576"|"""BUREAU,TARIFICATION"""
"DAA62C82-6BA5-4034-AAFE-0A2BC825903C"|"""BUREAU,TARIFICATION"""
"838858AF-9D8C-47CA-A3FA-23F4AC95A42C"|"""BUREAU,TARIFICATION"""

These rows were rejected due to the fact that the data itself contains double quotes (this is following the standards of quoted data).

**** 09:28:27 Error at record number 5
"947FB070-A010-4A5D-A147-4502F3A072ED"|"""BUREAU,TARIFICATION"""
**** 09:28:27 Error on piom GET ROW: 64, Text: Quoted data- Column
              missing close quote !ERROR! Delimited Data Parsing error:
              Delimiter did not immediately follow close quote mark in
              row 6, col 1

What do I miss?

SuSeSi 61 posts Joined 10/08
02 May 2014

Fastload does not support or can ignore quoting character as part of data. The csv file while saving by different application adds additinal quotes for their application differently.
I will suggest in this case to use a shell/perl script to
1. change the quoted character ' " '
2. change the two double quote character which represent a single quote in data to one.
the data:
"D9F570F6-538B-4795-98A4-021D99C03576"|"""BUREAU,TARIFICATION"""
becomes:
*D9F570F6-538B-4795-98A4-021D99C03576*|*"BUREAU,TARIFICATION"*
then use fastload command as:
SET RECORD VARTEXT DELIMITER '|' QUOTE YES '*';

 

-Sudhansu

You must sign in to leave a comment.