How might I import data from MS-Excel into Sql Server using ASP.NET?

Question

I need to import data from Excel to Sql Server using ASP.NET. How can I do this?

Answer 1

Have a look at

• Importing an Excel Spreadsheet Using Typed DataSets and TableAdapters: Building the Database
• Import Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy
• Asp.Net Excel File Import and Transfer to Sql Server

Answer 2

You can use ADO.net OLEDB data source. You can fetch records as you normally do for MS Access. Have a look at the example..

public static DataTable SelectAll()
{
    string conString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + @"\YourExcellfile.xls;Extended Properties=""Excel 8.0;HDR=Yes"";";
    OleDbConnection oleConnection = new OleDbConnection(conString);

    OleDbCommand oleCommand = new OleDbCommand("select * from [YourSheet1$]", oleConnection);
    OleDbDataAdapter adapter = new OleDbDataAdapter(oleCommand);

    oleConnection.Open();

    DataTable dt = new DataTable();
    adapter.Fill(dt);

    oleConnection.Close();

    return dt;
}

After the import you can pick the data from the data table and perform the insert operation using ADO.net Sql operation

Answer 3

I assume you want your users to upload an Excel document, which then has to be imported into SQL server. If so, you can either try some third-party library to open xls file and read data on a row-by-row basis, inserting it into an appropriate table or install Excel itself on a web server (not a good idea, though) and use it as an ODBC data source.

Answer 4

Besides using an ODBC data source, you can also to ask your user to export that Excel file to CSV and to import it manually.