how to import data from excel sheet to sql server 2005

Question

3.00/5 (1 vote)

See more:

ASP.NET

sir,
i want to import datafrom excel sheet to sql server 2005

plz, rply
thanks in advnc

Posted 8-Nov-12 20:02pm

anshu77

Add a Solution

2 solutions

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.,

SQL

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: http://www.connectionstrings.com/excel

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:

SQL

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. :)

Posted 8-Nov-12 20:16pm

Gautam Raithatha

Comments

fjdiewornncalwe 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.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

MT_ · Accepted Answer · 2012-11-08T20:19:00

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
http://code.msdn.microsoft.com/CSASPNETExcelImportExport-71cf1101[^]

B. Write a DTS package. Refer a link below.

http://www.databasejournal.com/features/mssql/article.php/3325731/Import-Multiple-Files-to-SQL-Server-Using-DTS.htm[^]

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.

Thanks
Milind