Caveman's Blog

My commitment to learning.

Archive for the ‘MS-Excel’ Category

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:

Advertisements

Written by cavemansblog

July 28, 2012 at 5:47 pm

MS-Excel: How to filter out distinct items in a column?

leave a comment »


I am not the savviest of Excel users, however I chanced upon this one tip that I would like to share with you. A simple way to filter out distinct items in a column in an Excel file would be to:

1. Highlight the column to be filtered

2. Click on the Data menu; Data –> Filter –> Advanced Filter.

3. Choose the list range and check the “Unique records only”.

Bonus tip: Ctrl + Shift + L is the keyboard shortcut to enable a filter on a column.

Written by cavemansblog

March 31, 2011 at 7:05 am

MSExcel: Copy & Paste data, not the formula

leave a comment »


When you try to copy and paste the data in the formula cell in MS Excel, you will observe that the formula gets copied instead of the actual data. One way to copy the actual data is:

1. Copy the source cell(s)

2. Use “Paste Special” instead if Paste to copy the data to your destination cell(s)

3. Choose values and hit ok.

Have fun !

Written by cavemansblog

January 5, 2011 at 7:57 pm

Posted in MS-Excel