topic Re: How To import CSV Data File contain comma and apostrophe in QlikView

How To import CSV Data File contain comma and apostrophe

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How To import CSV Data File contain comma and apostrophe

hemanthaanichet — Wed, 09 Nov 2016 07:02:21 GMT

Hi Harun,

I think there is some problem with your data while converting it into CSV.

Too many commas and spaces and single quotes too.

I solved your requirement in different way

i just save your data sheet into xls format and i loaded into qlikview

i faced the same issue as u got like there is a Single quotes infront of the customer name

Below is the reference

LOAD UNQ_ID_SRC_STM,

CUSTOMER_NAME,

UNQ_ID_SRC_STM2,

OWNED_BY_BR,

APPNUM,

ADR_LNE_1,

ADR_LNE_2,

ADR_LNE_3,

ADR_LNE_4

FROM

(biff, embedded labels, table is data$);

Later i changed it while loading itself as by using repalce

CHR(39) is used for single quotes

Below is the script i used

LOAD UNQ_ID_SRC_STM,

REPLACE(REPLACE(REPLACE(CUSTOMER_NAME,' ',''),CHR(39),''),',' ,' ') AS CUSTOMER_NAME,

UNQ_ID_SRC_STM2,

OWNED_BY_BR,

APPNUM,

ADR_LNE_1,

ADR_LNE_2,

ADR_LNE_3,

ADR_LNE_4

FROM

(biff, embedded labels, table is data$);

I think this might be helpful to you .let me know if u have any further queries

Regards

Hemanth

Re: How To import CSV Data File contain comma and apostrophe

ramasaisaksoft — Wed, 09 Nov 2016 07:52:21 GMT

Hi Haniff,

i am unable to attach the file.

But this is manual work only.

Step 1:-go to your CSV file and ctrl+F ( ' ) Replace ' with space after that while fetching data from CSV file you already have , separated value

Click on Table file in Edit script--> then file wizard editor will open-->select first radio button on left hand side file type as Delimited-->Right hand side Delimiter drop down box will have 2nd option has Comma select that and click next .

now check the data after loading.

Re: How To import CSV Data File contain comma and apostrophe

varshavig12 — Wed, 09 Nov 2016 08:55:13 GMT

Although,

this might not be the correct way, but I have a workaround,

Re: How To import CSV Data File contain comma and apostrophe

mohdhaniff — Wed, 09 Nov 2016 09:04:13 GMT

Hi Rama,

Thanks for your suggestion.

I can't do the manual way as my actual data exceed available CSV row.

The continuous row will lost when I save the file.

Re: How To import CSV Data File contain comma and apostrophe

mohdhaniff — Wed, 09 Nov 2016 09:10:41 GMT

Hi Varsha,

Actually I am using QV Personal Edition users. Can you help me to copy the script

Thank You.

Re: How To import CSV Data File contain comma and apostrophe

simospa — Wed, 09 Nov 2016 09:41:15 GMT

Hi,

attached there is Vasha's load script

Re: How To import CSV Data File contain comma and apostrophe

varshavig12 — Wed, 09 Nov 2016 09:42:05 GMT

t1:

LOAD [@1:n] as a

FROM

(fix, codepage is 1252, embedded labels);

t2:

load

Replace(a, TextBetween(a,'"','"') ,Replace(TextBetween(a,'"','"'),',',' ')) as a1

resident t1;

drop table t1;

t3:

load subfield(a1,',',1) as UNQ_ID_SRC_STM,

purgechar(subfield(a1,',',2),'"') as CUSTOMER_NAME,

subfield(a1,',',3) as UNQ_ID_SRC_STM2,

subfield(a1,',',4) as OWNED_BY_BR,

subfield(a1,',',5) as APPNUM,

subfield(a1,',',6) as ADR_LNE_1,

subfield(a1,',',7) as ADR_LNE_2,

subfield(a1,',',8) as ADR_LNE_3,

subfield(a1,',',9) as ADR_LNE_4

Resident t2;

drop table t2;

Re: How To import CSV Data File contain comma and apostrophe

varshavig12 — Wed, 09 Nov 2016 09:47:01 GMT

Hello Haniff,

For t1, Instead of delimited , I have choose fixed record and renamed the header as @1.

Hope this helps!

Re: How To import CSV Data File contain comma and apostrophe

mohdhaniff — Thu, 10 Nov 2016 00:53:19 GMT

Thanks

Re: How To import CSV Data File contain comma and apostrophe

mohdhaniff — Thu, 10 Nov 2016 01:50:51 GMT

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.

Re: How To import CSV Data File contain comma and apostrophe

mohdhaniff — Thu, 10 Nov 2016 02:13:52 GMT

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.

Re: How To import CSV Data File contain comma and apostrophe

varshavig12 — Thu, 10 Nov 2016 06:17:43 GMT

You're welcome