Multiple Excel Sheets

[psk180590]

Hallo All,

I have few Excel Sheets with multiple files with Month_Year as the Sheet Name.

How do i load all the sheets once and also save the Sheet Name as Date.

Ex: Data.xlsx has the Following Sheets with some data

Jan 12, Feb 12.....Dec 13

I want to load all the sheets and save the Sheet Name as Jan 2012, Feb 2012...Dec 2013.

Found few links in the community but somehow was not successful. Any pointers would be appraciated.

TIA!!

[Anil_Babu_Samineni]

What are these? Jan 12 and Feb 12 ...??

[jonathandienst]

This technique will load all the sheets from the matching files:

For Each vFileName In FileList(....)

    ODBC CONNECT32 TO [Excel Files;DBQ=$(vFileName)];

    SpreadsheetData:

    SQLTABLES;

    DISCONNECT;

    For i = 0 to NoOfRows('SpreadsheetData') - 1

        Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');

        LOAD .... FROM $(vFileName)

        (biff, table is [$(zSheet)]);

    Next

Next

Fill in the missing details. Hopefully that gets you going.

[jonathandienst]

To include the date modified as requested, use this code

For i = 0 to NoOfRows('SpreadsheetData') - 1

     Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');

     Let zDate = Num(Date#(zSheet, 'MMM yy'));

     LOAD ...,

          Date($(zDate, 'MMM yyyyy'),

          ....

     FROM $(vFileName)

     (biff, table is [$(zSheet)]);

Next

[psk180590]

These are the Sheet Names

[psk180590]

Hi Jonathan,

Thanks for the suggestion. I'm able to connect to the Excel folder and the script also runs successfully but, i don't see any fields after the reload.

[psk180590]

Also, Attaching a sample excel.

[jonathandienst]

I suggest you post the script you used. What I supplied was a script pattern to help you get to the correct script.You need to adapt the pattern to your specific environment and requirements.