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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.