Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have been trying to load a simple file which uses a 'broken pipe' delimiter (¦). This is the first time I've come across this type of delimiter and have had problems with it from the start. The main issue is the introduction of an extra character in the data, which I can only assume has been added as part of the encoding process?
The sample qvw and data file is attached. The file itself is being sent from a third party so I'm not able to change the delimiter or file at this stage.
Is there any way to load this data without gaining the extra character in the data?
Any help would be appreciated.
Thanks,
Mehul
Try the bellow script....
as per your data, I assumed that there would be only 4 columns
Test:
Load
purgechar(@1,'Â') as "10PLANT",
purgechar(@2,'Â') as "20STOR_LOC",
purgechar(@3,'Â') as "30TXTMD",
purgechar(@4,'Â') as "40TXTMD"
from TEST.csv
(txt, utf8, no labels,delimiter is '\xa6',header is 1 lines);
I suggest loading the file without specifying the delimiter, so you get a single field.
Then use subfield to parse.
Try the bellow script....
as per your data, I assumed that there would be only 4 columns
Test:
Load
purgechar(@1,'Â') as "10PLANT",
purgechar(@2,'Â') as "20STOR_LOC",
purgechar(@3,'Â') as "30TXTMD",
purgechar(@4,'Â') as "40TXTMD"
from TEST.csv
(txt, utf8, no labels,delimiter is '\xa6',header is 1 lines);
Hi,
probably not the most straight forward solution but without hard coded field names or numbers:
tabHeaderTemp:
LOAD RecNo()&','&Replace(@1,'¦',',') as header
FROM [https://community.qlik.com/servlet/JiveServlet/download/1002119-217370/TEST.csv] (txt, utf8, no labels)
Where RecNo()=1;
tabLines:
LOAD RecNo()&','&Replace(@1,'¦',',') as line
FROM [https://community.qlik.com/servlet/JiveServlet/download/1002119-217370/TEST.csv] (txt, utf8, no labels)
Where RecNo()>1;
tabHeader:
CrossTable (HeadNum,HeadStr)
LOAD * FROM_FIELD ('tabHeaderTemp', header) (txt, utf8, no labels, delimiter is ',', msq);
mapHeader:
Mapping LOAD HeadNum, HeadStr Resident tabHeader;
DROP Tables tabHeaderTemp, tabHeader;
tabResult:
LOAD * FROM_FIELD ('tabLines', line) (txt, utf8, no labels, delimiter is ',', msq);
RENAME Fields using mapHeader;
DROP Field @1;
DROP Table tabLines;
hope this helps nevertheless
regards
Marco
Hi Pradeep and Marco,
Thank you both for the solutions, they both work. However, I'm going to go with the purge method. Replacing the delimiter per row is fine on the test file but the main data file is quite large and this will increase the loading time.
Thanks again,
Mehul
Thanks for your feedback.
Regards
Marco