Caveman's Blog

My commitment to learning.

Posts Tagged ‘import from excel

SQL Server: Data transfer for Excel using Linked Server

leave a comment »


Introduction

I will demonstrate yet another way to perform data transfer to and from an excel file using a Linked Server in SQL Server 2008. In this demo I will show you how to:

  • Create a Linked Server using Excel as the data server
  • Import data to a SQL Server table from a spreadsheet
  • Export data to a spreadsheet from a SQL Server table

Create a Data Source

As the first step in this process I have created an Excel file with a spreadsheet named “Employee”, followed by defining the headers and inserting 5 records as showing in following illustration:

Create a Linked Server

Now that we have a data source let us create a linked server using an the Excel file created in the previous step as the data source. Open SQL Server Management Studion (SSMS) and expand “Server Objects” under the intended SQL Server, to find the Linked Servers item.Right click on Linked Servers and click on “New Linked Server” to see following dialog box. EXCEL_LINKED_SERVER is the name that I have chosen to call this linked server. Then we need to populate the Provider, Product Name, Data Source and Provider String like in this example and click the OK button.

A linked server should have been created successfully at this time. Right click on the “Linked Server” tree view item and click on refresh to see the newly created linked server. Expand the linked server to see the Exployee$ spreadsheet under the list of tables.

Import Data

At this point you should be able to access the Excel spread sheet just like any other SQL Server database table.  Let us import data into a table named DataFeed in the Demo database. It is important to note that the spreadsheet name in the SQL query has to be accessed only by preceding spreadsheet name  with “…”  Only one dot is used to represent the current database, two dots to represent another database in the SQL Server instance and three dots represent a Linked Server. When we would have ran the following SQL query, a table named “DataFeed” would have been created and 5 records from this table would be displayed in the results pane.

Export Data

Let us insert a couple of records into the DataFeed table that was created in the above step, followed by exporting those two rows to the spreadsheet. When the following query us run, you will observe that the spread sheet will have 7 records.

Now Let us look at the new data in the spreadsheet:

Written by cavemansblog

July 28, 2012 at 5:47 pm

SQL Server: Import data from Excel

with 3 comments


This article will demonstrate one of the several ways of importing data into a SQL Server table from an Excel spread sheet. I will use a simple SQL query to import data instead of SSIS/DTS. The precursor to achieving the data transfer will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server database. This can be accomplished by using the SQL Server 2005 Surface Area Configuration Utility or by executing the following code in SQL Server Management Studio’s Query Editor/Query Analyzer:

sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
reconfigure

When the server is capable of running Ad Hoc Distributed Queries we are all set to run SQL queries to import data from an MS-Excel spreadsheet. It must kept in mind that an Excel file with all the intended columns and data has to exit, before data can be imported (also the file must be closed when data is transferred). In the following code sample, data from the spreadsheet “EMP” in the Excel file, will be imported into the “employee” table in the SQL Server database using the OPENROWSET.

--Export data to Excel
select * into Employee from openrowset('Microsoft.Jet.OLEDB.4.0' ,
'Excel 8.0;Database=c:\test\test.xls','Select * from [EMP$]')

In the near future I will demonstrate how to import multiple spreadsheets into SQL Server database using SSIS, via dynamic discovery of all available spreadsheets in one Excel file.

References:
1. Server Settings Configuration Options