topic Re: Broken pipe delimiter encoding error in QlikView

Broken pipe delimiter encoding error

Anonymous — Mon, 21 Mar 2016 16:05:30 GMT

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

Re: Broken pipe delimiter encoding error

m_woolf — Mon, 21 Mar 2016 17:46:23 GMT

I suggest loading the file without specifying the delimiter, so you get a single field.

Then use subfield to parse.

Re: Broken pipe delimiter encoding error

PradeepReddy — Mon, 21 Mar 2016 17:46:31 GMT

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);

Re: Broken pipe delimiter encoding error

MarcoWedel — Mon, 21 Mar 2016 22:26:12 GMT

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

Re: Broken pipe delimiter encoding error

Anonymous — Tue, 22 Mar 2016 10:08:11 GMT

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

Re: Broken pipe delimiter encoding error

MarcoWedel — Tue, 22 Mar 2016 15:28:22 GMT

Thanks for your feedback.

Regards

Marco