Selecting New Columns only from excel

[thepope]

Hi All,

I have an excel file where periodic data is added as new columns, I would like to just import the new data (and export to a qvd) so that I can then trend the data

Please see the attached file as an example.

There will always be a fixed number of rows but the column count will increase per period by 5

any help would be appreciated

[prieper · Accepted solution]

Think that you need a twofold approach:
First is just to load the second line and determine, how many columns are available and to get then the lastmost columns.
aircode might be something like:

Period:
FIRST 1 LOAD
    *
FROM
[QV Template.xlsx]
(ooxml, no labels, header is 0 lines, table is Sheet1);

LET sPeriod = PEEK(FIELDNAME($(iColumns) - 4, 'Period'), 0, 'Period');

DROP TABLE Period;


Header:
FIRST 1 LOAD
    *
FROM
[QV Template.xlsx]
(ooxml, no labels, header is 1 lines, table is Sheet1);

LET iColumns = NOOFFIELDS('Header');

LET Col1 = FIELDNAME($(iColumns) - 4, 'Header');
LET Col2 = FIELDNAME($(iColumns) - 3, 'Header');
// etc

DROP TABLE Header;

Data:
LOAD
    A                            AS Data,
    $(Col1)                        AS Col1,    // choose yr name
    $(Col2)                        AS Col2
FROM
[QV Template.xlsx]
(ooxml, No labels, header is 1 lines, table is Sheet1)
;    

STORE Data INTO [Data$(sPeriod).QVD] (QVD);

HTH
Peter

edit: code completed for adding Period and store as QVD.

[thepope · Accepted solution]

Thanks,

Works a treat