Fastloading csv file with double quotes

Tools
Enthusiast

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?

4 REPLIES
Teradata Employee

Re: Fastloading csv file with double quotes

If you are using 14.00 or later, you can have fastload using following syntax:

SET RECORD VARTEXT DELIMITER ';' QUOTE YES '"';

Enthusiast

Re: Fastloading csv file with double quotes

Hi Sudhansu,

thank you very much.

We installed the version some days ago. This is great news.

Koen.

Enthusiast

Re: Fastloading csv file with double quotes

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?

Teradata Employee

Re: Fastloading csv file with double quotes

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 '*';