topic Re: How to load date from multiple sheets from one excel file at a time into QV? in QlikView

How to load date from multiple sheets from one excel file at a time into QV?

Tue, 17 Jun 2014 12:13:28 GMT

How to load date from multiple sheets from one excel file at a time into QV ?

Re: How to load date from multiple sheets from one excel file at a time into QV?

Anonymous — Tue, 17 Jun 2014 12:17:37 GMT

By sheet by sheet like

first load sheet1 & next step load sheet2

At a time is not possible I think so......

If you get any solution post here..........

Re: How to load date from multiple sheets from one excel file at a time into QV?

Tue, 17 Jun 2014 12:17:57 GMT

go to Edit script and give alias name and type as below.

prod< alias name>

Load * from Sheet1 ;

Sales<Alias name>

Load * from Sheet2;

Re: How to load date from multiple sheets from one excel file at a time into QV?

Wed, 18 Jun 2014 05:48:19 GMT

the above is the screen shot that is used to load multiple sheets in an excel file into the qvw appl my sheets are 3pl inventory, summary,qty recoth

Re: How to load date from multiple sheets from one excel file at a time into QV?

amit_saini — Wed, 18 Jun 2014 05:53:13 GMT

Please follow my suggestion here below:

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

Re: How to load date from multiple sheets from one excel file at a time into QV?

Wed, 18 Jun 2014 05:54:36 GMT

At a time we can not load right?

Re: How to load date from multiple sheets from one excel file at a time into QV?

vijetas42 — Wed, 18 Jun 2014 06:05:28 GMT

Hi,

At a time you can not load multiple sheet from one excel file,you need to load data of sheets one by one.

Regards,
Vijeta

Re: How to load date from multiple sheets from one excel file at a time into QV?

Anonymous — Tue, 09 Sep 2014 05:28:26 GMT

Hi,

here is the Script:(works irrespective of number sheets/sheet names).

ODBC CONNECT32 TO [Excel Files;DBQ=C:\MPA\Book1.xlsx];

tables:

SQLtables;

DISCONNECT; // Don't need ODBC connection anymore

One of the fields loaded by SQLtables is "TABLE_NAME". This field will contain the sheet name.

We will loop through this set of sheet names.

FOR i = 0 to NoOfRows('tables')-1

Assign the TABLE_NAME to the variable "sheetName".

TABLE_NAMEs that contain spaces will be enclosed in single quotes.

The purgeChar function will remove any quotes AND dollar signs.

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

// Optional filtering logic to select certain sheets

Table_with_data:

// Now that we have a sheet name, a standard biff (Excel) load can be used.

LOAD *,

'$(sheetName)' as Sheet // Optionally, the sheetName value may be loaded as a field

FROM C:\MPA\Book1.xlsx (ooxml, embedded labels, table is [$(sheetName)]);

Regards,

Chinna

Re: How to load date from multiple sheets from one excel file at a time into QV?

ajaykumar1 — Tue, 09 Sep 2014 05:34:10 GMT

Please refer Load Multiple excel sheets using For loop

Regards,

Ajay

Re: How to load date from multiple sheets from one excel file at a time into QV?

Anonymous — Tue, 09 Sep 2014 06:46:48 GMT

SET i = 'Sheet2','Sheet3';

for each m in $(i);

LOAD col1,

col2,

col3

FROM

C:\MPA\Book1.xlsx

(ooxml, embedded labels, table is $(m));