Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

chinnakatikisg
Valued Contributor

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.

Tags (2)
11 Replies
MVP
MVP

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

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.

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

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
chinnakatikisg
Valued Contributor

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

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

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

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
Honored Contributor II

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

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
Contributor III

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

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

engishfaque
Valued Contributor III

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

Dear Chinna,

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

SourceFileName:

LOAD *

FROM

$(vSourceDataFile)*.xlsx

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed

chinnakatikisg
Valued Contributor

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

Hi Hector,

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

BR,

Chinna

chinnakatikisg
Valued Contributor

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

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

Community Browser