Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have a couple of questions. For some background, I load data from a daily data file (data load is cumulative i.e. I have to load all files YTD) which is a fixed width csv file and can be pretty big (50-60k new records every day). However, one of the fields in the file is an address field and that field occassionally contains commas, which makes it next to impossible to load the file as CSV. The problem in loading it as fixed width is that Qlikview takes a long time to load these files up compared to csv files and I have also found some bugs whereby the data that Qlikview loads gets corrupted (You get a different set of data with every reload - takes me a few reloads to get the correct data - I will raise that with Qlikview). So my question is:
Thanks,
Abhi
The proper solution is to have the application that creates the csv file apply proper quoting on the address field. Another solution would be to create the csv file with another field separator. Perhaps a semicolon ; or a pipe character |.
If you need to fix this in qlikview then one way would be to load the whole line as one record, then replace the spurious comma's in the address field, then store the results into another text file and finally load that text file as csv. You can use the Textbetween and replace functions to get rid of the comma's.
Temp:
Load Replace(TextBetween(@1, ',' , ',' , 15) , ',' , '_') as Record From ....; // if Address is the 15th field
Store Temp into temp.csv (txt);
Thanks for the reply but won't it just replace the first comma instance in address, whereas I can have more than one comma in the address field.
It probably won't do anything at all. Perhaps something like this works.
Temp:
// address field between 7th comma from the start and 6th comma from the end
LOAD
@1:n, left(@1:n,index(@1:n,',',7)) & replace( mid( @1:n,index(@1:n,',',7)+1,index(mid( @1:n,index(@1:n,',',7)+1),',',-6)),',','') & right(@1:n,1+len(@1:n)-index(@1:n,',',-6)) as Record
FROM Data.csv (fix, codepage is 1252);
Store Temp into DataNew.csv (txt);