asked
Bulk Insert Data (Excel File)
Can you please advise me which is the best method of importing data into SQL from Excel (2007), Bulk Insert or OpenRowSet. Currently I've tried both and neither is working and before I continued to battle I thought I'd see which is the best option. Many thanks
You can try to Install the [2007 Office System Driver: Data Connectivity Components][1]. Then you should be able to SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\pathToExcelFile.xlsx;HDR=Yes', 'Select * from [Sheet1$]') Eventualy HDR=No if there are no columns headers. [1]:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=7554f536-8c28-4598-9b72-ef94e038c891
If it were me I would use SSIS and use an EXCEL Source and handle the import there. You could also use a data conversion task to handle any data manipulation that might be needed.
I put forward the proposal that we do it in SSIS on a 2008 server and just push the data into the 2000 server but essentially unless I can put forward a really good case it needs to all stay within SQL 2000.
So would I as a preference, unfortunately that's not an option as it's a SQL 2000 database, it's either code or DTS, have opted for the code option curently.
Well that is no fun. How much data is in the Excel file? I am lucky that in my environment I would still use SSIS and just use my OLE Destination as my 2000 server. I have a dedicated ETL server that is 2008 R2 that I use to connect to everything from flat files, Excel, SQL, Oracle, and darn near everything in between.
Not a lot, maybe 1000 rows, the problem is that I need to write a process that can easily be utilised for many sources of data and many destinations so it's getting a bit complex.
