If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.
I'm new to qlik and having issues when importing large csv files that we receive monthly (*we get this on different dates each month so have to manually load app) . The csv files have a double quote (") qualifier as well. There some are cases where some values( addresses) contain an extra double quote. This appears to be causing issues and some data is now in the incorrect fields. Is there a way to combat this issue? Is it to do with the load script delimiter? codepage? msq?
I have created a sample below as I am unable to upload dataset
|1||1411 Oakmound Road, Burr Ridge, Illinois, 60527||1411||Oakmond Road||Burr Ridge||Illinois||60527|
|2||3948 Rose Street, Lucinda, Pennsylvania, 16235||3948||Rose Street||Lucinda||Pennsylvania||16235|
My other issue is that I only picked up these issues as I'm doing manual checks and there are multiple csv's. This takes quite a long time and majority of the time the issue would be only found in 2 out of 10 files. Using qliksense is there a way to check for " or other symbols that are causing my issue via data load editor?? Any suggestions in how I would handle this please. Not too sure where to start
This is an example of what I have used to load the table
*all the necessary fields*
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);
also tried (txt, codepage is 1252, embedded labels, delimiter is ''", msq);
Thank you very much!
Thank you for your response Henric. I have tried removing msq and get the same result. Could you please recommend an alternative? Or would it be best to clean the csv files before loading?
It is most likely best to clean the csv files before loading. There is however one more trick you can try: Instead of loading a delimited file
*all the necessary fields*
FROM [File.txt] (txt, utf8, embedded labels, delimiter is ',', msq);
you could load it as a fixed-record file and try to create the fields yourself:
Subfield(EntireLine,',',1) as Field1,
Subfield(EntireLine,',',2) as Field2,
Subfield(EntireLine,',',3) as Field3,
[@1:n] as EntireLine
FROM [File.txt] (fix, utf8, no labels, header is 1 lines);
This will leave quotes untouched, so you may need to remove these - as well as leading/trailing blanks - by wrapping the Subfield() in a PurgeChar() and a Trim().
But as I said, it is probably best to clean the original text files instead.
Apologies for my late response. In terms of cleaning the data outside of qlik what would be the best way to handle the different types of data errors? Below is another csv where I have issues and sometimes "" shows data gaps so should be left. Due to that I wasn't too sure where to start.
4,269,House,31/05/2021,Bank of USA,"LA Branch""""",77.1
It should be possible to load the table you show using the "no quotes" clause. Also, you can inside the Load statement remove unwanted characters. E.g.
PurgeChar(T_Name,'"') as New_Name,
4,269,House,31/05/2021,Bank of USA,"LA Branch""""",77.1] (no quotes);