Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Import Excel data into SQL Database Expand / Collapse
Author
Message
Posted Thursday, November 30, 2006 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 27, 2007 6:02 AM
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!

Post #326846
Posted Thursday, November 30, 2006 3:04 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:33 PM
Points: 22,475, Visits: 30,157

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.

 




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)
Post #326855
Posted Thursday, November 30, 2006 8:19 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, March 28, 2014 8:30 AM
Points: 282, Visits: 503
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
Post #326902
Posted Friday, December 01, 2006 8:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, December 30, 2013 11:07 AM
Points: 195, Visits: 75

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

 

Post #326993
Posted Friday, December 01, 2006 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 01, 2006 8:36 AM
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.
Post #327013
Posted Friday, December 01, 2006 12:40 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 14, 2012 5:15 PM
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.
Post #327118
Posted Friday, December 01, 2006 1:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 27, 2007 6:02 AM
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

Post #327143
Posted Friday, December 01, 2006 1:53 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, December 14, 2012 5:15 PM
Points: 442, Visits: 122

Paul,

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

Post #327151
Posted Monday, December 04, 2006 7:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 27, 2007 6:02 AM
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.

Post #327361
Posted Monday, December 04, 2006 9:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 02, 2013 9:22 AM
Points: 386, 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

 

Post #327470
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse