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

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

Anonymous — Mon, 15 Dec 2014 08:24:24 GMT

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.

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

jagan — Mon, 15 Dec 2014 08:55:09 GMT

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)

Gysbert_Wassenaar — Mon, 15 Dec 2014 09:00:56 GMT

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

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

Anonymous — Mon, 15 Dec 2014 11:31:04 GMT

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)

marcus_sommer — Mon, 15 Dec 2014 13:28:24 GMT

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

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

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

prieper — Mon, 15 Dec 2014 21:34:13 GMT

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

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

hectorgarcia — Mon, 15 Dec 2014 21:44:56 GMT

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

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

engishfaque — Tue, 16 Dec 2014 05:00:12 GMT

Dear Chinna,

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

SourceFileName:

LOAD *

FROM

$(vSourceDataFile)*.xlsx

(ooxml, embedded labels, table is Sheet1);

Kind regards,

Ishfaque Ahmed

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

Anonymous — Wed, 17 Dec 2014 00:20:34 GMT

Hi Hector,

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

BR,

Chinna

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

Anonymous — Wed, 17 Dec 2014 00:42:22 GMT

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

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

marcus_sommer — Wed, 17 Dec 2014 08:20:54 GMT

XLS or XLSX isn't any difference for this kind of code. But you need some more adjustement for your case. Within the vbs is a short check-statement example on a certain content of a certain cell-value with the aim to check if a sheet is empty or contained the right data. You mentioned this in your post above. Adjust this or simply comment it out or remove it.

....

if ws.Range("A1").Value = "Belegnr. " then
....

Further have a look on the user-properties for security. There are options to allow the execution-statement and if you want run it through the server those user-account needed also these permission and access rights on the filesystem.

So I suggest check it in steps - first manually the excution from the vbs-batch and then the other.

- Marcus

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

Mon, 05 Jan 2015 11:38:47 GMT

Hi Chinna,

Try this macro to count the excel sheets without using ODBC.

Sub CountExcelSheetNo()

'Create Excel Object

Set ExcelObject = CreateObject("Excel.Application")

'Give the excel file

ExcelFile="D:\test.xlsx"

'Open the workbook

Set OpenWorkBook = ExcelObject.Workbooks.Open(ExcelFile)

'Hide the opened file

ExcelObject.Visible = False

'count the number of workbook sheets

msgbox(ExcelObject.worksheets.Count)

'Store it into a variable

set NoSheets = ActiveDocument.Variables("NoSheets")

NoSheets.SetContent ExcelObject.worksheets.Count, true

End Sub

Regards.