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

[#1]

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

[#2]

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