Loading Data

[#1]

Hi all,

How to load data if One excel sheet itself got 4 tables.

Janaki.

[amit_saini]

Hi Janaki,

Please share your excel sheet.

Thanks,
AS

[ecolomer]

You need to load each sheet

[#4]

Hi Amit

PFA

[jagan]

Hi,

Check this links

Load Multiple excel sheets using For loop

Loading Multiple Excel sheets

Regards,

Jagan.

[its_anandrjs]

Hi,

You can try this expression and and specify the excel file name here.

For Each vFile in FileList('Data.xlsx')

     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);

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

          From [$(vFile)]

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

     Next;

Next;

Regards

Anand

[#7]

thank you Anand..

[#8]

Hi Jagan,

I can not open second link, it says access denied.

Thanks.

[jagan · Accepted solution]

Hi,

This is the script in the second link

// Configure sheet names to this variable

SET vSheetNames = 'Jan Sales', 'Feb Sales','Mar Sales','Apr Sales','May Sales','Jun Sales','Jul Sales';

// Loop through the sheets and load the data

for each vSheetName in $(vSheetNames)

Data:

LOAD *

     '$(vSheetName)' AS SheetName

FROM

[test.xlsx]

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

NEXT

OR

The second is no need to mention the Sheet name just remove the table is *** .

Note: This method only works for XLSX.

Data:

LOAD *

     '$(vSheetName)' AS SheetName

FROM

[test.xlsx]

(ooxml, embedded labels, header is 2 lines);

Regards,

Jagan.

[#10]

Thanks jagan.