How To import CSV Data File contain comma and apostrophe

[mohdhaniff]

• I'm having issue on import data from csv with value contains single quotes and comma.
• Can anybody help me on how to get data imported into Qlik View same as data available in original source (csv)?

Sample.:

** Refer to attachment

• I already try to import by using quotation msq and No Quotes but still getting incorrect data imported:

1. msq script and result:

LOAD * FROM

C:\Users\b8p8mhh\Desktop\data.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Result: CUSTOMER_NAME with apostrophe incorrect due to there are another field also contains apostrophe (ADD_LINE_2)

2. No Quotes script and result:

LOAD * FROM

C:\Users\b8p8mhh\Desktop\data.csv

(txt, codepage is 1252, embedded labels, delimiter is ',', no quotes);

Result: incorrect data displayed as some of the fields contain comma

[mohdhaniff · Accepted solution]

Hi everybody,

Thanks for contributing your ideas.

I believe that QV not really support csv files that contains apostrophe + comma in multiple fields. It got conflict to read comma as delimiter and apostrophe as quotation at the same time.

Therefore, I already change my data as text (tab delimited) and perform the sql as below:

LOAD * FROM

C:\Users\b8p8mhh\Desktop\data.csv (txt, codepage is 1252, embedded labels, delimiter is '\t', no quotes);

With this solution, all data are correctly uploaded in QV as per original source.  Replace or Remove the apostrophe / comma with other characters are not required.

Hope this can help others.

Regards.

[mohdhaniff]

Thanks Varsha,

Interesting and nice trick.

Even though I can't use it into my actual data due to exceed Excel maximum row (> 1.8m rows) but I learn new skill.

[varshavig12]

You're welcome