Load .txt file with pipe symbol separator

[#1]

I am struggling to load a .txt file with the pipe symbol separater. Can any one help as to how the attached file can be loaded to Qlikview.

I tried using the pipe | symbol as the delimiter but does not work.

Row 8 has the column headers - these are further repeated in the file

I would like to delete the rows with Customer, Company code, Name, City, blank rows and rows with '------'.

I just want to end up with the header row and the data - no blanks or any other rows not part of the data.

Thanks in advance.

Jay

[MarcoWedel · Accepted solution]

Hi,

maybe like this:?

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LOAD Account, 
     DocumentNo, 
     [Doc. Date], 
     Assignment, 
     Reference, 
     Arrear, 
     Type, 
     PK, 
     RCd, 
     Num#([LC amnt], '#,##0.##;#,##0.##-') as [LC amnt], 
     [G/L], 
     [Net due dt], 
     [User name], 
     [Ref. Key 1], 
     [Ref. Key 2], 
     Disc.1, 
     DChl, 
     SOrg., 
     Offst.acct, 
     [Pstng Date], 
     Clearing, 
     PayT, 
     [Clrng doc.], 
     [Case ID]
FROM [http://community.qlik.com/servlet/JiveServlet/download/620347-129106/Data_With_Pipe_Separator.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 8 lines, filters(
Remove(Col, Pos(Top, 1)),
Remove(Col, Pos(Top, 25))))
WHERE(IsNum(Account));

hope this helps

regards

Marco

[#3]

Thanks ever so much everyone ! Thanks for the final solution Marco !

[MarcoWedel]

you're welcome

regards

Marco