Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Import Excel data into SQL Database


Import Excel data into SQL Database

Author
Message
Paul Mc
Paul Mc
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

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!


Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24255 Visits: 37978

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.



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Rich Hansell
Rich Hansell
SSC Veteran
SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)SSC Veteran (283 reputation)

Group: General Forum Members
Points: 283 Visits: 633
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
Jambu Krishnamurthy
Jambu Krishnamurthy
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 98

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


Donna Hodo
Donna Hodo
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 1
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.
Cindy Hallum
Cindy Hallum
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 122
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.
Paul Mc
Paul Mc
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

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


Cindy Hallum
Cindy Hallum
SSC-Addicted
SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)SSC-Addicted (442 reputation)

Group: General Forum Members
Points: 442 Visits: 122

Paul,

Are you sure you are right-clicking on the database name (not the table name)?


Paul Mc
Paul Mc
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1

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.


Mark Shvarts 1
Mark Shvarts 1
Old Hand
Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)Old Hand (390 reputation)

Group: General Forum Members
Points: 390 Visits: 441

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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search