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

Question

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

User · Answer

There are several tools which can import Excel to SQL Server.

I am using DbTransfer (http://www.dbtransfer.com/Products/DbTransfer) to do the job. It's primarily focused on transfering data between databases and excel, xml, etc...

I have tried the openrowset method and the SQL Server Import / Export Assitant before. But I found these methods to be unnecessary complicated and error prone in constrast to doing it with one of the available dedicated tools.

User · Answer

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

User · Answer

In SQL Server 2016 the wizard is a separate app. (Important: Excel wizard is only available in the 32-bit version of the wizard!). Use the MSDN page for instructions:

On the Start menu, point to All Programs, point toMicrosoft SQL Server , and then click Import and Export Data.
—or—
In SQL Server Data Tools (SSDT), right-click the SSIS Packages folder, and then click SSIS Import and Export Wizard.
—or—
In SQL Server Data Tools (SSDT), on the Project menu, click SSIS Import and Export Wizard.
—or—
In SQL Server Management Studio, connect to the Database Engine server type, expand Databases, right-click a database, point to Tasks, and then click Import Data or Export data.
—or—
In a command prompt window, run DTSWizard.exe, located in C:\Program Files\Microsoft SQL Server\100\DTS\Binn.

After that it should be pretty much the same (possibly with minor variations in the UI) as in @marc_s's answer.

[sql] How to export data from Excel spreadsheet to Sql Server 2008 table

The answer is

Examples related to sql

Examples related to sql-server-2008

Examples related to import-from-excel

Tags