February 22, 2016 at 1:45 pm
I've run into a curious issue while migrating a sql2k db to a 64bit sql08r2 server. I had to change the data providers for two ad hoc queries from the 32 bit JET to the 64 bit ACE and found that everything tested fine.
select F1,F2,F3,F4,F5,F6,F7,F8
from opendatasource
('Microsoft.ACE.OLEDB.12.0','Data Source="\\server\sites\incoming\problemFile - Copy.xls";
User ID=Admin;Password=;
Extended Properties="Excel 8.0;HDR=No;IMEX=1"'
)...['First Sheet$'] x
The job for both queries failed the next day with the unhelpful Msg 7399 ("Cannot initialize the data source"), but after I opened the sheets in excel and confirmed the sheets looked fine, the ad hoc queries ran fine. Turns out that the query fails unless I manually open each sheet in Excel and close without saving. Even though I'm not changing & saving the file, Excel is apparently doing something to the metadata or header that allows ACE to bring in the file.
After comparing the opened vs unopened version of the files with a hex editor, I found that Excel had changed a portion of the file that contained the string "Java Excel API v2.5.7" to "<excel user name>v2.5.7" where <excel user name> is the user registered on the excel instance. I suspect the "Java Excel API v2.5.7" is a section for a flag that tells ACE that the file is opened/locked. So the Java Excel API is producing a file that reports itself as currently open/locked. When I manually open and close in Excel, the application sets this flag to closed so ACE now believes it can import the file.
Has anyone run into this strange behavior and is there a workaround? Can I use the data provider in a mode that ignores the status of the file and allows importing from a file it thinks is open?
My theory about this open flag may be wrong, since 32bit JET has no problem importing the files, and it can't import from open spreadsheets either. Since all import methods use this data provider for Excel, no other import tools work either - including SSIS or linked servers.
February 22, 2016 at 2:41 pm
I've seen something similar, where a csv file was renamed to have an xls extension...so the file was not truely xls, so it could not be opened.
find the original file, and open it in notepad or a proper text editor. confirm if it's just plain ol csv that's been renamed, or if it's really semi binary inside.
Lowell
February 22, 2016 at 3:40 pm
Lowell (2/22/2016)
I've seen something similar, where a csv file was renamed to have an xls extension...so the file was not truely xls, so it could not be opened.find the original file, and open it in notepad or a proper text editor. confirm if it's just plain ol csv that's been renamed, or if it's really semi binary inside.
I've seen the same thing with files being renamed. The extension didn't match the internal content of the file, so it looked like the driver was failing to open the file. In reality, the driver simply couldn't open a file that was misidentified as a file of a different type.
February 22, 2016 at 6:56 pm
Lowell (2/22/2016)
...where a csv file was renamed to have an xls extension...so the file was not truely xls, so it could not be opened.
Ed Wagner (2/22/2016)
...The extension didn't match the internal content of the file, so it looked like the driver was failing to open the file. In reality, the driver simply couldn't open a file that was misidentified as a file of a different type.
Yes - I opened in a text (and then hex) editor and confirmed that it was, in fact, excel. My initial search for answers found similar cases - most often of html files being renamed to xls - and the accepted solution was to compile an app with the microsoft.office.interop.excel namespace that programmatically opened the file in an instance of excel, saved as excel, and then closed it. We don't have excel on this server, but I have a request in to install. Even then, I'm not sure if programmatically opening the file in excel would have the same "unlocking" effect that manually opening it does. Saving it might, though. It's just a theory, but it's all I have so far.
An example of the "fix" code to compile looks something like this:
<!--To compile add a reference to the Microsoft.Office.Interop.Excel namespace.-->
<!--Can download MS primary interop assemblies from MS download) -->
Module Module1
Sub Main()
Dim oXL As Excel.Application
Dim oYB As Excel.Workbooks
Dim oWB As Excel.Workbook
oXL = New Excel.Application
oXL.Visible = True
oYB = oXL.Workbooks
oWB = oYB.Open("c:\work\BadFile.xls")
oWB.SaveAs("c:\work\GoodFile.xls", Excel.XlFileFormat.xlExcel8)
oWB.Close()
oXL.Quit()
End Sub
End Module
February 24, 2016 at 11:03 am
I finally cracked the haunted spreadsheets by installing office on the dbserver, installing the most recent office interop libraries from ms download, adding a folder named "desktop" to C:\Windows\System32\config\systemprofile & C:\Windows\SysWOW64\config\systemprofile as a kludge for some office/interop issue, then building an SSIS package with a script task as follows:
'Imports Microsoft.Office.Interop.Excel
Public Sub Main()
Dim oXL As Microsoft.Office.Interop.Excel.Application
Dim oYB As Microsoft.Office.Interop.Excel.Workbooks
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
oXL = New Microsoft.Office.Interop.Excel.Application
oXL.Visible = True
oYB = oXL.Workbooks
oWB = oYB.Open("\\files\badfile.xls", CorruptLoad:=True)
oWB.Close()
oXL.Quit()
Dts.TaskResult = ScriptResults.Success
End Sub
Office activation failed, so now I get to see if the job works after the 29 days runs out.
February 24, 2016 at 1:50 pm
While this may not help with your having to open and close the files before importing, you might be able to get Office off your server by installing the Microsoft Access Database Engine 2010 Redistributable https://www.microsoft.com/en-us/download/details.aspx?id=13255
Some of the folks importing data on my servers needed this to read in data from Excel files, and it's worked like a champ. But, as I said, it may not help with your open / close issue, which would put you back where you're at...
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply