Import Excel data into SQL Database
-
How do I import Excel data into SQL Database? I've done it before but can't remember how.
I've been searching around on the net and can't find a straight answer. Why can't it be as easy as Access!
-
Easiest way I have found is to export (or save) the data from Excel as a CSV file and import hte data from that file.
-
You can create a dataflow task in an SSIS package with an Excel file source and do any manipulation you need to and pump it into your SQL DB as well..
Thanks,
Rich
-
Step 1
======
Create an Excel file like this, starting from A1. Note that there are five fields. The fifth field does not have any
values except for one row.
Col1 Col2 Col3 Col4 Col5
111 aaa 12/1/2006 111aaa
222 bbb 12/1/2006 222bbb
333 ccc 12/1/2006 333ccc
444 ddd 12/1/2006 444ddd
555 eee 12/2/2006 555eee nullvalues
666 fff 12/2/2006 666fff
777 ggg 12/2/2006 777ggg
888 hhh 12/2/2006 888hhh
999 iii 12/2/2006 999iii
Save the file as MyExcelFile2
Step 2
======
Start -> All Programs -> Microsoft SQL Server -> Enterprise Manager
Open up your server in the left pane of Enterprise Manager and select Data Transformation Services
Right click on Local Packages and select New package
Step 3
======
Drop a "Microsoft OLE DB Provider for SQL Server" connection object onto the designer.
Change the "New Connection:" value to DBConn
Select your Server: I am selecting [local]
Select your Database: I am selecting JambuDB
Click on OK
Step 4
======
Drop a "Microsoft Excel 97-2000" connection object onto the designer.
Change the "New Connection:" value to ExcelConn
For the FileName: option selec the Excel file you created in Step 1.
This is what I am selecting: C:\whatever\MyExcelFile2.xls
Click on Ok
Step 5
======
Click on "Transform Data Task" and first click on the ExcelConn and then on the DBConn
You should see an arrow point to DBConn from ExcelConn
Double click the arrow and the change the Description: to ExcelToSQLServer2000Task on te Source tab
On the Destination tab click on Create button and you should see something like this
CREATE TABLE [Sheet1$] (
[Col1] float NULL,
[Col2] nvarchar (255) NULL,
[Col3] smalldatetime NULL,
[Col4] nvarchar (255) NULL,
[Col5] nvarchar (255) NULL )
Change the table name if you wish. For this demo, I am leaving as is.
Click on OK
Click on OK
Step 6
======
That's it. Execute the package and you should the data in your database now.
Hope it helps.
PS: This particular activity should not have any difference in SSIS. Even if it is there, if we can do in 2000, we can
easily do it in SSIS.
jambu
-
The way I import Excel data into SQL Database is first create your Excel file; then go to SQL Enterprise Manger and drill down to the database you want the information imported in and click Tables; on the right hand side of the window you will see all the tables in that particular database; right click on one of the tables and choose from the menu All Tasks,Import Data; this will take you to the DTS Import/Export Wizard; click next; Choose a Data Source (here you will click on the drop arrow and choose Excel) and find your excel file that you want to import and click Next; Choose a destination; click next; Specify Table Copy or Query (copy Table(s) and view(s) from the source database should have a black dot to the left of it, click Next; if your spreadsheet has more than one workbook check the workbook that has your information and click Next; Save, schedule and replicate package box appears click Next and this puts your Excel file into a table.
-
If you're just having a hard time because you're working on 2005 then here's the secret... Right-click on the database name, choose Tasks, and then Import Data.
-
Thanks for all the quick responses. I guess I'll need to get a copy of Enterprise Manager. I know I've done an import before throught C#, no programing, and without any other software but just can't remember how.
Cindy, under Tasks, there is no Import Data option. I can't find an import option anywhere in SQL Management Studio 2005 or C#.
Thanks for the help,
Paul
-
Paul,
Are you sure you are right-clicking on the database name (not the table name)?
-
Cindy,
That's correct, I'm right clicking on the database name. The problem might be beause I'm using an express version of SQL Management Studio.
-
Another way to do it:
1. Copy your Excel data into text file - it will come as tab-delimited file.
2. Create target table in SQL Server.
3. Run BCP IN in DOS prompt, its format should be something like this (without line breaks):
bcp db_name.schema_name.table_name in c:\your_path\your_text_file_name.txt -c -S server_name -T -F 2 -e c:\your_output_error_file_name.txt
-F 2 means to skip column headers
-T means trusted connection
-
Hi Paul Mc,
If you are only importing the data once, you can also copy the data from Excel into the clipboard and paste it directly into the target table using the Server Management Studio (SQL 2005). Quite a handy method if you are importing data just once/a few times by hand.
Regards,
WilliamD
-
OPENDATASOURCE: It Is a one way to work with Excel or other third party Data in SQL. Some how U may utilize it.
Query - The reference Book: Microsoft SQL Server 2005 - Applied Techniques
SELECT
Employees.FirstName,
Employees.LastName,
Employees.Title,
Employees.Country
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=C:\Documents and Settings\User\My Documents\Microsoft Press\SQLAppliedTechSBS\Chapter08\EmployeeList.xls')...[Employees$] AS Employees
WHERE LastName IS NOT NULL
ORDER BY Employees.Country DESC
-
It's interesting thing.
But when I ran it I got a message:
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered.
How to register it ?
-
Ad hoc Query: Ad hoc queries provide the ability to open a connection to a remote data source and query it from within T-SQL code. This one-time connection lives just throughout the duration of the operation currently being executed. User can access data directly from Oracle,Excel,Access,Flat File or other third party db in a Single Query.
Enabling Ad Hoc Queries
sp_configure 'show advanced options',1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE;
In case of Excel be sure that there should not be space in Sheet Name which U R accessing and Workbook should be close, then it work other wise will give error.
-
hi....
The information is given by you is good for 2000 sql server .but it is no fit in sql 2005 server. So you have any idea about import excel file in 2005 srever
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply