Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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
Highlighted
Creator
Creator

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.

View solution in original post

12 Replies
Highlighted
Creator III
Creator III

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

Highlighted

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.

Highlighted
Specialist
Specialist

Although,

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

Highlighted
Creator
Creator

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.

Highlighted
Creator
Creator

Hi Varsha,

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

Thank You.

Highlighted
Partner
Partner

Hi,

attached there is Vasha's load script

S.

Highlighted
Specialist
Specialist

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;

Highlighted
Specialist
Specialist

Hello Haniff,

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

Hope this helps!

Highlighted
Creator
Creator

Thanks