Click here to Skip to main content
12,557,296 members (51,048 online)
Rate this:
Please Sign up or sign in to vote.
See more: ASP.NET
i want to import datafrom excel sheet to sql server 2005

plz, rply
thanks in advnc
Posted 8-Nov-12 20:02pm
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

u can read data from excel sheet in the same way u do from database. Only the connection string will change depending on microsoft office version.

For eg.,
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";
If it dont work, u can search others from:

Then for data access query, thhe table name will be the sheet name with '$' sign appended. For eg, suppose sheet name is "Sheet1", query will be:
select * from [Sheet1$]
U can read data by using your data access method (e.g. OledbConnection and OledbCommand) into DataTable. After that, using ur method, u can insert into sql server database. :)
Marcus Kramer 9-Nov-12 12:27pm
Gautam, please use full words in your answers. (ie. Remove the textspeak) We are professionals and prefer to converse as such. Professionals don't use textspeak. Cheers.
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

If this is one time activity that you want to perform, there are couple ways to do the same.

1. right click the table in the database and click "open table". Now copy the data from excel with ctrl+c and then paste in the SQL table. If the columns are same it should work

2. Right click on your database and click Tasks and then "Import Data". This will open import wizard. (Just like when you import a text file in excel). PS: This is not available in SQL Express version

If you need to this frequently, again there are couple of options

A. Write your own C# code, this will be the most reliable way as you are in control. Refer to link
Import Data from Excel to SQL Server[^]

You can use ready-made code from[^]

B. Write a DTS package. Refer a link below.[^]

Since the requirement given in your question is not clear, I am trying to give you various options. Choose the best that suits your requirement.

Hope that helps. If it does, mark it as answer and/or upvote.

anshu77 9-Nov-12 6:31am
THANK U SIR..................
Milind Thakkar 9-Nov-12 7:19am
welcome. if it helped, upvote.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.161026.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100