Import TXT file, only show one column

[#1]

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

[Anonymous · Accepted solution]

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,

    @4

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

[Peter_Cammaert]

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

[#4]

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.

[Anonymous]

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