Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

mohdhaniff
Contributor

How To import CSV Data File contain comma and apostrophe

  • 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.:

1.JPG

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

1.JPG

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);

1.JPG

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

1 Solution

Accepted Solutions
mohdhaniff
Contributor

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

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.

12 Replies
Highlighted
hemanthaanichet
Contributor III

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

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$);Capture3.PNG

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$);

Capture2.PNG

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

Regards

Hemanth

ramasaisaksoft
Valued Contributor III

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

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.

varshavig12
Valued Contributor

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

Although,

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

mohdhaniff
Contributor

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

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.

mohdhaniff
Contributor

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

Hi Varsha,

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

Thank You.

Partner
Partner

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

Hi,

attached there is Vasha's load script

S.

varshavig12
Valued Contributor

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

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;

varshavig12
Valued Contributor

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

Hello Haniff,

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

Hope this helps!

mohdhaniff
Contributor

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

Thanks