I am trying to import some excel file data into a SQL table. I thought of using OPENROWSET for this. The issue is that the csv file is location in a different machine in the same network. How do I read this excel file? If this cannot be done using OPENROWSET, are there any other alternatives? Please advice.Please note that I cannot go with Linked servers as I don't have the privileges to client's machine to install relevant drivers.
3
-
is this one time that you need data to be imported or its like getting sync after some schedule? if one time , why not bring that excel file over that sql server (using robocopy or shared copy path) and then do adhoc query using OPENROWSET?– KASQLDBACommented Oct 13, 2015 at 4:11
-
its not one time reading. The file is frequently read over every 4 hour period. Is there any other alternatives available than copying it to the current machine?– AnOldSoulCommented Oct 13, 2015 at 4:40
-
beware of permissions: usually sql server process is executed as a local unprivileged user that has no way to access remote resources.– PaoloCommented Oct 13, 2015 at 9:27
Add a comment
|
2 Answers
The SQL Server Import and Export Wizard offers the simplest method to create a Integration Services package that copies data from a source to a destination.
This will create a package for you, which you can schedule via SQL agent job as explained in Scheduling SSIS Package Execution in SQL Server Agent, as per the desired refresh frequency.
Also, refer to this 10 steps package creation for importing data from excel to SQL table as described by Andy Moving Data From Excel to SQL Server - 10 Steps to Follow
This article uses SSIS 2012, the latest version at the time of writing, but you won’t notice many differences if you’re using 2008 or 2005.
While you could schedule an JET OLEDB type query as a SQL Agent job with something like the code below:
SELECT *
FROM OPENDATASOURCE( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\PathToExcelFile\ExcelFile.xls;Extended Properties=Excel 8.0')...[Sheet1$]
I would only recommend this approach if you have not, can not or do not want to install SSIS. If you are planning on regularly importing the contents of this file you will be better off using SSIS as you have things like error handling and data transformations that may be easier for you to configure in SSIS instead of writting them from scratch in T-SQL.
The OLEDB solution may be the quick solution and require no extra applications other than the Jet OLEDB driver, but SSIS will be a better long term solution. I would recommend you research SSIS and try to make a package to insert the data in a test environment.
answered Oct 13, 2015 at 6:55
