Load CSV file with quotes and commas

[tommaso26]

Hello,

I am having some problems when loading a .csv file. The file has different columns and values are separated by commas. Some fields contain text values that are enclosed in double quotes because they contain commas related to the text and shouldn't be seen as a delimiter.

Example of a row in .csv file for 8 different fields:

215,31,"Sport shoes, good for running",Sport-100,"Universal fit, well-vented, lightweight.",Black,12.0278,33.6442

I have troubles when loading this into Qlik sense because it does not recognize the quotes as a grouping symbol for the text fields and I get the data split at every comma, so I obtain 11 fields insted of 8.

I tried to manipulate the data source using single quotes insted of double quotes but it doesn't work either.

I use the following to load the table:

LOAD
         *all the necessary fields*
FROM [lib://DataFiles/table.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Is there a way to manage this situtation in Qlik Sense?

Thank you very much!

[anthonyj]

Hi @tommaso26 ,

I haven't been able to replicate your issue with the reading of the table. I've used the same codepage and a copy of the line you supplied but it is allocating correctly.

I tried adding an extra line and it only broke when I added a comma to a field that didn't have the double quotes.

I don't know how many rows there are in your data but I wonder if there's a row that has a comma included in the column that is not surrounded by double quotes.

Regards

Anthony

[tommaso26]

Hi @anthonyj ,

thank you for the answer.

My input file has approx. 1000 records so I tried to load just the first row as in your example, however I am still having the same problem loading the .csv file.

My input file:

Loading:

Output:

I also tried to use the SUBFIELD function to manually split the data into the right fields, but it doesn't work either and it is not the optimal solution because the number of commas within the "_" may change for different records.

Thank you

[anthonyj · Accepted solution]

Hi,

Instead of opening the file in excel. Right click and open in Notepad. Save the file again but choose UTF-8 as the encoding.

I noticed when I did this excel correctly allocated the columns and so did Qlik.

Thanks

Anthony

[tommaso26]

Hi @anthonyj ,

Thank you very much, it actually worked!

I think the format of the orginal .csv file was not correct somehow.

Thank you,

Tommaso