snippetsqlMinor
How to read an excel file from a network machine using SQL?
Viewed 0 times
fileexcelreadsqlusinghowmachinefromnetwork
Problem
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.
Solution
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.
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.
Context
StackExchange Database Administrators Q#117811, answer score: 4
Revisions (0)
No revisions yet.