Load a csv with a header and use header date as a column

Wed, 26 Sep 2012 16:57:26 GMT

Hi, I have a csv file that has a header containing the date the file was created. I need to grab the date but not load the row into the table. The load should then proceed to load the actual column data but use the date to populate an additional date column that doesn't exist as a column in the csv.

Example file format is below.

"Created On : 25 Jun 2012 01:00"

"Portfolio Name","Identifier","Instr Type","Description","Currency"

"PLLNOPRO","GB00B1VYCN43","placeholder","Skipton BS IBS","GBP"

"PLLNOPRO","XS0047301279","placeholder","Greycoat",""

"PLLNOPRO","IE00B1DX6V59","placeholder","Ignis GBP Liquidity Fund","GBP"

So, I need to end up with a table with 5 columns:

Date, "Portfolio Name", "Identifier", "Instr Type", "Description", "Currency"

And the data in the table looking like:

"25 Jun 2012","PPLNOPRO","GB00B1VYCN43","placeholder","Skipton BS IBS","GBP"

Hope someone out there can solve this mystery for me?

Thanks, Paul

Re: Load a csv with a header and use header date as a column

Wed, 26 Sep 2012 17:36:56 GMT

He Paul,

Try something like this:

Tmp:

LOAD @1

FROM

C:\test\row.csv

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

let vDate = mid(FieldValue('@1',1),15, Len(FieldValue('@1',1))-21);

drop tables Tmp;

Test:

LOAD

'$(vDate)' as Date,

[Portfolio Name],

Identifier,

[Instr Type],

Description,

Currency

FROM

C:\test\row.csv

(txt, codepage is 1252, embedded labels, delimiter is ';', msq, filters(

Remove(Row, Pos(Top, 1))

));

What I did was loading only the first column to get the date created. Then i created a variable to get the value of the first field of the first column and did some transformations to get the date (you probably have to fine tune this)

Then I loaded the whole table another time, removed the first row and added the data variable as a field.

Hope this helps.

gr.

Frank

topic Re: Load a csv with a header and use header date as a column in QlikView

Load a csv with a header and use header date as a column

Re: Load a csv with a header and use header date as a column