Import Excel data into a database

Question

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

The NPOI library is already deployed on a system that works very well, but it was pretty hard for me to find sample code on the Internet for data collection and import.

I was wondering if anyone who has experience with similar work can tell me the right direction regarding sample code, tutorials, etc.?

Or if someone can recommend a better alternative to NPOI?

Answer 1

Depending on the location of your excel file (i.e. if it is a table file), you can use ADO.Net to do this. You can query data from sheets and retrieve data as if you were querying a true database.

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

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

Answer 2

Do you think the launch of the DTS package? Depending on the amount of data and if there are any transformations, it may be interesting to consider the SSIS package to run from your code. Run SSIS FROM VB.NET or C # package

Answer 3

The (commercial) Aspose.Cells tool is commonly used to read Microsoft Office Excel files.

Another alternative, as pointed out by @ Negative0, is to use ADO.NET.

Answer 4

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) { } 

Answer 5

Either the database server or your application can handle data import.

To create the database, create the SSIS package (the answer is above the recommended DTS, but the DTS is deprecated). The easiest way to do this is to use SQL Server Management Studio to "Import" data into the database (right-click on the database and select "Tasks"). The last import step allows you to save the import settings as an SSIS package. Then you run the SSIS package from your application.

Your application can also handle it in several ways. Windows comes with an ODBC driver for Excel files, or you can use the Excel API to open a workbook in your application, transfer data to your application, and then write to the database. The ODBC route is probably implemented faster if all the data you need is just text from a sheet (if you need to evaluate formatting or go beyond just getting text data, you should use the API).