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

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 2

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 3

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.

Answer 4

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