How to Export Data from Excel Spreadsheet to SQL Server 2008 Table

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

Step 2: pick your SQL Server target database

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

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

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

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

How do I import an Excel spreadsheet into SQL Server 2008R2 database?

There is a microsoft knowledge base article that lays out all the ways this is possible.

http://support.microsoft.com/kb/321686

I think using OPENROWSET or OPENDATASOURCE will be the easiest way, without the wizard. (see Distributed Queries)

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

See OPENROWSET documentation, with examples lower down the page.

http://msdn.microsoft.com/en-us/library/ms190312.aspx

Manually

Right click on the database name/go to task and then select import data, as a source select an excel file that you created before and choose it's path on the next page select sql server as destination

Import Excel Spreadsheet Data to an EXISTING sql table?

Saudate, I ran across this looking for a different problem. You most definitely can use the Sql Server Import wizard to import data into a new table. Of course, you do not wish to leave that table in the database, so my suggesting is that you import into a new table, then script the data in query manager to insert into the existing table. You can add a line to drop the temp table created by the import wizard as the last step upon successful completion of the script.

I believe your original issue is in fact related to Sql Server 64 bit and is due to your having a 32 bit Excel and these drivers don't play well together. I did run into a very similar issue when first using 64 bit excel.

Import Excel to SQL Server 2008

If you're limited solely to TSQL, the above two answers will show you some ideas. If you have access to either Data Tools or Business Intelligence, with SSIS, you can automate it with the assumption that each sheet in the Excel workbook matches each time. With SSIS, you'll use a Data Flow task and each sheet will be imported into the table that you want. When you're ready for the file the next week, you'll drop it into the folder and run the SSIS package.

However, if the sheet names change, (for instance, one week sheets are called Cats, Dogs, Rain and the next week it's Sulfur, Fire, Hell) then this would cause the package to break. Otherwise, if only the data within the worksheet change, then this can be completely automated with SSIS.

Example article: https://www.simple-talk.com/sql/ssis/moving-data-from-excel-to-sql-server---10-steps-to-follow/

How to load excel data into sql server 2008 table?

The simpliest way to import XLS data directly into SQL is to use the Import Wizard.

You can do this either by having the import wizard create the table for you when the wizard runs, alternatively, you could create the table before hand and then use the wizard and use that table as your target table.

I prefer the latter method as I like to control my table creation with my desired datatypes.

To find the Import Wizard, simply right-click on the Database name, Tasks -> Import Data

Import data to SQL Server table from Excel

Found out that it was to do with the excel file. Before opening the excel file i got this error message "The file you are trying to open .xls is in a different format than specified by the file extension. Verify that file is not corrupted and is from a trusted source before opening the file. "
Opened it and copied its contents and created a new excel file from it. Then I used this file to import in the wizard. I specified Microsoft Excel as source with file name and Microsoft excel 2007 as version.
For the destination I chose SQL Server Native client 11.0, Server name and database. I chose edit mappings to map the fields between excel sheet and target table. After running the wizard it transferred the data to the target table in sql server.

Export excel data to sqlserver

Maybe you would like to try a tool I have developed? It's an Excel Add-In that exports Excel data to SQL Server.

There is a feature to automatically export the data to SQL Server every time you press the save button in Excel. If you need to update the database every time a cell value change, you will need to add a few lines of VBA-code that will push the data to SQL Server.

To beta testers I currently give away a free license, so if you are interested in testing it out, send me an email:)

www.sqlpreads.com

---

Related Topics