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

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

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

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

Examples related to sql

Examples related to sql-server-2008

Examples related to import-from-excel