Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vardhancse
Valued Contributor II

Multiple Excel sheets without ODBC/OLEDB connection

Hi,

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

43 Replies
vardhancse
Valued Contributor II

Re: Multiple Excel sheets without ODBC/OLEDB connection

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>

Re: Multiple Excel sheets without ODBC/OLEDB connection

whats the issue your facing ?

vardhancse
Valued Contributor II

Re: Multiple Excel sheets without ODBC/OLEDB connection

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"

Re: Multiple Excel sheets without ODBC/OLEDB connection

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
Valued Contributor II

Re: Multiple Excel sheets without ODBC/OLEDB connection

Yes, instead of * given all the field names.

Do you think it may have any problem?

awhitfield
Esteemed Contributor

Re: Multiple Excel sheets without ODBC/OLEDB connection

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

Re: Multiple Excel sheets without ODBC/OLEDB connection

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
Valued Contributor II

Re: Multiple Excel sheets without ODBC/OLEDB connection

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
Valued Contributor II

Re: Multiple Excel sheets without ODBC/OLEDB connection

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