Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Sample.:
** Refer to attachment
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
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.
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
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.
Although,
this might not be the correct way, but I have a workaround,
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.
Hi Varsha,
Actually I am using QV Personal Edition users. Can you help me to copy the script
Thank You.
Hi,
attached there is Vasha's load script
S.
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;
Hello Haniff,
For t1, Instead of delimited , I have choose fixed record and renamed the header as @1.
Hope this helps!
Thanks