topic How can I load excel multi-sheets to qlikview in QlikView

How can I load excel multi-sheets to qlikview

aytacbas — Thu, 13 Nov 2014 13:50:15 GMT

hi all;

I have an excel spreadsheet that contains multi sheets and every week will be added to new sheets. İn addition A column will be same every sheets (This column will be key)

how to load this data ?

Re: How can I load excel multi-sheets to qlikview

Thu, 13 Nov 2014 13:59:30 GMT

Hi,

For i=0 to n;

Load * from Table.xls(sheet($i));

next i;

Re: How can I load excel multi-sheets to qlikview

Anonymous — Thu, 13 Nov 2014 14:01:32 GMT

Use SQLTables function and then loop through all of the tables in TABLE_NAME column

Re: How can I load excel multi-sheets to qlikview

agomes1971 — Thu, 13 Nov 2014 14:01:57 GMT

LET vStartSheetNumber = 1;

LET vEndSheetNumber = 50;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

'' AS Data.Field1,

'' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

Concatenate(Data)

LOAD

FROM [$(vExcelFileName).xlsx]

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

Note : in my excel file the sheet names are in the patter of Page 1, Page 2, ..... Page n.

Hope this is it.

Regards

André Gomes

Re: How can I load excel multi-sheets to qlikview

Anonymous — Thu, 13 Nov 2014 14:06:25 GMT

let vReportDefinitions=<xls location>;

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

QUalify *;

SQL_Tables:

Load *;

SQLTABLES;

vNoOfRows=NoOfRows('SQL_TABLES');

for i=0 to vNoOfRows-1

vTablename=peek('SQL_Tables',$(i),'SQL_Tables.Table_Name')

Load

From

vTablename;

Next i;