Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

siva_boggarapu
Contributor II

How to open Password protected Excel file in qlikview?

Hello Everyone,

Good Morning!

I have one excel file which consist of Password. I am trying  to open  Password excel file in Qlikview, it's giving error like"Cannot read BIFF file".

Could you please explain me how to open?

Best,

Siva.

1 Solution

Accepted Solutions
shubham_kumar
Contributor III

How to open Password protected Excel file in qlikview?

Dear Siva,

Here is some work around.. but the excel file needs to be opened in  MS Excel first during the below steps..

1. The excel file should be opened first in the system where QlikView is extracting that excel data (with supplying the password)

2. Create ODBC connection for the encrypted (password protected) excel file. Provide DSN Name ,Choose the Excel file and proper excel version (latest is version12)

3. In the QlikView script, choose "ODBC" and connect,choose the DSN you created in step2 and provide password and leave the userid blank.

4.Test the connection

5.Click OK

6.Click "select" and "tick" the "system Fields" check box. You should be able to see the sheets.

NOTE

Whenever there is data extraction needed from the protected excel into QlikView, you should open the protected excel file in excel first otherwise you will get error. "SQL##f-SqlStateSmiley Frustrated1000,ErrorCode:4294962281,ErrorMsg:[Microsoft]ODBC Excel Driver]External table is not in the expected format".

SO KEEP the Excel file Open !!!!??? in the QlikView Server (where the data extraction happens )

Seems the the EXCEL ODBC driver needs the excel file to be opened in MS Excel..

If any other better way, pls update..

Let me know if it works...

Best,

Shubham

5 Replies
shubham_kumar
Contributor III

How to open Password protected Excel file in qlikview?

Dear Siva,

Here is some work around.. but the excel file needs to be opened in  MS Excel first during the below steps..

1. The excel file should be opened first in the system where QlikView is extracting that excel data (with supplying the password)

2. Create ODBC connection for the encrypted (password protected) excel file. Provide DSN Name ,Choose the Excel file and proper excel version (latest is version12)

3. In the QlikView script, choose "ODBC" and connect,choose the DSN you created in step2 and provide password and leave the userid blank.

4.Test the connection

5.Click OK

6.Click "select" and "tick" the "system Fields" check box. You should be able to see the sheets.

NOTE

Whenever there is data extraction needed from the protected excel into QlikView, you should open the protected excel file in excel first otherwise you will get error. "SQL##f-SqlStateSmiley Frustrated1000,ErrorCode:4294962281,ErrorMsg:[Microsoft]ODBC Excel Driver]External table is not in the expected format".

SO KEEP the Excel file Open !!!!??? in the QlikView Server (where the data extraction happens )

Seems the the EXCEL ODBC driver needs the excel file to be opened in MS Excel..

If any other better way, pls update..

Let me know if it works...

Best,

Shubham

siva_boggarapu
Contributor II

How to open Password protected Excel file in qlikview?

Thank you shubham, It is working..but only the document should be open with password first or else it does give error.

best

Siva

qvhlaold0
New Contributor III

Re: How to open Password protected Excel file in qlikview?

Hello,

I create ODBC source and when I try to open XLS, QlikView is speeking: Could not decrypt file.

I have Win7 x64, but I use 32bit ODBC drivers. Office 2010, I tried XLS version 97-2003, version 12.0, I tried two others version of driver: 6.01.7600.16833 (ODBCJT32.DLL; 15.6.2011) and 14.00.6015.1000 (ACEODBC.DLL; 29.8.2011) - the same result.

Do you have any idea? Other way?

Thanks,

Olda

bimala0507
Contributor

Re: How to open Password protected Excel file in qlikview?

Hi:

I have excel 2013. I created the ODBC driver by the above mentioned method, but could not read it in QlikView. It does not show me the file for which I have created the ODBC. Is it because my Excel file is stored in .xlsx format and I am creating ODBC connection with .xls. What ODBC driver I should use to create DSN for Excel  2013 ?

Kind Regards,

Bimala

shubham_kumar
Contributor III

Re: How to open Password protected Excel file in qlikview?

the simplest way to do it, we can directly fetch the option from 'Data from files' and use the Tables Files. Do you have any specific reason for creating the ODBC? what is the scenario?

Community Browser