topic Re: need to load all the sheets in excel at time ? in QlikView

need to load all the sheets in excel at time ?

Sun, 23 Jul 2017 16:33:56 GMT

HI All,

I am having months data in the Excel Sheet.

Every month it will added one more Sheet With new month, need to be loaded in a Single load dynamically.

Please find the attached excel, Can any help me on this..

Regards,

Helen

Re: need to load all the sheets in excel at time ?

effinty2112 — Sun, 23 Jul 2017 17:00:00 GMT

Hi Helen,

When you say that there will be a new sheet every month do mean a new sheet (tab) will be added to this Excel file or will there be a new file for the month?

If there is going to be a new file every month then it's very easy. If all the columns are identical for each month then put all the files into one folder. It's good practice to add the path to this folder to the script as a variable:

LET vxlsFolder = 'insert path here ';

Orders:

LOAD OrderID,

ProductID,

Product,

CustomerID,

EmployeeID,

OrderDate,

Margin,

Sales,

Cost,

Quantity

FROM

$(vxlsFolder)\*.xls

(biff, embedded labels, table is Orders$);

table is Orders$ here means that the script will extract from a sheet called Orders like you have in the file you uploaded.

Save your files to your folder and create your script to read one of them. When it's working then edit the line

$(vxlsFolder)\FilenameYouUseToTest.xls;

$(vxlsFolder)\*.xls

replacing the filename with '*'.

The script will then concatenate all the lines from the files into a single table Order.

Good luck

Andrew

Re: need to load all the sheets in excel at time ?

Sun, 23 Jul 2017 17:04:22 GMT

every month one extra sheet in the same excel

Regards,

Helen

Re: need to load all the sheets in excel at time ?

effinty2112 — Sun, 23 Jul 2017 17:17:54 GMT

Helen,

In that case I'll refer you to this: Load Multiple excel sheets using For loop

S there is an update only once a month the simpler thing may be to make separate files from each sheet.

Kind regards

Andrew

Re: need to load all the sheets in excel at time ?

Sun, 23 Jul 2017 17:32:00 GMT

Tried , Iam getting error, Kindly check once

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

'' AS Sno,

'' AS Name,

'' as [Sales Value]

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

Concatenate(Data)

LOAD

FROM [$(vExcelFileName).xlsx]

(ooxml, embedded labels, table is [Page $(index)]);

Re: need to load all the sheets in excel at time ?

effinty2112 — Sun, 23 Jul 2017 17:38:00 GMT

Helen,

What are the names of your sheets? This script will only work if your sheets are 'Page 1', 'Page 2', ...etc.

Regards

Andrew

Re: need to load all the sheets in excel at time ?

Sun, 23 Jul 2017 17:42:25 GMT

my sheet names will be of months, plz check in the excel

Re: need to load all the sheets in excel at time ?

effinty2112 — Sun, 23 Jul 2017 17:50:07 GMT

Your excel sheet doesn't tell me the names of the sheets you want to load data from. You say sheets will be of months.

'Jan17' or 'Jan 17' or ' January 2017' or '01-17' or ...

Re: need to load all the sheets in excel at time ?

Sun, 23 Jul 2017 18:07:02 GMT

attached the Data excel above , plz check

Re: need to load all the sheets in excel at time ?

Frank_Hartmann — Mon, 24 Jul 2017 07:57:21 GMT

like this? :

Re: need to load all the sheets in excel at time ?

effinty2112 — Mon, 24 Jul 2017 08:33:23 GMT

Helen,

Let vXLSPath='PathToFileIncludingFilename.xlsx';

ODBC CONNECT TO [Excel Files;DBQ=$(vXLSPath)];

Sheets:

SQLTABLES;

DISCONNECT;

for i = 0 to FieldValueCount('TABLE_NAME') -1

Let vTablename = Left(Peek('TABLE_NAME',$(i)),len(Peek('TABLE_NAME',$(i)))-1);

Data:

LOAD *

FROM

$(vXLSPath)

(ooxml, embedded labels, table is [$(vTablename)]);

Next i;

DROP Table Sheets;

Re: need to load all the sheets in excel at time ?

alextomlins — Fri, 26 Oct 2018 15:25:05 GMT

Hi Andrew Walker - I'm getting an error for the ODBC connection. I'm on Qlik Sense Enterprise....Do you know what it could be out of chance? ;

The following error occurred:

CONNECTs other than LIB CONNECT are not available in this script mode.