topic Re: Loading Text Files with Pipe delimited in QlikView

Loading Text Files with Pipe delimited

Neil_B — Sat, 16 Nov 2024 03:01:47 GMT

Query re loading “|” pipe delimited text files. Using QlikView 11.20

We have pipe delimited text files that we need to load into QlikView. The issue is, that there are a few text fields that contain carriage returns and line feeds chr(10) and chr(13). Inevitable QlikView treats these as delimiters and breaks into new fields.

I am using this syntax (txt, codepage is 1252, embedded labels, delimiter is '|', msq); in the load statement, and even combinations of Replace(Replace([My Description], chr(10), ''),chr(13),'') As [My Description]

I’ve looked around the forum read some interesting articles and tried numerous suggested solutions, but QlikView always seems to default to treating the combination of chr(10) and chr(13) as a delimiter.

Is there solution for this issue or maybe I missing something, ideally we want to avoid any pre-processing with other tools, or get the text file format changed (they are set in stone!).

Many Thanks

PS I did try the topic "Text file loading resistant to extra CF/LR "– unfortunately it didn’t help

Re: Loading Text Files with Pipe delimited

olivetwist — Thu, 11 Jul 2019 14:16:19 GMT

Are you able to share an example source file? It works for me on a basic txt file but I dont think i have the special char situations you described.

Re: Loading Text Files with Pipe delimited

Neil_B — Thu, 11 Jul 2019 14:24:03 GMT

Yes I'll try create file to replicate the issue, obviously I have to be carefully with the original data

Re: Loading Text Files with Pipe delimited

Neil_B — Fri, 12 Jul 2019 08:59:38 GMT

I've included an attachment (text file, Zipped) at the top. Loading this in to QlikView will demonstrate the issue as mentioned above.

Re: Loading Text Files with Pipe delimited

Frank_Hartmann — Fri, 12 Jul 2019 10:06:08 GMT

if its always the same structure you can try like this:

LOAD if(len(A_MyKey)>3,Null(),A_MyKey) as A_MyKey, [Open/Closed], New, Status, [Line Number], if(len(Description)<28, Description&' no carriage returns or line feeds',Description) as Description, if(Reference='', 'Reference Z'& right(A_MyKey,2) ,Reference) as Reference, Profession FROM [C:\Users\admin\Desktop\TestPipe.txt] (txt, codepage is 1252, embedded labels, delimiter is '|', msq) where len(A_MyKey)<=3;

Re: Loading Text Files with Pipe delimited

Neil_B — Fri, 12 Jul 2019 15:33:59 GMT

Hi Frank

Thanks for your suggestion, sorry if I mislead you bit, the actually real Description field is a free text field. I created the data for the file for demonstration purposes, plus so as not to expose the real data for obvious reasons. So we can't rely on the Description text structure being similar for each record.

Regards

Re: Loading Text Files with Pipe delimited

Frank_Hartmann — Sat, 13 Jul 2019 09:44:27 GMT

what about this?

Test: LOAD RowNo() as Row, A_MyKey, [Open/Closed], New, Status, [Line Number], Description, Reference, Profession FROM [C:\Users\admin\Desktop\TestPipe.txt] (txt, codepage is 1252, embedded labels, delimiter is '|', msq); NoConcatenate Load RowNo() as Row, A_MyKey, [Open/Closed], New, Status, [Line Number], Profession, if(Reference='', Description &' '&Previous(A_MyKey)&' '&Previous(Previous(A_MyKey)) ,Description) as Description, if(Reference='',Previous(Previous([Open/Closed])),Reference) as Reference Resident Test Where Description<>'' Order by Row desc; DROP Table Test;

Re: Loading Text Files with Pipe delimited

Neil_B — Wed, 17 Jul 2019 13:23:14 GMT

Thank you very much, I do believe that is the solution.