Whats Wrong with this Code

[alvinford]

Hi All,

I am trying to Load maultiple sheets from Excel. (Dont want to load first 20 Lines of each Excel). Using the below code but its shooting error. Can I know whats wrong with this code.

LET SourcePath='C:\Qlikview\Check\Test\*.xlsx';

For each File in Filelist ('$(SourcePath)')          ;

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

tables:

SQLtables;

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

          LET SheetName = peek('TABLE_NAME', i, 'tables');

Test:

          LOAD *

          From [$(File)]

         (ooxml, embedded labels, header is 20 lines, table is ['$(SheetName)']);

Next

Next File

Drop table tables;

Need you help to know whats wrong with above code.

Regards,

Alvin.

[#2]

Hi,

What is the error you getting.

[alvinford]

Hi Yaseen,

Thanks for your reply . Getting the following error.

Error: File extdata.cpp, Line 2787

Regards,

Alvin.

[CELAMBARASAN]

Hi,

You need to disconnect the file before you load it as excel file for data.

LET SourcePath='C:\Qlikview\Check\Test\*.xlsx';

For each File in Filelist ('$(SourcePath)')          ;

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

tables:

SQLtables;

DISCONNECT;

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

          LET SheetName = peek('TABLE_NAME', i, 'tables');

Test:

          LOAD *

          From [$(File)]

         (ooxml, embedded labels, header is 20 lines, table is ['$(SheetName)']);

Next

Next File

Drop table tables;

It shows error becuase the file is in open status unable to read.

Hope it helps

Celambarasan

[alvinford]

Hi Adimulam,

Thanks for your reply.

Actually the file is closed. You have mentioned that "File is in Open status unable to read" what does it mean. Is there any way to change the status?

I have applied your code. The following error is displayed whlile reload. How can we resolve this ? Request your help.

Regards,

Alvin.

[alvinford]

Hi ....

can any one me help on this ...

Regards,

Alvin.

[#7]

Hi,

I think the excel file is corrupt.

Please, share your application and Excel files to help you.

Regards,

Ricardo

[alvinford]

Hi Ricardo,

Thanks for your reply.

As requested please find the attached application and excel.

I have three sheets in the Excel, want to load all the sheets. As I mentioned facing the error while loading the data. Request your help.

Regards,

Alvin.

[#9 · Accepted solution]

Hi Alvin,

The following worked for me.

LET SourcePath='C:\Qlikview\Test\*.xls';

For each File in Filelist ('$(SourcePath)')          ;

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

tables:

SQLtables;

DISCONNECT;

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

          LET SheetName = purgechar(peek('TABLE_NAME', i, 'tables'),'$');

tables:

          LOAD *

          From [$(File)]

         (ooxml, embedded labels, header is 20 lines, table is $(SheetName));

Next

Next File

Drop table tables;

There are extra columns in one of the sheets which results in a synthetic join ...You should expilicitly name your columns to prevent this.

ie

LOAD Name,

     No,

     Amt,

     Pramoted

FROM [$(File)]

Kind Regards,

Footsie

[alvinford]

Hi ..Footsie,

Thanks for your reply ... Cheers !!

Regards,

Alvin.