46

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?

Loser Coder
  • 2,338
  • 8
  • 42
  • 66

3 Answers3

92

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!!

Walter Stabosz
  • 7,447
  • 5
  • 43
  • 75
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 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? – Loser Coder Aug 13 '10 at 15:54
  • @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 Aug 13 '10 at 15:59
  • 1
    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? – Loser Coder Aug 13 '10 at 20:29
  • Meant to update the comment, but anyway, Thanks a lot - works! – Loser Coder Aug 16 '10 at 00:23
  • 7
    you should get a Medal for this Answer! – Malachi Oct 18 '12 at 16:45
  • The method proposed in here: http://www.excel-sql-server.com/excel-sql-server-import-export-using-excel-add-ins.htm should be much better and simpler than the proposed solution for most people. – ihightower Apr 17 '15 at 09:50
  • After few days of search, i ve found this solution. Seems the simplest solution because it is a microsoft process and you can customize a lots od things, example while you put excel into sql db, you can modify the sql generated code. – Emanuel Pirovano Apr 06 '18 at 12:55
2

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.

Michael Geller
  • 74
  • 2
1

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.

Nikita R.
  • 7,245
  • 3
  • 51
  • 62