How to Extract data from Excel 2003(password protected)
-
Hi Guys,
I want to read specific cells from excel(.xls) file and then export it to SQL SERVER. But the problem is the excel file is password protected.
I have try to use OPENROWSET query. But it seems Microsoft Jet not work if the excel is protected by password. Example:
SELECT F1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\book1.xls;HDR=NO;IMEX=1',
'SELECT * FROM [Sheet4$E34:E34]')
Is there any suggestion for my ETL work? I really need advice..
Any suggestions is welcomed.
Notes: i use SQL 2005 and SSIS 2005
Regards,
Harry
-
Do you know the password?
Search this site for "excel password protected". You'll find plenty of forum posts.
-
Short answer is, you can't.
If you don't know the password, and you're not even able to open the file (no Read Only option), then you're not able to do this. If you do know the password, you should be able to open and save a renamed copy of the Excel book without the password; check out:
http://www.sqlservercentral.com/Forums/Topic885800-148-1.aspx
(or anything else found in Emily's response of searching the forums)
You usually have to get creative when working with Excel in SSIS. 🙂
-
the problem Solved brother..
The Solving is : i use Microsoft.Office.Interop.Excel to create a small console application that read data from excel and store it to SQL server. then my SSIS just need to call that console application.
Thx all...
-
Quick Q: was the password on the original Excel book a password to open the book, or was it a password to protect the workbook from changes? If the password was to prevent opening the book, was there a read-only option available? Just curious, because if the password was to open the book, then the protection of the data would seem to be defeated by your work around.
-
If you can use third-party solutions, check the commercial CozyRoc Excel components. These are the relevant components:
* Excel Source component - for reading data from Excel worksheet.
* Excel Destination component - for writing data in Excel worksheet.
* Excel Task - for manipulating Excel workbooks.
* Excel Connection - used by the components above and also for implementing custom scripts based on it.
The Excel Connection allows reading from password-protected workbooks.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
In you Data Task create Excel Source
there select Excel Connection Manager and map to the excel file. Since you have read only permission you can extract complete data.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply