topic load a Password protected Excel File to QlikView in QlikView

load a Password protected Excel File to QlikView

Sat, 13 Nov 2010 22:04:55 GMT

Hi Guys,

Is it possible to load a Password protected Excel File to QlikView?

Thanks,

Aldo.

load a Password protected Excel File to QlikView

Anonymous — Sat, 13 Nov 2010 22:11:08 GMT

No
(I wish someone proves me wrong...)

load a Password protected Excel File to QlikView

Sat, 13 Nov 2010 22:14:59 GMT

what about the new version 10?

It is pretty strange...

load a Password protected Excel File to QlikView

Anonymous — Sat, 13 Nov 2010 22:40:49 GMT

Actually, I tried in QV10. It says:
The Excel file can't be read because it is encrypted.
Doesn't leave much hope.

load a Password protected Excel File to QlikView

stevedark — Mon, 07 Mar 2011 18:01:01 GMT

Hi there,

I have managed to find a work around for this one. If you create an ODBC connection to the spreadsheet and use that for the connection string then it should still correct - even with the protection. On clicking the Select button to load the data you need to tick the 'System Tables' check box in order to be able to see the protected sheets.

Once you have done this you should be able to select data from your protected Excel document.

Unfortunately this means you don't get all the wizards you generally get when importing Excel data and you need to have the Excel ODBC drivers on the machine you are doing the load.

Hope this helps,

Regards,
Steve

load a Password protected Excel File to QlikView

Anonymous — Mon, 07 Mar 2011 18:07:37 GMT

Steve - great idea, thanks!
I'll defenitly try it.

load a Password protected Excel File to QlikView

Mon, 13 Jun 2011 05:34:55 GMT

Nice idea!

It works? did someone tried it?

Thanks,

Aldo.

load a Password protected Excel File to QlikView

Fri, 30 Sep 2011 12:16:14 GMT

Hi Steve,

I tried and it works, but only while the excel file is open.

Is there any workaround?

Thanks in advance,

Aldo.

Re: load a Password protected Excel File to QlikView

Thu, 11 Oct 2012 07:36:47 GMT

Hi Michael,

Did you find a way to implement this issue?

Is it possible in QV11 SR2 ?

Thanks,

Aldo.

Re: load a Password protected Excel File to QlikView

flipside — Thu, 11 Oct 2012 08:47:26 GMT

Hi Aldo,

The following functions seem to work ...

Function OpenExcel(filepath,fext,pwd1,pwd2)

set objExcel = CreateObject("Excel.application")

set objWB = objExcel.Workbooks.Open(filepath & fext,,,,pwd1,pwd2)

'.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword,

' IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)

end Function

Function CloseExcel

on error resume next

set xl = getobject(,"Excel.Application")

xl.activeworkbook.saved = true

xl.Quit

set xl = nothing

call CloseExcel

End Function

... and call in script with the following ...

let x = OpenExcel(filepath,fext,pwd1,pwd2);

ODBC CONNECT32 TO $(cmdODBC);

SQL $(cmdSQL);

DISCONNECT;

let x = CloseExcel();

NOTE: The CloseExcel() function will close all instances of Excel open on the machine.

flipside

Re: load a Password protected Excel File to QlikView

Thu, 11 Oct 2012 14:11:36 GMT

flipside,

Something is missing in the call.

Where to use variable "x" ?

Which string should I use in variables cmdODBC / cmdSQL ?

could you help, please?

Thanks in advance,

Aldo.

SET vFilePath ='C:\Temp\1.xls'; SET vExt ='(xls)';

SET vPwd1 ='a1'; SET vPwd2 ='a1';

SET cmdODBC =''; // ???

SET cmdSQL =''; // ???

LET x = OpenExcel(vFilePath,vExt,vPwd1,vPwd2);

ODBC CONNECT32 TO $(cmdODBC); SQL $(cmdSQL); DISCONNECT;

LET x = CloseExcel();

