Read and Update in database through excel

Question

// Error when upploading with provider Microsoft.ACE.OLEDB.12.0

System.InvalidOperationException: 'The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

// Error when uploading with provider Microsoft.JET.OLEDB.4.0

System.Data.OleDb.OleDbException: 'Could not find installable ISAM

HELP PLEASE

// for browsing file 
  private void btnBrowse_Click(object sender, EventArgs e)
        {
OpenFileDialog fdlg = new OpenFileDialog();
fdlg.Title = "Select File";
fdlg.FileName = txtFileName.Text;
fdlg.Filter = "Excel Sheet (*.xls)|*.xls|All Files(*.*)|*.*";
fdlg.FilterIndex = 1;
fdlg.RestoreDirectory = true;
if (fdlg.ShowDialog() == DialogResult.OK)
            {
                txtFileName.Text = fdlg.FileName;
            }
        }


// for importing excel file into datagridview
   private void btnImport_Click(object sender, EventArgs e)
        {
OleDbConnection theConnection = new OleDbConnection(@"provider=Microsoft.ACE.OLEDB.12.0;data source='" + txtFileName.Text+"';Extended Properties=\"Excel 15.0;HDR=YES;\"");

theConnection.Open();
OleDbDataAdapter theDataADapter = new OleDbDataAdapter("SELECT * FROM[Sheet1$]",theConnection);
DataSet theSD = new DataSet();
DataTable dt = new DataTable();
theDataADapter.Fill(dt);
this.dataGridView1.DataSource = dt.DefaultView;## Heading ##
        }

Answer 1

Excel supports a number of standards of file formats that are useful in this scenario, CSV and XML are often the simplest to consume as inputs from an application point of view. By choosing one of these non-excel formats you now offer support to a wider audience.

It also means this is no longer an Excel based solution, its now only a data problem

A basic data import pattern requires the following components:

1. The specific data structure must be documented
2. On import, the data structure should be validated
3. If the structure is trusted either use serialization or a reader process to interpret the data
4. Execute your comparison business logic to detect inserts and updated and execute those operations against your application database.

There are plenty of tools you can use to read from CSV files, so search for assistance on that format to see if it is a fit for your code base.

• Processing CSV File
• C#: Processing CSV files (Part 1)

If you use XML serialization, then this can take care of steps 1,2,3 in one hit, you only need to create your DTO class (The class that respresents the records from excel rows) then serializing that class provides the basic documentation.

• Examples of XML Serialization
• Read From XML File into C# Class Using Serialization

The standard XML Deserialization code will manage the validation and convert the input into objects that you can then use like normal data objects in your code.

Once the data is in memory, whether you do that one row at a time or in bulk, then you can use your normal C# techniques to compare the imported record, find a match in your DB and update it, or is there is no match insert a new row.

The Catch:

Try not to allow the user to create there own file, or rather, try not to create a solution where you allow the user to map different columns to fields in your database or where you expect the user to create the file with the correct structure themselves.

Keep it simple, make your app prepare and provide the "blank" file or template that the user then manipulates and later re-submits back to your app.

---

This is a very broad topic and there are many different ways to achieve this, however if you are asking, its probably better to start with basics and evolve the process later if it becomes neccessary to do so.

For instance it is possible for Excel to connect directly to your API or even database, meaning you could even write a rich interface into Excel itself, but that is very involved and generally not necessary to achieve small tasks. Relaxing security to a point where Excel can talk directly to your backend or database opens a whole other cans of worms, but its a possibility to be aware of that can be very useful for trusted power users in read-only scenarios.

Answer 2

If you're talking about a temporary solution, for less than 1000 products, and its going to be once off, even if it's a once off per year, then it might be easier to tell your user to submit the excel file to you with their products, and then you can use direct DBA techniques to import this data into the database manually.

I feel that this post sums up your scenario: Time is running out...

The idea is to identify how long it takes you to complete the task (Import data from an Excel spreadsheet) and then to identify how many times you are likely to perform this task..

If user presents to you an Excel file of a flat data structure, with a bit of SQL Wizardry and Ninja skills, 10 minutes later you can have that data inserted into the database using SQL alone.

• This would be a good topic for a question to ask on this forum, try it out, then post a question when/if you get stuck.
• So if you are only going to do this task once or twice, ever... then don't take more than 10-20 minutes to develop a coded solution that the user can use.
  • Even if it only took you 10 minutes, you could reasonably charge the client a couple of hours for the privilege, but the reverse is less reasonable, you could probably only charge for a few hours to code, deploy and teach the user to use this new functionality, even though that full process may take a few days of back and forth effort to get it right, I would consider it highway robbery to charge much for this sort of feature if they aroun only going to do it once.

Instead of SQL skills, you could still write your own C# script specifically to import the user's file, it doesn't have to be client facing, a simple c# script either as a standalone console app, unit test or a hidden method in your application runtime could be written in less than an hour, then it might take you 10 minutes to take the excel data and massage it so that your script can use it.

But the time it takes you, vs what you can reasonably charge the user for this functionlity must still be taken into account.

So once you've identified how long the task will take you, and how many times you will need to perform this task in the future, then you can follow tables like this to identify if it is worth the effort to automate the task at all:

Originally from XKCD: Is It Worth the Time?

If you can write good solid client facing solution into your app where the client can select the file to upload and your code automates the rest, then you still only want this to take few hours at most, certainly possible, but unless you do it well, you will probably get higher customer satisfaction by saving them some $$$ and performing this task for them on demand, then offering them a buggy interface that they have to pay $1000s of dollars for.

Tasks like this are less about how to write the code to complete the task, and more about what is the right business decision for you and your client, which brings me to me second favorite sketch:

Originally from XKCD: Automation

So while you think you want to write code to allow your user to import data from excel for one time only... there might be a different way to solve your problem.