Extract Data from Excel File and Store in SQL Server database

Question

I am looking for advice on the best way to parse a Microsoft Excel file and update/store the data into a given SQL Server database. I using ASP.NET MVC so I plan on having a page/view take in an Excel spreadsheet and using that user given file I will need to use C# to parse the data from the columns and update the database based on matches with the spreadsheet column that contains the key column of the database table. The spreadsheet will always be in the same format so I will only need to handle on format. It seems like this could be a pretty common thing I am just looking for the best way to approach this before getting started. I am using Entity Framework in my current application but I don't have to use it.

I found this solution which seems like it could be a good option:

public IEnumerable<MyEntity> ReadEntitiesFromFile( IExcelDataReader reader, string filePath )
{
   var myEntities = new List<MyEntity>();
   var stream = File.Open( filePath, FileMode.Open, FileAccess.Read );

   using ( var reader = ExcelReaderFactory.CreateOpenXmlReader( stream ) )
   {
     while ( reader.Read() )
     {
        var myEntity = new MyEntity():
        myEntity.MyProperty1 = reader.GetString(1);
        myEntity.MyProperty2 = reader.GetInt32(2);

        myEntites.Add(myEntity);
      }
   }

   return myEntities;
}

Here is an example of a what a file might look like (Clock# is the key)

enter image description here

So given a file in this format I want to match the user to the data table record using the clock # and update the record with each of the cells information. Each of the columns in the spreadsheet have a relatable column in the data table. All help is much appreciated.

Is the issue importing that data or processing the data after you import it? Can you explain your actual problem? — Nick.Mc
– Nick.Mc, Commented Oct 28, 2014 at 23:41
Sorry, the issue is actually importing the data. I can get the excel file and loaded fine. I just need to know how to run through each row in the spreadsheet and update the data using c# — user3788671
– user3788671, Commented Oct 28, 2014 at 23:42
Are you saying you want to write back to the Excel file you just imported, or update data in the table you just imported into? — Nick.Mc
– Nick.Mc, Commented Oct 29, 2014 at 1:02

hbdavehb · Accepted Answer · 2014-10-29 00:53:42Z

3

You can use the classes in the namespace Microsoft.Office.Interop.Excel, which abstracts all the solution you found. Instead of me rewriting it, you can check out this article: http://www.codeproject.com/Tips/696864/Working-with-Excel-Using-Csharp.

Better yet, why not bypass the middle man? You can use an existing ETL tool, such as Pentaho, or Talend, or something to go straight from Excel to your database. These types of tools often offer a lot of customization, and are fairly straightforward to use. I've used Pentaho quite a lot for literally what you're describing, and it saved me the head ache of writing the code myself. Unless you want to/need to write it yourself, I think the latter is the best approach.

answered Oct 29, 2014 at 0:53

hbdavehb

2201 silver badge6 bronze badges

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

1 Comment

Panagiotis Kanavos Over a year ago

Installing Excel on a server just to use interop is a very bad idea and guaranteed to cause problems. As for ETL, SQL Server itself includes a full featured ETL system since the 1990s. It's what Pentaho et al are based on

Manoj · Accepted Answer · 2014-10-31 13:02:38Z

Try This public string GetDataTableOfExcel(string file_path) {

            using (OleDbConnection conn = new OleDbConnection())
            {
                DataTable dt = new DataTable();
                string Import_FileName = Server.MapPath(file_path);
                //Import_FileName = System.IO.Path.GetDirectoryName(file_path);
                string fileExtension = Path.GetExtension(Import_FileName);
                if (fileExtension == ".xlsx")
                    conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Import_FileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;'";
                using (OleDbCommand comm = new OleDbCommand())
                {
                    comm.CommandText = "Select * from [Sheet1$]";
                    comm.Connection = conn;
                    using (OleDbDataAdapter da = new OleDbDataAdapter())
                    {
                        da.SelectCommand = comm;
                        da.Fill(dt);
                                                    }
                }
            }
        }

Now Your Data in DataTable. You can create insert query from datatable's data.

file_path is excel file's full path with directory name.

Collectives™ on Stack Overflow

Extract Data from Excel File and Store in SQL Server database

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related