I would like to read a password protected Excel workbook and I'm not sure what the syntax is to enter this parameter.

I found only passwords for the connection, which is not needed due to login.

I use the following in my SQL Server 2014 Web Edition

select  *
     -- into tr.corelab
        from  OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
'Data Source=C:\test.xlsx;Extended Properties=Excel 12.0')...[test$]
  • Have you tried by adding ;Password=**** to your connection string? – McNets Mar 14 '17 at 13:43
  • I don't believe you are able to read from a password protected Excel file using OPENDATASOURCE or even OPENROWSET. Per here: Make sure the file you are reading is closed. And no password protection. – John Eisbrener Mar 14 '17 at 15:50
  • I tried adding ;Password= but this syntax is related to the windows authenticating and not to excel file. Ok then I'll use VBA or something to remove the password first. thank you for trying. – Eyal Philippe Ri Mar 15 '17 at 17:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.