Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Broken pipe delimiter encoding error

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

1 Solution

Accepted Solutions
PradeepReddy
Specialist II
Specialist II

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

View solution in original post

5 Replies
m_woolf
Master II
Master II

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

Then use subfield to parse.

PradeepReddy
Specialist II
Specialist II

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

MarcoWedel

Hi,

probably not the most straight forward solution but without hard coded field names or numbers:

QlikCommunity_Thread_210363_Pic1.JPG

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

Anonymous
Not applicable
Author

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

MarcoWedel

Thanks for your feedback.

Regards

Marco