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.

[CELAMBARASAN]

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

[CELAMBARASAN]

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

And you are trying to access by normal file read.