topic Re: . If an excel file contains multiple sheets with different data\table . how to create a qvd for each sheet present in that excel file? in QlikView

. If an excel file contains multiple sheets with different data\table . how to create a qvd for each sheet present in that excel file?

Tue, 17 Jun 2014 12:14:35 GMT

. If an excel file contains multiple sheets with different data\table . how to create a qvd for each sheet present in that excel file?

Re: . If an excel file contains multiple sheets with different data\table . how to create a qvd for each sheet present in that excel file?

Wed, 18 Jun 2014 05:40:59 GMT

it is same as you load the excel file into the qlikview application but the difference is when u load the excel file u will see the screen that shows the fields in the excel file in that left top most corner u will have a tab called tables in that you can see the sheet names that your excel file contains

Re: . If an excel file contains multiple sheets with different data\table . how to create a qvd for each sheet present in that excel file?

sushil353 — Wed, 18 Jun 2014 05:43:22 GMT

Hi,

While loading the data from XL file you have option to select the sheet.

from the tables option while extracting data you can easily select the desired sheet.

HTH

Sushil

Re: . If an excel file contains multiple sheets with different data\table . how to create a qvd for each sheet present in that excel file?

amit_saini — Wed, 18 Jun 2014 05:50:48 GMT

Hi Manoj,

You have to create those sheet as 1,2,3..... . Please follow these step below:

Step 1. press Alt F11, double click on any sheets in the left of the opened window and copy the below code as shown in the below picture.

Sub renameSheets()
Dim iSheetCount
For iSheetCount = 1 To Sheets.Count
Sheets(iSheetCount).Name = iSheetCount
Next iSheetCount
End Sub

Step2:

Then run by clicking the run button like below

Step3:

Save as the file in xlsx fomat.

Step4:

Then Open the Qlikview file, Press Cntrl E, go to the code marked in red as shown in the below picture and change the number as equal to the sheets available in the file “Name of your Excel sheet” after running the macro. Assuming the file contain 290 sheets.
Eg. for loop=1 to 290

Step5:

Reload Qlikview

Hope this will help you.

Thanks,

Re: . If an excel file contains multiple sheets with different data\table . how to create a qvd for each sheet present in that excel file?

jonathandienst — Wed, 18 Jun 2014 07:16:19 GMT

You can use a script like this:

DIRECTORY;

Let vReportDefinitions = 'file path to your Excel file'

ODBC CONNECT32 TO [Excel Files;DBQ=$(vReportDefinitions)];

SpreadsheetData:

SQLTABLES;

DISCONNECT;

For zi = 0 To NoOfRows('SpreadsheetData') - 1

Let zSheet = Peek('TABLE_NAME', zi, 'SpreadsheetData');

Let zOutput = Replace(vReportDefinitions, '.xls', '_' & zSheet & '.qvd');

Data:

LOAD * FROM [$(vReportDefinitions)]

(biff, no labels, table is [$(vSheet)]);

If Alt(NoOfRows('Data'), 0) > 0 Then

STORE Data into [$(zOutput)] (qvd);

Drop Table Data;

End If

This is for xls files. For xlsx files, change the string '.xls' in the replace statement with '.xlsx' and (biff, ..) to (ooxml, ...) in the load statement.

HTH

Jonathan