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

[#1]

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

[Anonymous]

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

[#3]

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

prod< alias name>

Load * from Sheet1 ;

Sales<Alias name>

Load * from  Sheet2;

[#4]

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

[amit_saini]

Please follow my suggestion here below:

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

[#6]

At a time we can not load right?

[vijetas42]

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

[Anonymous]

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

NEXT

Regards,

Chinna

[ajaykumar1]

Please refer Load Multiple excel sheets using For loop

Regards,

Ajay

[Anonymous]

SET i = 'Sheet2','Sheet3';

for each m in $(i);

LOAD col1,

     col2,

     col3

FROM

C:\MPA\Book1.xlsx

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

next