Updating SQL Table From Excel Spreadsheet

I have an SQL Table called AllStocks with 5 columns.
The columns are StockAllId, StockName, StockSymbol, StockPrice, StockDate.

When I open my application I want to update two of the columns from an Excel spreadsheet. I want to update the StockPrice and StockDate.

My Excel book is called ExcelAllStocks and the sheet is Sheet1.

I will add this to the page load event, but I am lost as to how exactly I can read the data from Excel and how to write over the two columns with data currently in them.

I've tried creating a connection to Excel but I'm having no luck.

Help would be appreciated.

Thanks
richgnAsked:
Who is Participating?
 
GeoffSuttonCommented:
This is the function I use.  It pulls from Excel sheet and places data into a datatable, where you can manipulate it as you want.  Please forgive the lack of commenting but it's a pretty straightforward section of code.
HTH,
Geoff

public System.Data.DataTable  getExcelSheet(string filePath, int SheetNumber, bool Delete)
        {
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source=" + filePath + ";" +
                            "Extended Properties=Excel 8.0;";

            
                    List<string> tblNames=new List<string>();

                    System.Data.OleDb.OleDbConnection oledbconn = new System.Data.OleDb.OleDbConnection();
                
                System.Data.DataTable ExcelTables;
                try{
                    //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFilePath + ";Extended Properties=""Excel 8.0;IMEX=1;HDR=YES;"""
                    oledbconn.ConnectionString = connectionString;
                    //Server.ScriptTimeout = 60 * 60;
                    oledbconn.Open();
                    ExcelTables = oledbconn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, 
                                  new Object[] {null, null, null, "TABLE"});


                    foreach(System.Data.DataRow dr in ExcelTables.Rows){
                        if (dr[3].ToString() == "TABLE"){
                            if (dr[2].ToString().Contains("$")){ //work book tab
                                tblNames.Add(dr[2].ToString());
                            }
                        }
                    }
                }
                catch (Exception ex){
                    throw ex;
                }
                finally{
                    oledbconn.Close();
                }


            string sqlExcel = "Select * From [" + tblNames[0] + "]";

            System.Data.DataTable DT = new System.Data.DataTable();
            System.Data.OleDb.OleDbConnection oledbConn = new System.Data.OleDb.OleDbConnection(connectionString);

            try
            {
                oledbConn.Open();
                System.Data.OleDb.OleDbCommand oledbCmd = new System.Data.OleDb.OleDbCommand(sqlExcel, oledbConn);

                System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(oledbCmd);
                da.Fill(DT);
            }
            finally
            {
                oledbConn.Close();
            }
            if (Delete)
            {
                System.IO.File.Delete(filePath);
            }
            return DT;
        }

Open in new window

0
 
stevericeCommented:
What platform is (a) the excel spreadsheet and (b) the SQL database? What is the database?

If you have ODBC drivers for both, you should be able to set the excel spreadsheet as a data source.

Sorry, if this is something you've already tried.......

Regards
Steve
0
 
richgnAuthor Commented:
Excel is in Office 2007.
SQL is SQL 2005 Server Express

How would I add the ODBC drivers? The only thing I've done so far is add the using System.Data.OleDb;

I've been thrown in the deep end with a rapidly approaching deadline.

Thanks for the help!
0
 
richgnAuthor Commented:
Thanks Geoff,

Once connected, do you know how I would overwrite the data in 2 of the columns in my SQL table with data from the Excel Sheet?
0
 
GeoffSuttonCommented:
Use a simple SQL query:  
System.Data.SQLClient.SqlConnection con=new System.Data.SQLClient.SqlConnection("connection string for SQL Server DB");
System.Data.SQLClient.SQLCommand cmd=new System.data.SQLClient.SqlCommand("",con);
foreach (datarow dr in dt.rows){
cmd.CommandText=String.format("Update AllStocks set( StockPrice={0}, StockDate='{1}')",dr[1],dr[4]);
con.open();
cmd.executeNonQuery();
con.close();

}
This is just roughed in, of course.  You will have to verify that the columns in the spreadsheet match what I put in (Column A would be dr[0] etc) and also that the date format matches what SQL server can understand.  But that is basically all you need to do.
HTH,
Geoff
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.