.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Reading Excel data to SQL database

Posted By:      Posted Date: September 23, 2010    Points: 0   Category :ASP.Net


Using C# to develop an application.

I need to upload data ( which is in excel) to a sql table.

This is the code I have.

public ReadExcel(string strFileName,string strRegion)
        string strFilePath = string.Concat("c:/BulkUpload/", strFileName);
        myDataSet = new DataSet("Excel_DataSet1");
        string myConnectionString;
        OleDbConnection myConnection;
        OleDbDataAdapter myAdapter;
        myConnectionString = "provider=Microsoft.Jet.OLEDB.4.0;data source='" + strFilePath + "';Extended Properties=Excel 8.0;";
        myConnection = new OleDbConnection(myConnectionString);

        myAdapter = new OleDbDataAdapter("select * from [sheet1$]", myConnection);

        DataTable dt = myDataSet.Tables[0];
        int count = 0;
        foreach (DataRow r in dt.Rows)
            if (count != 0)
                DBConnect db = new DBConnect();
                db.AddData("INSERT INTO " + strRegion + "(Country,ProjectOwner,State,County,ProjectName,Quarter,Manager,Comments) VALUES ('" + r[1].ToString() + "','"

View Complete Post

More Related Resource Links

Insert Excel Data via File Upload into sql server database???

Hi all, I have requirement that User can Upload the Excel Sheet Data to sql server Database at once. How i do that Any Article or hint is apperciated.   Regards 

reading xml data and insert into database table

hi friends, .very urgent for me so only i send following script i create for read xml data and to import to table. i got result for reading xml and rows are added to table but the values are showing null pls clarify this problem. thanks alter procedure pizza_sales ( @xml xml ) as begin INSERT INTO pizza_xml (LOC_CODE, CUST_CODE, PRODUCT_ID, INV_TYPE, INV_NO, INV_DT, INV_QTY_IN_INV_UOM, INV_RATE_IN_BS_CURR, INV_VU_IN_INV_CURR, -- TOTAL_DISC_IN_BS_CURR, NET_INV_VU_IN_INV_CURR, NET_INV_VU_IN_BS_CURR, TOTAL_TXES_IN_BS_CURR ) select table1.column1.value('@LOC_CODE','nvarchar(75)'), table1.column1.value('@CUST_CODE','nvarchar(75)'), table1.column1.value('@PROD_CODE','nvarchar(75)'), table1.column1.value('@INV_TYPE','nvarchar(50)'), table1.column1.value('@INV_NO','nvarchar(100)'), table1.column1.value('@INV_DT','datetime'), table1.column1.value('@INV_QTY_IN_INV_UOM','numeric(28,8)'), table1.column1.value('@INV_RATE_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@INV_VU_IN_INV_CURR','numeric(28,8)'), --table1.column1.value('@TOTAL_DISC_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_INV_CURR','numeric(28,8)'), table1.column1.value('@NET_INV_VU_IN_BS_CURR','numeric(28,8)'), table1.column1.value('@TOTAL_TXES_IN_BS_CURR','numeric(28,8)') from @xml.nodes('pizza/pizzaxml')as table1(column1) end declare @id

Reading data from Excel 2007

I am attempting to read data from an uploaded spreadsheet using ACE.OLEDB. The code, which is running fine on dev and test machines for XL2003/2007 reports "External table is not in the expected format" error on connecting on the production server for XL2007 only. The code is Dim connectionString As String = "provider=Microsoft.Ace.OLEDB.12.0;" _ & "Data Source='" & ImportData.FullName & "';Extended Properties=Excel 12.0;" LogWebActivity.LogThis("Entering POPSUKD, ConStr=" & connectionString, LogWebActivity.LogDetailLevel.DetailAndData) Dim con As New System.Data.OleDb.OleDbConnection(connectionString) LogWebActivity.LogThis("Dimmed con", LogWebActivity.LogDetailLevel.Debugging) Try Dim cmdSelect As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & WorksheetName & "$]", con) Dim adapter As New System.Data.OleDb.OleDbDataAdapter(cmdSelect) Dim dS As New Data.DataSet LogWebActivity.LogThis(cmdSelect.CommandText, LogWebActivity.LogDetailLevel.Debugging) con.Open() LogWebActivity.LogThis("Opened Connection", LogWebActivity.LogDetailLevel.Debugging) adapter.Fill(dS, WorksheetName) LogWebActivity.LogThis("Filled DataAdapter", LogWebActivity.LogDetailLevel.Debugging) _SKUS = dS.Tables(W

Importing Excel 2007 data into SQL 2005 database

I have a SQL 2005 cluster installation without SSIS and without management studio (I didn't install it). Now we need to import data from an excel 2007 file into a specific table in a database. I have tried doing this from a remote management studio, BI development studio and DTSwizard but I can't seem to make it work. The error message from BIDS is that "the data source is on a remote computer..." Does any of you have a step by step  guide that works from a remote location. BR Rasmus    

Uploading excel data to sql database in asp.net application

Hi All,  What is the efficient way to upload excel data to sql database?excel file consists of 4000 rows..so i need to save these records to sql database in web application,i know only by creating ODBC connection i.e_path = @"Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + _path;OdbcConnection _xlsConn = new OdbcConnection(_path);_xlsConn.Open();OdbcCommand _xlsSelSht = new OdbcCommand("Select " + columns + " FROM [" + _sheet + "$]", _xlsConn);OdbcDataReader _xlsReader = _xlsSelSht.ExecuteReader();while (_xlsReader.Read()){.....}Is this efficient way ? keeping in mind speed because huge data needs to upload...   thanks in advance...  

Reading data from document library excel workbook



I am having Sharepoint 2010 Enterprise RTM Trial on windows Server 2008 SP2. 

I have Document Library in my site. I want to display the contents of excel workbook placed in that library in a webpart (webpart like List webpart not Excel Web access webpart). If workbook is updated then the webpart should also update....

Can it be done through workflows ? or any other webpart that provide me the desired behaviour ?


Reading and Writing Data to/from Excel File


I need to read and write data from excel sheet. Firstly for example I need to use the concept of accessing workgroup, sheet and then cells whatever I want.

For example I need to use cell A1+B1 and put the result in C1

alternatively I can use the same function above and put data in some other sheet of same excel file.

Please reply the answer either using C#.NET or asp.net using C#.NET

Reading excel data in SSIS Script task


I am reading an excel file using OleDbDataReader.

I can read all cells from the sheet. But when I read a string value its returning me a NULL value. If I replace that cell with a Integer value I can get it.

Basically I am doing a validation on the cells to see if there are any non-numeric values. For certain cells which has string value I can get it as string and I can do the validation but for certain others I cant. Its returning as NULL. Any help would be really helpful.




Export Visio Database Table Names to Excel

If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

display data into GridView from excel sheet

I have to display data from excel sheet into my gridview and also I need to figure out a way to store data into excel files from my web page. I am using C# 3.5, VS 2008.

Creating an Excel Spreadsheet and Adding Data to It Programmatically

The Interoperability services make it veryeasy to work with COM Capable Applications such as Word and Excel. Thisarticle reveals using Excel from a managed application. Excel is thespreadsheet component of Microsoft Office 2000. The majority of Excelprogrammatic functionality is exposed through Automation via the typelibrary Excel9.olb. The intention of this article is to express that amanaged application can interrelate with Excel as a COM server.

Reading and Writing Images From a Windows Mobile Database using UltraLite 10(C#)

Periodically I get a request for information on how to read and write binary data to a database running on Windows Mobile. If you search the Internet you can typically find examples that are available on Windows Desktops or allow you to read and write to a local file system. The problem is that it can take a bit of work to get this code to work on Windows Mobile accessing a database.

Ultimately you might be asking, why would I want to store and image in a database? Well in an environment where you synchronizing data between a local mobile database and a consolidated (central) database this can be extremely useful. Imagine if an insurance adjuster went to an accident scene, took a picture of a damaged car, loaded it into his Windows Mobile database and then replicated that image up to the Insurance headquarters for approval. All of this could be done in a very short period of time when using images in the database. Another good example might be a doctor who was waiting for a patient chart to become available. If you could store the image in a database this chart could be sent down to the doctor's device once it became available.

For this article I am not going to get into how to synchronize the images to and from a remote and central database as this is typically fairly straightforward when using a data synchronization technologies like MobiLink

Linq - add data to database then email user the details


Hi all

I'm not sure how to structure the code for this but what I want to do is be able to add data to a database, and once this has completed successfully, then email the user to say the details have been added successfully to the database.

I need to:

1. Check that the relevant data has been added into the database then

2. Email the details of the relevant data when completed. 


Thanks in advance for your help,




huge number of data from database ... so how to minimize load time


Hi ...

I have used the above method to configure my crystal report ...

Its working fine , But i have huge number of data in database ....

So , it take long time to load the report ...

So , i have decided to show only the last 50 records inserted into the database , not all the 500 records to be loaded every time.

Plz help me with this issue  ...


Also , Plz explain me abt index legend in crystal report.

Saving textbox data to MySql database in asp.net


Hi dear coders

though a very simple question to ask, but please tell me how to save and retrieve data in/from my textboxes to/from database (MYSQL) on submit button click.

i know its a very simple task but i cant make it done, so i request you to please send me some working example link or post any helpful code here in asp.net

i will be highly thankful for any kind help from you geniuses.



Need Oracle Data Provider .CS File for Oracle 10g Database connection !



I need a 'Wrapper.cs' file which takes care of the Database connection ( Oracle 10g) where

i can just call the method with my SQL Query


Gridview1.DataSource = SampleWrapper.ExecuteDatatable("THE SQL QUERY");


Plz Post the link if there is any open source !    

delete data in excel sheet



           I have write the code for deleting the data in excel sheet. but we have got the error" Deleting data in a linked table is not supported by this ISAM."

     below I paste some code snippest.

string strDelete = "Delete  from [" + strSheetName + "$]";

            cmdExcel.Connection = con;
            cmdExcel.CommandType = CommandType.Text;
            cmdExcel.CommandText = strDelete;


Please can anybody this answer.It is urgent

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend