How to export data from Excel spreadsheet to Sql Server 2008 table

sql sql-server-2008 import-from-excel
Loser Coder picture Loser Coder · Aug 13, 2010 · Viewed 186.1k times · Source

I want to import data from an Excel file - assume Excel 2003 / .xls - to Sql Server 2008.

Have tried adding a linked server to the JET OLE DB Access driver, and of course it fails on the 64-bit machine. But when I try to drop the linked server while experimenting, there's another error saying that the linked server already/still exists!

I have also tried changing the Excel driver to 32-bit (regedit tool) but am not sure if it's doing anything, same error is coming up!

Some more details: Say Table1 in Excel file has 5 columns. I want to map to Database.dbo.Table1 with 5 columns again, but with different names in table. Is there a way to do even do such an import?

Answer

marc_s picture marc_s · Aug 13, 2010

From your SQL Server Management Studio, you open Object Explorer, go to your database where you want to load the data into, right click, then pick Tasks > Import Data.

This opens the Import Data Wizard, which typically works pretty well for importing from Excel. You can pick an Excel file, pick what worksheet to import data from, you can choose what table to store it into, and what the columns are going to be. Pretty flexible indeed.

You can run this as a one-off, or you can store it as a SQL Server Integration Services (SSIS) package into your file system, or into SQL Server itself, and execute it over and over again (even scheduled to run at a given time, using SQL Agent).

Update: yes, yes, yes, you can do all those things you keep asking - have you even tried at least once to run that wizard??

OK, here it comes - step by step:

Step 1: pick your Excel source

enter image description here

Step 2: pick your SQL Server target database

enter image description here

Step 3: pick your source worksheet (from Excel) and your target table in your SQL Server database; see the "Edit Mappings" button!

enter image description here

Step 4: check (and change, if needed) your mappings of Excel columns to SQL Server columns in the table:

enter image description here

Step 5: if you want to use it later on, save your SSIS package to SQL Server:

enter image description here

Step 6: - success! This is on a 64-bit machine, works like a charm - just do it!!

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Rename column SQL Server 2008

I am using SQL Server 2008 and Navicat. I need to rename a column in a table using SQL. ALTER TABLE table_name RENAME COLUMN old_name to new_name; This statement doesn't work.

Read More