Import Excel Data to Database

Question

I'm just beginning a project where my system (written in .NET MVC) will have to read Excel data from a specified file, and import into the database (SQL Server 2008).

The NPOI library is already deployed in the system which works pretty well, however I have found it quite difficult to find sample code on the web with regards to collecting data, and importing it.

I was wondering if anyone who has experience with this sort of work could point me in the right direction with regards sample code, tutorials, etc?

Or if anyone can recommend a better alternative to NPOI?

Do you have Microsoft Office Excel installed on your server (i.e. on the machine that will recieve the .xls file and extract the data)? — Tomas Aschan, Feb 10 '11 at 14:45

score 3 · Accepted Answer · answered Feb 10 '11 at 14:48

Depending on the layout of your excel file (i.e. if it is tabular) you could use ADO.Net to do it. You can query data from sheets and pull data as if you were querying a true database.

You can start with the connection string: http://www.connectionstrings.com/excel

Then there is a vb.net example over on MSDN, that you could easily convert to C#: http://support.microsoft.com/kb/311731

score 1 · Answer 2 · edited May 23 '17 at 12:04

1

Would you consider running a DTS package? Depending on the amount of data and if there are any transformation to do it could be interesting to consider a SSIS package to run from your code. Execute a SSIS package FROM VB.NET or C#

edited May 23 '17 at 12:04

Community

1
1

answered Feb 10 '11 at 14:55

Burcephal

445
4
9

score 1 · Answer 3 · answered Feb 10 '11 at 15:03

1

The (commercial) tool Aspose.Cells is usually what I use for reading Microsoft Office Excel files.

Another alternative, as @Negative0 stated, would be to use ADO.NET.

answered Feb 10 '11 at 15:03

Uwe Keim

39,551
56
175
291

score 1 · Answer 4 · answered Feb 10 '11 at 16:18

Using Microsoft Jet: First create a connection

string Con2 = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
filename + @";Extended Properties='Excel 12.0;IMEX=1'";
System.Data.OleDb.OleDbConnection ExcelConnection =
 new System.Data.OleDb.OleDbConnection(Con2);

Then do something like...

DataSet ds = new DataSet();

 // Create OleDbCommand object and select data from worksheet TABNAME
 OleDbCommand cmd_hulpkostenplaatsen = new OleDbCommand("SELECT * FROM [TABNAME$]", ExcelConnection);
        OleDbDataAdapter oleda_hulpkostenplaatsen = new OleDbDataAdapter();
        oleda_hulpkostenplaatsen.SelectCommand = cmd_hulpkostenplaatsen;
        oleda_hulpkostenplaatsen.Fill(ds, "HULPKOSTENPLAATSEN");

        foreach (DataRow row in ds.Tables["HULPKOSTENPLAATSEN"].Rows)
        {
        }

score 1 · Answer 5 · answered Feb 10 '11 at 17:38

Either your database server or your application can handle importing data.

To have the database do it, build an SSIS package (an answer above recommended DTS, but DTS has been deprecated). The easiest way to do this is to use SQL Server Management Studio to "Import" data into a database (right click on a DB and select Tasks). The last step of the import allows you to save your import settings as an SSIS package. You would then execute the SSIS package from your application.

Your application can also handle it directly a few different ways. Windows ships with an ODBC driver for Excel files, or you can use the Excel API to open the workbook within your application, bring the data into your app, and then write it to the DB. The ODBC route is probably quicker to implement, as long as all of the data that you need is simply text from within a sheet (if you need to evaluate formatting or go beyond just getting textual data, you have to use the API).

Import Excel Data to Database

5 Answers5