Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to 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
anshu77444
Rate this: bad
good
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: 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:
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. Smile | :)
  Permalink  
Comments
Marcus Kramer at 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
good
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
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
  Permalink  
Comments
anshu77 at 9-Nov-12 6:31am
   
THANK U SIR..................
Milind Thakkar at 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
0 OriginalGriff 330
1 Sergey Alexandrovich Kryukov 295
2 Gihan Liyanage 222
3 Abhinav S 172
4 ClimerChinna 167
0 Sergey Alexandrovich Kryukov 8,188
1 OriginalGriff 6,991
2 CPallini 2,598
3 Richard MacCutchan 1,925
4 Abhinav S 1,736


Advertise | Privacy | Mobile
Web04 | 2.8.140826.1 | Last Updated 9 Nov 2012
Copyright © CodeProject, 1999-2014
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