Solved: Load .txt file with pipe symbol separator - Qlik Community

Report Inappropriate Content · ‎2014-09-25

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 · ‎2014-09-26

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

View solution in original post

maxgro · ‎2014-09-25

just to start

Directory;

load subfield(@1, '|', 2) as Account,

subfield(@1, '|', 3) as DocumentNo,

subfield(@1, '|', 4) as Doc.Date,

subfield(@1, '|', 5) as Assignment,

subfield(@1, '|', 6) as Reference,

subfield(@1, '|', 7) as Arrear,

subfield(@1, '|', 😎 as Type,

@1

;

LOAD @1

FROM Data_With_Pipe_Separator.txt (txt, codepage is 1252, no labels, delimiter is '@', msq)

where left(@1,1)='|' and not

wildmatch(@1, '|---------------*') //, '*Account|Document*')

;

Report Inappropriate Content · ‎2014-09-25

Hi,

While loading the data in the script, select the delimiter to '|' then do a normal reload.

as a second step use subfield(Account,'-',1) to delimit '----' before the Account.

I hope it will work.

I have done the same and got the attached application result.

Hope it helps.

MarcoWedel · ‎2014-09-25

Hi,

you could try like:

LOAD *
FROM [http://community.qlik.com/servlet/JiveServlet/download/619468-128924/Data_With_Pipe_Separator.txt]
(txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 7 lines, filters(
Remove(Col, Pos(Top, 1))))
WHERE(IsNum(Account));

hope this helps

regards

Marco

Report Inappropriate Content · ‎2014-09-26

Thanks Marco, your solution worked.

Just one problem I encountered is with the $ value column 'LC Amnt'. Unfortunately, when you sum this column, its only adding up the positive values - it ignores the negatives. The negative values are just being ignored. Is there a way round this ?

Thanks Again.

Report Inappropriate Content · ‎2014-09-26

Thanks Marco, your solution worked.

Just one problem I encountered is with the $ value column 'LC Amnt'. Unfortunately, when you sum this column, its only adding up the positive values - it ignores the negatives. The negative values are just being ignored. Is there a way round this ?

Thanks Again.

Anonymous · ‎2014-09-26

Hi

i dont see any negative values in LC Amnt. However, the data in this field is recognized as text by qlikview.

Could you modify Marcos script like this and try again:

LOAD *,

replace(replace([LC amnt], '.', Null()), ',', Null())/100 as [LC amnt2]

FROM [http://community.qlik.com/servlet/JiveServlet/download/619468-128924/Data_With_Pipe_Separator.txt]

(txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 7 lines, filters(

Remove(Col, Pos(Top, 1))))

WHERE(IsNum(Account));

maybe this solves your problem with negative figures. if not please post sample data with negative values in order to test that,

Best regards

Stefan

Report Inappropriate Content · ‎2014-09-26

Thanks Stefan.

Sorry, realized I did not have any negatives in the sample data I originally sent. Attached has negative values.

Your solution did not work because the negative values in the data file are denoted by a hash at the end of the number.

Hoping there is a way to get round this one !

Thanks Agaian.

MarcoWedel · ‎2014-09-26

Hi,

like Stefan said, I also can't see any negative values of [LC Amnt], not even in your txt source.

To recognize the numerical and date fields, I attached a version of the script including the format variables:

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 * 
FROM [http://community.qlik.com/servlet/JiveServlet/download/619468-128924/Data_With_Pipe_Separator.txt] 
(txt, codepage is 1252, embedded labels, delimiter is '|', msq, header is 7 lines, filters( 
Remove(Col, Pos(Top, 1)))) 
WHERE(IsNum(Account));

hope this helps

regards

Marco

Report Inappropriate Content · ‎2014-09-26

Sorry, attached the wrong file. This one has negatives. Thanks.