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?

Does it need to be done through ASP.net? If it's a once-off import, you could use the DTS wizard. — mopoke
– mopoke, Commented Dec 28, 2009 at 8:42

Glorfindel · Accepted Answer · 2023-02-21 15:02:47Z

4

Have a look at

edited Feb 21, 2023 at 15:02

Glorfindel

22.8k13 gold badges97 silver badges124 bronze badges

answered Dec 28, 2009 at 8:44

Adriaan Stander

167k32 gold badges294 silver badges288 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jehanzeb afridi · Accepted Answer · 2011-12-26 19:45:41Z

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

FYI, last I heard, the Jet drivers were still only supported on x86, with no plans for x64.

Anton Gogolev · Accepted Answer · 2009-12-28 08:42:52Z

0

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.

answered Dec 28, 2009 at 8:42

Anton Gogolev

116k39 gold badges204 silver badges293 bronze badges

1 Comment

Nandini Over a year ago

i want this to be happen through asp.net code.I need the proper code for this

Rubens Farias · Accepted Answer · 2009-12-28 08:45:01Z

0

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.

answered Dec 28, 2009 at 8:45

Rubens Farias

58k8 gold badges136 silver badges165 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Related