topic Re: Import TXT file, only show one column in QlikView

Import TXT file, only show one column

Wed, 27 Jan 2016 22:12:40 GMT

I have a few text files that are produced from IBM Cognos TM1, they have similar structure to the file I have attached. But when I try to import them to Qlikview, they just show it as one column.

The way I do it:

> Edit Script > Table Files

On the File Wizard: (see attached)

I selected "Comma" as Delimiter, and "Embedded Labels" as Labels.

Qlikview just doesn't show me the different columns, what have I done wrong here? Please help.

Thanks in advanced.

Phyllis

Re: Import TXT file, only show one column

Anonymous — Wed, 27 Jan 2016 22:51:17 GMT

In the Wizard set the Header Size to Lines & 1,Quoting to None,and Labels to None. which creates this script :

LOAD @1,

@2,

@3,

FROM

(txt, codepage is 1252, no labels, delimiter is ',', no quotes, header is 1 lines);

That is not the end game and can be tidied up like this :

LOAD purgechar ( @1 , '"' ) as [External_Rating] ,

@2 as [Name] ,

@3 as [Long_Name] ,

purgechar ( @4 , '"' ) as [Credit quality step]

FROM

(txt, codepage is 1252, no labels, delimiter is ',', no quotes, header is 1 lines);

Re: Import TXT file, only show one column

Peter_Cammaert — Wed, 27 Jan 2016 23:02:03 GMT

All lines are quoted in their entirety, making them act as a single field (and without a heading). Quotes in text or csv files are typically used to cover separators embedded in text values. They should be treated as part of the text value, and not as a regular field separator.

Can you get TM1 to output the same but without the starting and ending double-quotes? If possible, that would be the easiest solution. If not, there are QlikView scripting techniques available that do away with extraneous characters.

Best,

Peter

Re: Import TXT file, only show one column

Thu, 28 Jan 2016 11:31:13 GMT

Thank you Bill and Peter for your help.

I didn't realise the text file is giving me the double quotes at the beginning and the end, I should have opened the file in NotePad to check that first, rather than look at it in Excel.

I have fixed the issue by changing the TM1 output, as I have the same process to output a number of dimensions to text file, it is easier to manipulate the output.

Thanks again.

Re: Import TXT file, only show one column

Anonymous — Thu, 28 Jan 2016 12:11:46 GMT

Good, as Peter suggested correcting data at source is always the best option.