June 7, 2005 at 7:37 am
hi everybody,
I am maintaining a system which is importing CSV files into SQL Server. we use BULK insert from a Visual basic program,now client also want us to import Excel files into SQL Server DB.
can anybody tell me whats the best meathod of importing Excel file
June 7, 2005 at 7:46 am
There is no real difference between csv and excel. You just need to go into the task and make sure the columns are still mapping OK.
Bulk insert works with excel the same as with csv.
June 7, 2005 at 7:49 am
Thanks a lot Jonathan.
June 7, 2005 at 7:57 am
I jut realised I gave you a bit of a bum steer. I forgot that you need to save the speadsheet as a text file first. You cannot import excel directly into a sql table using bulk insert.
You can write a bit of vb code to do this or you can set up a transform data task between the spreadsheet and the db, but it will be slower.
June 7, 2005 at 8:11 am
can we save Execl file to txt, i mean just save as would work, Its format wont be change.
June 7, 2005 at 8:33 am
You can do it manually but you are better of using code. This function will work for you.
Just pass in the filename with .xls extension plus the tabs you want to save. It saves as documentname + tabname + .txt
You cna chnage it around if you only have one tab.
Public Function fnSaveAsText(filename, tabStart, tabEnd)
On Error GoTo Err_fnSaveAsText
Open "c:log.log" For Output As #1
' create the excel object
Dim ExcelApp As Excel.Application
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.Visible = False ' hide the application
ExcelApp.DisplayAlerts = False ' ignore application warnings
ExcelApp.Interactive = False ' no interaction on application
Dim TextSaveName, ExcelOpenName As String
Dim path As String
Dim tabCount As Integer
path = "C:\"
ExcelOpenName = path & filename ' get name and path of excel doc
' get name and path of text doc to be saved
TextFileName = path & Left(filename, Len(filename) - 4)
' open the excel file and disable link updates
ExcelApp.Workbooks.Open (ExcelOpenName)
ExcelApp.ActiveWorkbook.UpdateRemoteReferences = False
' save each tab selected as text
For i = tabStart To tabEnd
ExcelApp.Sheets(i).Select ' focus on the sheet
TextTabName = ExcelApp.Sheets.item(i).Name ' get the name of the sheet
TextSaveName = TextFileName & "_" & TextTabName & ".txt" ' generate a savename
ExcelApp.ActiveWorkbook.SaveAs filename:=TextSaveName, FileFormat _
:=xlText, CreateBackup:=False ' save is as text
Next i
ExcelApp.Workbooks.Close ' close all open documents
ExcelApp.Interactive = True ' turn back on interaction
' Error handling
Err_fnSaveAsText: ' always come here regardless
' seek and destroy open objects
ExcelApp.Quit
Set ExcelApp = Nothing
If err.Number <> 0 Then
Print #1, err.Number & " - " & err.Description
End If
Close #1
End Function
June 7, 2005 at 8:36 am
Thanks
June 8, 2005 at 12:38 am
with sql:
SELECT * INTO XLSImport1 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\xltest.xls', 'SELECT * FROM [Sheet1$]')
June 8, 2005 at 1:02 am
Hi,
you can also add the Excel file as a linked server:
EXEC sp_addlinkedserver 'EXCELSOURCE',
'ExcelSource',
'Microsoft.Jet.OLEDB.4.0',
'C:\XLS_File.xls',
NULL,
'Excel 5.0'
select *
into XLS_Import_Table
from EXCELSOURCE...[Sheet1$]
sp_dropserver 'EXCELSOURCE', 'droplogins'
Regards
Matthias
October 31, 2007 at 4:13 am
When I execute this code i get the following error...
"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]."
October 31, 2007 at 8:47 pm
Does another user have the file open on their desktop?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 13, 2008 at 1:58 am
This won't work on the 64bit version of SQL Server as 'Microsoft.Jet.OLEDB.4.0' isn't supported.
Works fine on 32bit but for some reason it isn't supported on 64bit.
May 12, 2008 at 7:48 pm
hi,
I am getting the same error on 32- bit SQL Server. can you please clarify the same.
May 13, 2008 at 2:59 pm
Which error? The unable to open error?
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
May 14, 2008 at 9:41 am
When I execute this code i get the following error...
"OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
[OLE/DB provider returned message: Cannot start your application. The workgroup information file is missing or opened exclusively by another user.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.]."
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply