Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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.

11 Replies
jagan
Luminary Alumni
Luminary Alumni

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


talk is cheap, supply exceeds demand
Anonymous
Not applicable
Author

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
Master II
Master II

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
Partner - Creator III
Partner - Creator III

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

engishfaque
Specialist III
Specialist III

Dear Chinna,

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

SourceFileName:

LOAD *

FROM

$(vSourceDataFile)*.xlsx

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed

Anonymous
Not applicable
Author

Hi Hector,

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

BR,

Chinna

Anonymous
Not applicable
Author

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