Loading Text Files with Pipe delimited

[Neil_B]

Hi

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

[olivetwist]

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.

[Neil_B]

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

[Neil_B]

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

[Frank_Hartmann]

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;

[Neil_B]

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

[Frank_Hartmann · Accepted solution]

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;

[Neil_B]

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