CODEWITHSUNDEEP
c#exceldatatableformatting
Evan L
Evan L

Reputation: 3855

Copy Row from DataTable to another with different column schemas

I am working on optimizing some code I have been assigned from a previous employee's code base. Beyond the fact that the code is pretty well "spaghettified" I did run into an issue where I'm not sure how to optimize properly.

The below snippet is not an exact replication, but should detail the question fairly well.

He is taking one DataTable from an Excel spreasheet and placing rows into a consistantly formatted DataTable which later updates the database. This seems logical to me, however, the way he is copying data seems convoluted, and is a royal pain to modify, maintain or add new formats.

Here is what I'm seeing:

private void VendorFormatOne() 
{
    //dtSumbit is declared with it's column schema elsewhere
    for (int i = 0; i < dtFromExcelFile.Rows.Count; i++)
    {
        dtSubmit.Rows.Add(i);
        dtSubmit.Rows[i]["reference_no"] = dtFromExcelFile.Rows[i]["VENDOR REF"];
        dtSubmit.Rows[i]["customer_name"] = dtFromExcelFile.Rows[i]["END USER ID"];
        //etc etc etc
    }
}

To me this is completely overkill for mapping columns to a different schema, but I can't think of a way to do this more gracefully. In the actual solution, there are about 20 of these methods, all using different formats from dtFromExcelFile and the column list is much longer. The column schema of dtSubmit remains the same across the board.

I am looking for a way to avoid having to manually map these columns every time the company needs to load a new file from a vendor. Is there a way to do this more efficiently? I'm sure I'm overlooking something here, but did not find any relevant answers on SO or elsewhere.

Upvotes: 1

Views: 1663

Answers (3)

Stephen Byrne
Stephen Byrne

Reputation: 7505

I had originally intended this just as a comment but ran out of space. It's in reply to Micah's answer and your first comment therein.

The biggest problem here is the amount of XML mapping would equal that of the manual mapping in code

  • Consider building a small tool that, given an Excel file with two columns, produces the XML mapping file. Now you can offload the mapping work to the vendor, or an intern, or indeed anyone who has a copy of the requirement doc for a particular vendor project.

  • Since the file is then loaded at runtime in your import app or whatever, you can change the mappings without having to redeploy the app.

  • Having used exactly this kind of system many, many times in the past, I can tell you this: you will be very glad you took the time to do it - especially the first time you get a call right after deployment along the lines of "oops, we need to add a new column to the data we've given you, and we realised that we've misspelled the 19th column by the way."

  • About the only thing that can perhaps go wrong is data type conversions, but you can build that into the mapping file (type from/to) and generalise your import routine to perform the conversions for you.

Just my 2c.

Upvotes: 1

skolte
skolte

Reputation: 367

A while ago I ran into similar problem where I had over 400 columns from 30 odd tables to be mapped to about 60 in the actual table in the database. I had the same dilemma whether to go with a schema or write something custom.

There was so much duplication that I ended up writing a simple helper class with a couple of overridden methods that basically took in a column name from import table and spit out the database column name. Also, for column names, I built a separate class of the format

public static class ColumnName
{
  public const string FirstName = "FirstName";
  public const string LastName = "LastName";
  ...
}

Same thing goes for TableNames as well.

This made it much simpler to maintain table names and column names. Also, this handled duplicate columns across different tables really well avoiding duplicate code.

Upvotes: 0

Micah
Micah

Reputation: 471

This might be overkill, but you could define an XML file that describes which Excel column maps to which database field, then input that along with each new Excel file. You'd want to whip up a class or two for parsing and consuming that file, and perhaps another class for validating the Excel file against the XML file.

Depending on the size of your organization, this may give you the added bonus of being able to offload that tedious mapping to someone less skilled. However, it is quite a bit of setup work and if this happens only sparingly, you might not get a significant return on investment for creating so much infrastructure.

Alternatively, if you're using MS SQL Server, this is basically what SSIS is built for, though in my experience, most programmers find SSIS quite tedious.

Upvotes: 2

Related Questions