How can I dynamically reference the first sheet in an Excel Document

[wms_manis]

I'm pretty sure I have done this before but I don't recall how it was done.

I am loading in a bunch of spreadsheets using the *.

Load *

FROM

[SOTL Week*.xlsx]

(ooxml, embedded labels, header is 1 lines, table is LYNX);

Unfortunately some of the SOTL Week ##.xlsx files don't have the sheet name LYNX. So to overcome this, if I could simply reference the first sheet in the workbook, without regard to how it was named, my files would load.

Thanks!

Wade

[maxgro · Accepted solution]

load all sheets of all files SOTL Week*

in bold the sheets loop

DIRECTORY;

Table:

load '' as field autogenerate 0;

For Each vFile in FileList('SOTL Week*.xlsx')

  trace file=$(vFile);

    ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

    Sheets:

    SQLTABLES;

    DISCONNECT;

    For i = 0 To NoOfRows('Sheets')-1         

    Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

        trace $(vSheet);

        concatenate(Table)

        LOAD '$(vSheet)' as [Tab Name],

        *

        From [$(vFile)]

        (ooxml, embedded labels, table is [$(vSheet)]);

  Next;

NEXT;

[wms_manis]

I like it. Worked well. Thanks!

Sent from my iPhone