Load data from multiple sheets of excel file(dynamic sheet count)

[Anonymous]

Dear Gurus,

Here is my requirement.

We get a excel file generated from  BW source. Data populates in multiple sheets with the same structure(same columns in all sheets).

But Number of sheets will get varied every day.

So I have to identify the number of sheets and load all the sheets data using for loop.

Is there any other way apart from ODBC  connection?

Thanks for your help in advance.

BR,

Chinna.

[jagan]

Hi,

If you are using xlsx then try like this, just remove the sheet name

Data:

LOAD *   

FROM

[test.xlsx]

(ooxml, embedded labels);

Hope it helps you.

Regards,

Jagan.

[Gysbert_Wassenaar]

If all the sheet names are like 'Sheet' + a number, i.e. Sheet1, Sheet2, ..., Sheet then you can try something like:

set errormode = 0;

for i= 1 to 255

    MyExcelData:
    LOAD *
    FROM 
    (ooxml, embedded labels, Table is Sheet$(i) )
    ;

next

[Anonymous]

Hi ,

Thank you Gysbert. Unfortunately above solution will not work(if error mode not set to 0)

if we have any empty sheets/number of sheets are less than  255. we will get field not found error.

Suppose if I have 5 sheets of data it will iterate till 255 times and it will give error all the time. I know we have to set error mode to ignore the error message.

Is there any other way apart from odbc connection to determine the number of sheets in a excel sheet.

BR,

Chinna

[marcus_sommer]

You could read and check the sheets with a vbs-batch and stored them into a txt-file before you loaded these sheets into qv, maybe so:

'-------------------------------------------------------------------------------------------------------------------------------------------------

'Content of ReadAndWriteExcelSheets.vbs:

dim sourcepath, sourcefile, xls, ws, wsNames, fso, targetpath, targetfile

sourcepath = "D:\"
sourcefile = "Test.xls"
targetpath = "D:\"
targetfile = "SheetListing.txt"

set xls = createobject("Excel.Application")
xls.Workbooks.open sourcepath & sourcefile

wsNames = "WorkSheets" & chr(13) & chr(10)

for each ws in xls.worksheets
    if ws.Range("A1").Value = "Belegnr. " then
        wsNames = wsNames & ws.Name & chr(13) & chr(10)
    end if
next

set fso = createobject("scripting.fileSystemobject")
set targetfile = fso.opentextfile(targetpath & targetfile, 2, true)
targetfile.write wsNames
targetfile.close

xls.Application.Quit

'-------------------------------------------------------------------------------------------------------------------------------------------------

SET vCSCRIPT = 'c:\windows\system32\cscript.exe';
EXECUTE $(vCSCRIPT) "D:\ReadAndWriteExcelSheets.vbs";

SheetList:

Load WorkSheets From D:\SheetListing.txt (ansi, txt, delimiter is '\t', embedded labels);

for i = 1 to noofrows('SheetList')

     let vSheetName = peek('WorkSheets', $(i), 'SheetList')

     xlsData:

     Load * From Test.xls (biff, embedded labels, Table is $(vSheetName))

next

But it should also work with odbc the if the xls will be handle like a database then it should be possible to query something like:

Select * From tables;

On the fast I haven't found this directly only ways per vba and ado - http://support.microsoft.com/kb/257819 -

maybe someone else had here experience.

- Marcus

[prieper]

You may establish an ODBC-connection to the Excelfile.

The command SQLTABLES delivers i.a. all sheetnames.

With a loop you may read them one by one into a variable, which then will be used in the FROM-statement.

HTH Peter

[hectorgarcia]

from @rob wonderlich cookbook, i tried and it worked very well

[engishfaque]

Dear Chinna,

SET vSourceDataFile = '..\Data Files\Excel\';

SourceFileName:

LOAD *

FROM

$(vSourceDataFile)*.xlsx

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed

[Anonymous]

Hi Hector,

I'm looking for without ODBC connection. Many thanks for your response.

BR,

Chinna

[Anonymous]

Dear Marcus,

Many thanks for your script.

Is your script is compatible for xlsx?

Some how my sheet names are not being read with that vb script.

PFA script , qvw and sample data for your reference.

Kindly help me to get this work.

Thank you in advance.

BR,

Chinna