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: 
Not applicable

space delimited txt file in qlikview

Hi

I have a .out file with 20 columns and this file is space delimited. When I take it in Qlikview any column value which has space is moved to two different columns. Could you please suggest how I can fix this. Thanks

I am attaching an example of a .txt file and corresponding qvw file. As you see name is Ab John which is split in two columns, I want it in same column.

Thanks

Message was edited by: Priyadarsini Krishnamurthy Added the attachment.

11 Replies
Peter_Cammaert
Partner - Champion III
Partner - Champion III

So actually your input file has a fixed width format? there is an import file type for that.

robert_mika
Master III
Master III

Could post a sample?

el_aprendiz111
Specialist
Specialist

Hi

Directory;

LOAD col_1,

     col_2,

     col_3,

     col_4,

     col_5,

     col_6,

     col_7,

     col_8,

     col_9,

     col_10,

     col_11,

     col_12,

     col_13,

     col_14,

     col_15,

     col_16,

     col_17,

     col_18,

     col_19,

     col_20

FROM

[..\..\..\..\..\..\Desktop\web_Qlik\dat.txt]

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

MarcoWedel

if there is no fixed length, spaces as delimiter, column values containing spaces and no quotation then it's going to be difficult.

Please provide some sample data to clarify.

thanks

regards

Marco

Not applicable
Author

Hi,

I have attached a sample please

Thanks

el_aprendiz111
Specialist
Specialist

hi,

LOAD Name,

     Age,

     Addr1,

     Addr2

FROM

[..\..\..\..\..\Desktop\web_Qlik\sample.txt]

(txt, codepage is 1252, embedded labels, delimiter is spaces, msq);

settu_periasamy
Master III
Master III

For the Provided sample, the below script works (Using Fixed Record in transform wizard)

T1:
LOAD @1:7,
@8:12,
@13:24,
@25:n
FROM
sample.txt
(fix, codepage is 1252, embedded labels);


Capture.JPG

Not applicable
Author

This is going to be a little complicated as mentioned above.  I would start by loading in all possible fields (as separated by spaces)  so in your sample file I would load in 7 columns. 

Easy option.  If you always know that Name will have 1 space and each address will have 1 space each you can just concat the two fields together.

LOAD @1&' '&@2,

     @3,

     @4&' '&@5,

     @6&' '&@7,

FROM

(txt, codepage is 1252, no labels, delimiter is spaces, msq);

If you don't know that there will always be 1 space in those columns you are going to need a lot more complicated code evaluating what is in each field.  for example you know that Age is a number you can evaluate all of the fields for the first Numeric field and make everything before that the name.  This is by no means easy and some situations (addr1 followed by addr2) are near impossible to get perfect but it should give you some direction.

do you have any control over the output file?

Not applicable
Author

I didn't see it was fixed with.  I noticed Ab John had one space and BD Rob had 2 but didn't put that together.  The fixed with solution would make things easier if that holds up.