Delimiting CSV file

[#1]

Hi,

I have a CSV file with comma delimited headers.

However the data is delimited with commas AND quotation marks.

Within the quotation marks sometimes commas are used, which makes it complicated.

For instance in the below example hsjshskh 8267282 will be identified as DATE

AMOUNT, TEXT, DATE;;

""999999"", ""XXXX uerururur 82726353, hsjshskh 8267282"", ""2015/01/01"";;

This is from the attached example file.  All of the text should be in "DESCRIPTION".

Pls find attached.

Any ideas how to come around this?

[jonathandienst · Accepted solution]

Normally the quotes rules (like msq) will handle the commas embedded in quotes correctly and will not see them as field delimiters. You may have a problem with the double quotes in your data as the second quote in the double quote closes the quote, so QV no longer sees the comma embedded in a string and treats as a field delimiter.

Is it possible to adjust your data source so that it no longer double quotes the fields?

Other suggestions:

• Preprocess the file to remove the double quotes. Some Powershell or external vbscript that executes on the file before QV loads it.
• Load the entire record line into a single field, then manipulate the line and perform a LOAD...FROM_FIELD to load data into the correct field names.