Multiple Excel sheets without ODBC/OLEDB connection

[vardhancse]

Hi,

Can any one please let me know option to load multiple excel sheets(Having multiple sheets) with out using ODBC connection

[vardhancse]

Implemented using ODBC but facing some issue and so trying for any other alternative option.

**Just change the file path

FOR EACH file in FileList('Filepath\*.xlsx');

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

tables:

SQLtables;

DISCONNECT;

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

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

If Wildmatch(sheetName, 'Region*','Area*') Then

Table:

Load *

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

ENDIF

NEXT i

Drop table tables;

Next file

From <https://community.qlik.com/message/1264387#1264387>

[avinashelite]

whats the issue your facing ?

[vardhancse]

Hi Avinash can please let me know will there be any issue with the file format.

1. Present loading .xlsx file but throwing an error "field not found"

2. When running through QMC then "SQL##f - SqlState: IM014, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application"

[avinashelite]

1.No issue with the file format , have specified any fields in the load statement ?

2. You need to have the excel drive in the server

[vardhancse]

Yes, instead of * given all the field names.

Do you think it may have any problem?

[awhitfield]

Hi Sasi,

this indicates a mismatch between 32 and 64 components:

][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Andy

[avinashelite]

Yes , that is the issue , since you have mentioned the field name it is excepting same field names in all the sheets that's why its give the problem

[vardhancse]

But the structure across all the sheets is one and the same.

Moreover some times getting _xlnm#_FilterDatabase issue as well.

Its duplicating sheet names by appending _xlnm#_FilterDatabase.

[vardhancse]

Hi Andrew,

Using user DSN I am trying to load the excel data

When I am loading the data in server the above error was coming