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

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

alt text

Step 2: pick your SQL Server target database

alt text

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

alt text

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

alt text

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

alt text

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

alt text

how can I do this by adding a script, I wan't to import data into 3 tables periodically?Is there a way to create a format file or something to directly write a query/script to get the data? We could do this in SQL Server 2005 but can we do it directly for 64-bit machines?

Amy 2010-08-13 15:54:03

@Amy: you would have to step through the wizard once manually, to set everything up. Once done, you can save the steps as a SSIS package into SQL Server and have that executed at any given time, by scheduling it in SQL Agent. Works on any machine - 32 or 64 bit - no problem.

marc_s 2010-08-13 15:59:33

Hi, first of all thanks for your help!! I'm trying to create column mapping for Excel data to SQL table. Say Excel table1 has col1, col2, col3. And SQL table1 has col1, col2a, col3a. Can you think of a way to maintain current information and do this?

Amy 2010-08-13 20:29:18

Meant to update the comment, but anyway, Thanks a lot - works!

Amy 2010-08-16 00:23:15

ansaurus

tags:

views:

answers:

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

related questions