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?

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 <img src="https://i.stack.imgur.com/w1AjS.png" alt="enter image description here"> Step 2: pick your SQL Server target database <img src="https://i.stack.imgur.com/kHvHl.png" alt="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! <img src="https://i.stack.imgur.com/95q8P.png" alt="enter image description here"> Step 4: check (and change, if needed) your mappings of Excel columns to SQL Server columns in the table: <img src="https://i.stack.imgur.com/9e0U6.png" alt="enter image description here"> Step 5: if you want to use it later on, save your SSIS package to SQL Server: <img src="https://i.stack.imgur.com/PLHHX.png" alt="enter image description here"> Step 6: - success! This is on a 64-bit machine, works like a charm - just do it!!

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

Tags:

sql

sql-server-2008

import-from-excel

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?

586

asked Aug 13 '10 05:08

Loser Coder

1 Answers

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

100

answered Oct 13 '22 16:10

marc_s

Related questions
                            
                                Add an incremental number in a field in INSERT INTO SELECT query in SQL Server
                            
                                Control flow in T-SQL SP using IF..ELSE IF - are there other ways?
                            
                                MySQL LIKE query with underscore
                            
                                Doing DateTime Comparisons in Filter SQLAlchemy
                            
                                Optimizing Delete on SQL Server
                            
                                Check if current date is between two dates Oracle SQL
                            
                                SqlConnection.Close() inside using statement
                            
                                Return a value if no record is found
                            
                                Mixing explicit and implicit joins fails with "There is an entry for table ... but it cannot be referenced from this part of the query"
                            
                                Selecting distinct combinations
                            
                                SQL Database Design Best Practice (Addresses)
                            
                                Why Stored Procedure is faster than Query
                            
                                How do you OR two LIKE statements?
                            
                                SQL Server 2005 and temporary table scope
                            
                                With Entity Framework is it better to use .First() or .Take(1) for "TOP 1"?
                            
                                SQL Server : How to test if a string has only digit characters
                            
                                How does a Recursive CTE run, line by line?
                            
                                Slow simple update query on PostgreSQL database with 3 million rows
                            
                                How can I declare a Boolean parameter in SQL statement?
                            
                                Whats the best SQLite data type for a long string

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!

Donate Us With

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

Tags:

sql

sql-server-2008

import-from-excel

Loser Coder

People also ask

1 Answers

marc_s

Recent Activity

Donate For Us