topic Re: Qlikview strange message during load in QlikView

Qlikview strange message during load

Fri, 22 Mar 2013 07:35:46 GMT

We receive the following message

SQL##f - SqlState: S1000, ErrorCode: 4294962281, ErrorMsg: [Microsoft][ODBC Excel Driver] External table is not in the expected format.

ODBC CONNECT32 TO [Excel Files;DBQ=D:\migration_d\klant\Employee Dashboard\Run Maart\2. Source data as used for QlikView\1. Employee data\~$Stat_Niveau_Download_01-03-2013_BE.xlsx]

if we load the data. Loking in the directory there is no file called Stat_niveau or something similar. Only 2 files are there with complete different names.

So how can this be caused and how can I work around this.

John

Re: Qlikview strange message during load

hic — Fri, 22 Mar 2013 07:42:58 GMT

This file name is probably stored in the ODBC data source. So you need to open the data source and change the file name.

File -> Edit Script -> Tools -> ODBC Administrator 64/32 bit -> User DSN/System DSN

HIC

Re: Qlikview strange message during load

Anonymous — Fri, 22 Mar 2013 07:44:06 GMT

If you look in your QlikView script, is there an ODBC connection string and SQL statement to extract data from this specific Excel file? (note that it could be in the hidden script: File > Edit hidden script)

Re: Qlikview strange message during load

Fri, 22 Mar 2013 07:59:37 GMT

Thanks System DSN User DSN can be edited but to what file must I point it.

I ran the dasbhoard many times without loading errors. So must I remove the link all together. Not clear to me what to do with System/User DSN.

John

Re: Qlikview strange message during load

Fri, 22 Mar 2013 09:42:24 GMT

Hello John,

I googled that error and found this :

"It has to do with how the Excel file was saved in Excel 2007. If the file was saved in the format "Excel 93-2003 Workbook (*.xls)" then ODBC can correctly read the range as a table. If, however, it was saved in the new XML format using "Excel Workbook" in Excel 2007, the external table is not in the expected format, because it is in XML and not the native Excel table object. Excel 2007 successfully opens these files when double clicked, so just opening the file in Excel is not sufficient to determine if the file can be read using ODBC."

Try saving the file in "Excel 93-2003 Workbook (*.xls)" format, see if this resolves your issue

thanks,

itangad

Re: Qlikview strange message during load

Fri, 22 Mar 2013 09:47:02 GMT

For our QV app we use the xlsx format for every datasource. The file reported by QV is not in the folder. I have used the app before without problems.

John

Re: Qlikview strange message during load

Fri, 22 Mar 2013 09:56:57 GMT

John, if it worked earlier, then it is nothing to do with qlikview. It might be some driver issue or you can check in qlikview by navigating to

File -> Edit Script -> Tools -> ODBC Administrator 64/32 bit -> File DSN

You might that file there.

Thanks,

angad

Re: Qlikview strange message during load

Fri, 22 Mar 2013 10:52:14 GMT

Hello John,

In your Script you are using this :

LET vDocPath = left(DocumentPath(),Len(DocumentPath())-(12+Len(DocumentName())));

LET vPath = '$(vDocPath)' & '2. Source data as used for QlikView\';

LET vPathCSP = '$(vPath)' & '1. Employee data\';

So I think you must first debug, whether the path created is corrected or not.

Also you can try Debug the Script :

Go to Edit Script-> File -> Debug

And check points and analyze the script.

Thanks,

angad