How can I read an uploaded Excel file and save it direct to DB?

Question

Hi!

Is there a way to upload an Excel file and save its content to Sql Server directly?

thanks!!

EDITED:

I don't want to save it as binary. I want to read its contend and save them to DB, each Excel column content into the DB table column and so on...

Answer 1

I know or a product called QueryCell that lets you use SQL to access an excell file. Not exactly what you were looking for but i thought it was woth mentioning.

Answer 2

You can use something like this.. you need have oledb drive on your server.

    String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + uploadFilenm + "; Extended Properties=" + (char)34 + "Excel 8.0;IMEX=1;" + (char)34;

   // Create connection object by using the preceding connection string.
    OleDbConnection objConn = new OleDbConnection(sConnectionString);

    // Open connection with the database.
    objConn.Open();

    // The code to follow uses a SQL SELECT command to display the data from the worksheet.

    // Create new OleDbCommand to return data from worksheet.
    OleDbCommand objCmdSelect = new OleDbCommand("SELECT * FROM [sheet1$]", objConn);

    // Create new OleDbDataAdapter that is used to build a DataSet
    // based on the preceding SQL SELECT statement.
    OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();

    // Pass the Select command to the adapter.
    objAdapter1.SelectCommand = objCmdSelect;

    // Create new DataSet to hold information from the worksheet.
    DataSet objDataset1 = new DataSet();

    // Fill the DataSet with the information from the worksheet.
    objAdapter1.Fill(objDataset1, "XLData");

Answer 3

you can make a dtsx package in SSIS that will treat the excel file as a data source - specify your SQL database as the data destination and you're away!

here: http://msdn.microsoft.com/en-us/library/ms139836.aspx