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: 
Neil_B
Contributor III
Contributor III

Loading Text Files with Pipe delimited

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

Labels (2)
1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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;

View solution in original post

7 Replies
olivetwist
Creator
Creator

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
Contributor III
Contributor III
Author

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

Neil_B
Contributor III
Contributor III
Author

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
Master II
Master II

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
Contributor III
Contributor III
Author

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
Master II
Master II

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
Contributor III
Contributor III
Author

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