topic Re: Multiple Excel sheets without ODBC/OLEDB connection in QlikView

Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Wed, 10 May 2017 09:02:30 GMT

Hi,

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

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Wed, 10 May 2017 09:08:45 GMT

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

avinashelite — Wed, 10 May 2017 09:17:11 GMT

whats the issue your facing ?

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Wed, 10 May 2017 09:22:21 GMT

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

avinashelite — Wed, 10 May 2017 09:40:01 GMT

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

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Wed, 10 May 2017 09:45:40 GMT

Yes, instead of * given all the field names.

Do you think it may have any problem?

Re: Multiple Excel sheets without ODBC/OLEDB connection

awhitfield — Wed, 10 May 2017 09:47:15 GMT

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

avinashelite — Wed, 10 May 2017 09:48:11 GMT

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

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Wed, 10 May 2017 09:49:36 GMT

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.

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Wed, 10 May 2017 09:50:31 GMT

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

Re: Multiple Excel sheets without ODBC/OLEDB connection

avinashelite — Wed, 10 May 2017 10:08:43 GMT

share the sample app and the excel files ...Is the app running fine in local ?

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Wed, 10 May 2017 10:20:01 GMT

In local system while reloading its throwing an error" _xlnm#_FilterDatabase."

changed the file format to .xls and tried but no luck

Re: Multiple Excel sheets without ODBC/OLEDB connection

avinashelite — Wed, 10 May 2017 10:23:32 GMT

Share the sample app and excel let me check once

Re: Multiple Excel sheets without ODBC/OLEDB connection

cwolf — Wed, 10 May 2017 10:40:36 GMT

You can use a macro to get the sheet names:

VB Macro:

function GetSheetNames(pExcelFile)

set ExApp = CreateObject("Excel.Application")

ExApp.Visible = False

set ExWrk = ExApp.Workbooks.Open(pExcelFile)

s = ""

for i = 1 to ExWrk.Sheets.Count

if i = ExWrk.Sheets.Count then

s = s + "'" + ExWrk.Sheets(i).Name + "'"

else

s = s + "'" + ExWrk.Sheets(i).Name + "',"

end if

ExWrk.Close

ExApp.Quit

GetSheetNames=s

end function

Script:

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

let sheetNames=GetSheetNames('$(file)');

FOR Each sheetName in $(sheetNames)

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

Table:

Load *

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

end if

next file

Re: Multiple Excel sheets without ODBC/OLEDB connection

vinieme12 — Wed, 10 May 2017 13:16:21 GMT

Read this thread

The Great ODBC Confusion

ODBC 64 giving an "architecture mismatch" error

Re: Multiple Excel sheets without ODBC/OLEDB connection

tamilarasu — Wed, 10 May 2017 13:53:26 GMT

Hi Sasi,

Have you tried to add "IF" statement like I said yesterday? Looks like you haven't try.. Let me try for you.

**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 vSheetName = Replace(Peek('TABLE_NAME', $(i), 'tables'),chr(39),'');

If(WildMatch(vSheetName,'*$')) Then
LET vSheetName = Replace(Replace(Peek('TABLE_NAME', $(i), 'tables'),'$',''),chr(39),'');

If Wildmatch(vSheetName, 'Region*','Area*') Then
Table:
Load *
From $(file)(ooxml, embedded labels, table is [$(vSheetName)]);
ENDIF

ENDIF
NEXT i

Drop table tables;
Next file

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Thu, 11 May 2017 04:59:17 GMT

Hi Nagaraj,

thank you so much using ODBC i'm looking for similar if condition as well.

But now identified one more issue is that "File extdata.cpp, Line 2291". can please let me know any reason for these kind of issues

Re: Multiple Excel sheets without ODBC/OLEDB connection

prieper — Thu, 11 May 2017 07:36:22 GMT

Error: File extdata.cpp, Line 2903

Re: Multiple Excel sheets without ODBC/OLEDB connection

tamilarasu — Thu, 11 May 2017 07:41:26 GMT

Sasi - Could you post your log file here?

Re: Multiple Excel sheets without ODBC/OLEDB connection

vardhancse — Thu, 11 May 2017 09:12:59 GMT

Hi Nagaraj,

can please let me know any alternative other than using ODBC connection.

Because in our server we don't have an excel driver and so.