Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

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
Highlighted
MVP & Luminary
MVP & Luminary

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.

Highlighted
MVP & Luminary
MVP & Luminary

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
Highlighted
Specialist
Specialist

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

Highlighted
MVP & Luminary
MVP & Luminary

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

Highlighted
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

Highlighted
Partner
Partner

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

Highlighted
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

Highlighted
Specialist
Specialist

Hi Hector,

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

BR,

Chinna

Highlighted
Specialist
Specialist

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