topic Re: Store txt with header row in QlikView

Store txt with header row

joegrant55 — Sat, 16 Nov 2024 19:07:48 GMT

I have created a table that stores multiple fields into a txt file.

I need to add a 'header row' above the current fields as this txt file is loaded into a system that uses the header row as an indication of what to do with the file. My delimiter is '|'.

Example:

Header1|Header2|Header3|Header4

Does anyone know how I add a header row in the script?

Thanks,

Joe

Re: Store txt with header row

kaushiknsolanki — Wed, 12 Feb 2020 13:37:46 GMT

Hi,

How are you storing this table in txt file?

Re: Store txt with header row

joegrant55 — Wed, 12 Feb 2020 13:41:03 GMT

Like so:

TableExample:

LOAD

FIELD1

FIELD2

FIELD3 etc.

From source

Store TableExample into TableExample.txt(txt);

Re: Store txt with header row

joegrant55 — Wed, 12 Feb 2020 13:46:56 GMT

Apologies the store part is like so:

Store TableExample into TableExample.txt (txt, delimiter is '|');

Re: Store txt with header row

kaushiknsolanki — Wed, 12 Feb 2020 13:50:28 GMT

Hi,

You should give delimiter like below and it should work.

Store [TableName] into [Path]\[FileName].Txt(txt, delimiter is '|');

Re: Store txt with header row

kaushiknsolanki — Wed, 12 Feb 2020 13:51:48 GMT

Hi,

When i performed the same thing, I could see the headers (Field Name) in the file.

Dont you see them?

Re: Store txt with header row

joegrant55 — Wed, 12 Feb 2020 14:11:12 GMT

Yes I see them but as per my example I need another row above the field name headers made up of different names.

Header1|Header2|Header3|Header4

I have attached a txt document example also.

Thanks,

Joe

Re: Store txt with header row

kaushiknsolanki — Wed, 12 Feb 2020 14:17:31 GMT

What is the source of the table?

Is it Excel or database table?

Re: Store txt with header row

joegrant55 — Wed, 12 Feb 2020 14:21:32 GMT

It has been created using multiple qvds and xlsx and then stored as a csv.

This csv. is then loaded, edited and saved as a txt file.

Re: Store txt with header row

kaushiknsolanki — Wed, 12 Feb 2020 14:47:21 GMT

You can do one thing.

Store that data in a temporary text file, similar to what you are doing currently.

Store [TableName] into [Path]\[FileName].Txt(txt, delimiter is '|');

Now write the load script to load this txt file (Newly created one in above step).

Your load statement should look like below.

Data:
LOAD @1 as Header1,
@2 as Header2,
@3 as Header3,
@4 as Header4

FROM
Path\Filename
(txt, codepage is 1252, no labels, delimiter is '|', msq);

And store this using normal Store command.

Re: Store txt with header row

joegrant55 — Wed, 12 Feb 2020 15:57:37 GMT

Thanks this seems to work apart from 1 problem.

I have renamed @1, @2, @3 and @4 to the required header names.

I am now left with @5 to @37 which I don't need. Ideally I need the data in these fields but not the field names. Is there any solution for this issue?

Re: Store txt with header row

kaushiknsolanki — Wed, 12 Feb 2020 16:03:05 GMT

Hi,

No you cant do that.

Re: Store txt with header row

joegrant55 — Fri, 14 Feb 2020 13:59:08 GMT

Okay, are there any other solutions to my problem? This is part of a daily process which I am automating and this is the only step I have been unable to automate using Qlik.

Re: Store txt with header row

Brett_Bleess — Wed, 11 Mar 2020 13:13:45 GMT

Joe, what about using PowerShell script in Windows to modify the CSV file after you create it to add the headers that way? You can even call that script from our load script if you allow Execute in the Script Editor for that app, or if you have Publisher, you could create an External Task in Supporting Tasks to launch things after that reload etc... That is likely going to be the best way I think, as I suspect PowerShell should likely be easier to use to do the insert of that into the file than trying to do things via our script. Hopefully this may work.

Cheers,
Brett