topic Re: Whats Wrong with this Code in QlikView

Whats Wrong with this Code

alvinford — Tue, 12 Mar 2013 14:58:46 GMT

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 File

Drop table tables;

Need you help to know whats wrong with above code.

Regards,

Alvin.

Re: Whats Wrong with this Code

Tue, 12 Mar 2013 15:09:15 GMT

Hi,

What is the error you getting.

Re: Whats Wrong with this Code

alvinford — Tue, 12 Mar 2013 15:12:00 GMT

Hi Yaseen,

Thanks for your reply . Getting the following error.

Error: File extdata.cpp, Line 2787

Regards,

Alvin.

Re: Whats Wrong with this Code

CELAMBARASAN — Tue, 12 Mar 2013 15:28:37 GMT

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 File

Drop table tables;

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

Hope it helps

Celambarasan

Re: Whats Wrong with this Code

alvinford — Wed, 13 Mar 2013 01:47:32 GMT

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.

Re: Whats Wrong with this Code

alvinford — Wed, 13 Mar 2013 03:00:49 GMT

Hi ....

can any one me help on this ...

Regards,

Alvin.

Re: Whats Wrong with this Code

Wed, 13 Mar 2013 03:31:08 GMT

Hi,

I think the excel file is corrupt.

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

Regards,

Ricardo

Re: Whats Wrong with this Code

alvinford — Wed, 13 Mar 2013 06:13:37 GMT

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.

Re: Whats Wrong with this Code

Wed, 13 Mar 2013 08:07:15 GMT

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.

LOAD Name,

No,

Amt,

Pramoted

FROM [$(File)]

Kind Regards,

Footsie

Re: Whats Wrong with this Code

alvinford — Wed, 13 Mar 2013 08:20:27 GMT

Hi ..Footsie,

Thanks for your reply ... Cheers !!

Regards,

Alvin.

Re: Whats Wrong with this Code

CELAMBARASAN — Wed, 13 Mar 2013 13:10:00 GMT

Hi,

Should not use Purge chart here.

It removes all the $ symbols in that name.

You should use

LET SheetName = left(peek('TABLE_NAME', i, 'tables'), len(peek('TABLE_NAME', i, 'tables')) - 1);

Re: Whats Wrong with this Code

CELAMBARASAN — Wed, 13 Mar 2013 13:11:30 GMT

Opened status in the sense "Opened by OLE DB Provider for data access".

And you are trying to access by normal file read.