CODEWITHSUNDEEP
c#sql-serverexcelimport
109221793
109221793

Reputation: 16897

Import Excel Data to Database

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?

Upvotes: 4

Views: 14732

Answers (5)

Vadim P
Vadim P

Reputation: 43

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).

Upvotes: 1

Pleun
Pleun

Reputation: 8920

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

Upvotes: 1

Burcephal
Burcephal

Reputation: 455

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#

Upvotes: 1

Uwe Keim
Uwe Keim

Reputation: 40746

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.

Upvotes: 1

John Koerner
John Koerner

Reputation: 38077

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

Upvotes: 3

Related Questions