Re: load a Password protected Excel File to QlikView

flipside — Thu, 11 Oct 2012 18:41:58 GMT

Hi Aldo,

I've probably complicated the code by using the 2 variables, you can simply replace them with your required ODBC connection string and SELECT command. When you do that, you will notice the filepath repeats 3 times overall in the code (in the function call, in the connection string and in the select command), so once you are up and running, you can then look at tidying up the code to use variables - just makes everything neater and more manageable.

The variable x is used in this case to solely call the function, you may not always get a return value but in these functions no return value is needed anyway.

In the OpenExcel function, the filepath needs submitting without the extension, so your variables need to be ...

SET vFilePath ='C:\Temp\1'; SET vExt ='.xls';

... this will make sense when you look at the filepath in the select command.

One issue you may encounter as you set this up and do your testing, is you may inadvertently open multiple instances of Excel, so check this out in Task Manager and ensure none are running before each testing stage as this could cause a failure to access the workbook.

Hope this works for you.

flipside

Re: load a Password protected Excel File to QlikView

Fri, 12 Oct 2012 08:08:51 GMT

any chance to have an example of conn string and sql query ? (sql query to excel...?)

Thanks.

Aldo.

Re: load a Password protected Excel File to QlikView

flipside — Fri, 12 Oct 2012 09:27:54 GMT

Yeah sure.

I take it you have already created an excel driver ODBC connection, which you should be able to select in QV script using the Connect... button. This will generate something like ...

ODBC CONNECT32 TO [Excel Files;DBQ=U:\QV\Sandpit\ProtectedFile.xls];

... where "Excel Files" is the name of the DSN and the filepath (DBQ) is the source file. If the file isn't already open the ODBC wizard will fail, so open the Excel file first. After that use the Select wizard to generate the SQL code directly below the ODBC code, something like ...

SQL SELECT *

FROM `U:\QV\SANDPIT\Protected`.`Sheet1$`;

flipside

Re: load a Password protected Excel File to QlikView

Fri, 12 Oct 2012 10:25:38 GMT

Got it working, thanks !

Aldo.

Re: load a Password protected Excel File to QlikView

ivan_will — Fri, 02 Nov 2012 13:22:43 GMT

Re: load a Password protected Excel File to QlikView

marcus_sommer — Wed, 29 May 2013 08:14:25 GMT

Possibly an alternative:

http://community.qlik.com/message/350934#350934

- Marcus

Re: load a Password protected Excel File to QlikView

Wed, 25 Jun 2014 20:08:31 GMT

Hi,

I have created the macro which opens the file in excel and decrypts it, then there is the code that via the ODBC connects with the excel and reads the data. All according to the steps presented by the User Flipside.

Everything works perfectly when I trigger it within the Qlikview designer. When I try to start the task via QMC it gives the error:

Error: SQL##f - SqlState: S1000, ErrorCode: 4294962291, ErrorMsg: [Microsoft][ODBC Excel Driver] Could not decrypt file.

Have you tried to reload the qvw file which decrypts the excel file via the chain in QMC? Why is it not working?

Thanks for any suggestions.

Asia

Re: load a Password protected Excel File to QlikView

Tue, 14 Oct 2014 20:27:49 GMT

It works if I keep the protected excel open. I dont thinks a correct workaround. I rather encrypt the password in QV script but keeping an workbook open in excel doesnt sound right.

Is my understanding correct? Anyone else had a different way to do this?

Thanks,

Ganesh

Re: load a Password protected Excel File to QlikView

datanibbler — Mon, 17 Nov 2014 08:28:42 GMT

Hi,

I was just redirected here by a link since I have the same issue.

I seem to have no Excel_ODBC_driver or whatever. The "connect" and "select" buttons don't work - the list is empty, no databases selectable.

Also, I cannot keep the Excel_file open every time I need to reload the data from them.

What do I have to do now?

Thanks a lot!

Best regards,

DataNibbler