Write data from excel file to a table in SQL Database

Posted on 2006-04-13
Last Modified: 2010-05-18

The next step is to take the info from the Excel file and write it to a table in SQL DB.
Question by:GoldenJag

    Author Comment

    This is officially what my code looks like:

    private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)
          int fileid = Convert.ToInt32(DataGrid1.SelectedItem.Cells[1].Text);
          com.solu.webservices.Crypt ws = new com.solu.webservices.Crypt();
          byte[] test = ws.DecryptStoredFile(pm.sessionKey,fileid);
          //WRITE TO TEMP FILE
          //string FileName = Path.GetTempFileName();
          string FileName = @"C:\temp.xls";
          FileStream File_Stream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
          catch(Exception ex)

                                                      //NEXT STEP:
                      //WRITE to info from Excel file to SQL  Table via ODBC

    LVL 12

    Expert Comment

    OK, cool.

    Is this the new code? You should be careful with the FileStream object ... the using {} block we had is a better way of doing it as it ensures that the stream is both flushed and disposed once you've finished.

    Other than that, you now want to write from Excel to SQL, unfortunately I haven't had much success with this in the past. ODBC and Excel is a bit of a nightmare - the data-type detection is very dodgy.

    However, I can point you to this place to get you started:

    Hopefully that will help, or someone else will join in this question to add some more advice. Feel free to ask for more details as you go.


    Author Comment

    I found the solution: it is

    string strConn;

                      strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                            "Data Source= C:\\temp.xls;" +
                            "Extended Properties=Excel 8.0;";
                      //You must use the $ after the object you reference in the spreadsheet
                      OleDbConnection objConn = new OleDbConnection(strConn);
                      OleDbCommand myCommand = new OleDbCommand("SELECT * FROM [Sheet1$]",objConn);
                      OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

                      objAdapter1.SelectCommand = myCommand;

                      DataSet myDataSet = new DataSet();

                      objAdapter1.Fill(myDataSet, "XLData");
                      DataGrid1.DataSource = myDataSet.Tables[0].DefaultView;
    LVL 12

    Expert Comment

    Fine by me

    Accepted Solution

    Closed, 500 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    Article by: Ivo
    C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
    Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now