Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

Re: space delimited txt file in qlikview

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

Re: space delimited txt file in qlikview

Could post a sample?

el_aprendiz111
Valued Contributor

Re: space delimited txt file in qlikview

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);

Re: space delimited txt file in qlikview

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

Re: space delimited txt file in qlikview

Hi,

I have attached a sample please

Thanks

el_aprendiz111
Valued Contributor

Re: space delimited txt file in qlikview

hi,

LOAD Name,

     Age,

     Addr1,

     Addr2

FROM

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

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

Re: space delimited txt file in qlikview

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

Re: space delimited txt file in qlikview

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

Re: space delimited txt file in qlikview

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.