topic Re: Load Multiple excel sheets dynamically without using Macro or ODBC in QlikView

Load Multiple excel sheets dynamically without using Macro or ODBC

nirmalraj_kj — Fri, 12 Jan 2018 12:50:23 GMT

Hello all,

Herewith attached the source format, is it possible to load in QlikView without using macro or ODBC/OLEDB connection.

Your help is much appreciated.

Please note there might adding more sheets on Ad hoc basis.

Regards

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

marcus_sommer — Fri, 12 Jan 2018 13:19:52 GMT

Not really. If you could add all possible sheetnames within a loop-routine and fetched possible errors with the ERROMODE it might be sufficient for you.

- Marcus

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

Anil_Babu_Samineni — Fri, 12 Jan 2018 13:25:18 GMT

If Sheets are static then Use Concatenate like

tables:

LOAD * FROM (ooxml, embedded labels, table is Chennai);

Concatenate(tables)

LOAD * FROM (ooxml, embedded labels, table is Bangalore);

Concatenate(tables)

LOAD * FROM (ooxml, embedded labels, table is Mumbai);

Concatenate(tables)

LOAD * FROM (ooxml, embedded labels, table is Delhi);

Concatenate(tables)

LOAD * FROM (ooxml, embedded labels, table is Pune);

Concatenate(tables)

LOAD * FROM (ooxml, embedded labels, table is Hyderabad);

Or, If dynamic you could use For Each loop Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

nirmalraj_kj — Fri, 12 Jan 2018 13:52:51 GMT

Hi Anil,

Thanks for your response, I have mentioned in my question that we have to load the data dynamically rather using ODBC/OLED or Macro.

and I mentioned there might add some more sheets in future in that case how can we write concatenate. This is not a dynamic way.

Regards

Nirmal

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

nirmalraj_kj — Fri, 12 Jan 2018 13:54:30 GMT

Thanks for your reply Marcus,

That is not possible in my case I have more that 50 sheets and it might increase in future. I have to load this dynamically without changing code.

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

Anil_Babu_Samineni — Fri, 12 Jan 2018 13:56:04 GMT

That's why I have initiated Plan B for you

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

nirmalraj_kj — Fri, 12 Jan 2018 13:58:31 GMT

You mean using ODBC?

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

Anil_Babu_Samineni — Fri, 12 Jan 2018 14:02:24 GMT

Not exactly, But how your source stored? In DB / Flat files?

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

nirmalraj_kj — Fri, 12 Jan 2018 14:03:31 GMT

Excel spread sheet...

Re: Load Multiple excel sheets dynamically without using Macro or ODBC

marcus_sommer — Fri, 12 Jan 2018 14:20:48 GMT

You need to know (or at least to guess the possible sheetnames) or using odbc/macros to keep the loadings dynamically. Maybe the citys which are used as sheetnames are also stored anywhere else from which it could be read ...

- Marcus