October 1, 2010 at 11:05 am
Hello All.
I am trying to import excel files using the OpenRowSet command, and am having success when the headers are on the first row. Here is the script:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;
Database= C:\Data\MyExcel.xls', 'SELECT * FROM [MyTab$]')
How can I alter the syntax to make it work for when the headers are at row 4?
Thanks for your help on this!
October 1, 2010 at 11:36 am
thekrauseman (10/1/2010)
Hello All.I am trying to import excel files using the OpenRowSet command, and am having success when the headers are on the first row. Here is the script:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;
Database= C:\Data\MyExcel.xls', 'SELECT * FROM [MyTab$]')
How can I alter the syntax to make it work for when the headers are at row 4?
Thanks for your help on this!
The short version is... you can't as far as I know.
The ODBC drivers need to make some assumptions when working with the office products, and this is one of them.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 1, 2010 at 12:55 pm
i ran across an issue like this a few weeks ago. if you're doing these imports via ssis, you can add a script task before the import, i found something like this during my search
Public Sub Main()
Dim strFilePath As String
Dim objExcel As Object
'
strFilePath = "filepath\filename"
objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open(strFilePath)
objExcel.Rows("1:4").Delete()
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs(strFilePath)
objExcel.ActiveWorkbook.Close()
objExcel.Quit()
objExcel = Nothing
'
Dts.TaskResult = Dts.Results.Success
End Sub
you'll have to have excel installed wherever this is executed as it opens the file to delete the rows
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply