C# Discussion Boards - CodeProject

Hi Brenton,

None of the options that you have tried will work.

I'll try to explain.

As we left it, MappingForm has a DataViewRow containing a series of cells, each cell holding something like "thisType" or "thatType". To populate each of your DataGridViews with relevant information you need to get it from a file, in the same way that you did for the dgv on Form1. Only you know which file contains data for "thisType" and which file holds data for "thatType".

The if (dataKey.cells[c].Value.ToString == "thisType") lines (or the switch block, if that is what you used), simply enables you differentiate between the various 'Types' so that you know which to use the in the SELECT statement when retrieving the data into a DataTable/DataSet or whatever you decide to use as the datasource for your dgvs.

Something like

SELECT * FROM TheThisTypeFile.csv
  WHERE DataType == "thisType"

Obviously the WHERE clause is only required if that file holds data for various 'Types'.

Hope that helps.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

I am still confused because I would still have to know what the field names are called in the file. For instance, I have 2 files. One file may have 32 fields and the other 10 fields which come in different days (say one today and one tomorrow). In these columns we are looking for Loan Purpose Type and Loan Documentation type. In file one these fields are named Doc Type and Purp Type and in the second file they are named Documentation Type and Purpose Type. Also, in the where clause you would need to specify all the doc types. However, each client has there own doc types. One client may have say Full Doc, Limited Doc, and No Doc. Another client may have 1, 2, and 3. so it seems the query would have to change every time. Is this correct?

Ah-ha!

I am beginning to understand a little more about how this is supposed to work.

If I am right, probably not, but here goes anyway, the purpose of the dgv in Form1 is to allow for translation/modification of what is in the files from client, to a 'standard' wording.

If I have got that right, you have been doing things backwards! Laugh | :laugh:

Sorry, I shouldn't laugh, but it is just the sort of thing I do all the time.

I will wait for your response before going further.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

That is correct, with one exception. The only thing I want to accomplish in Form 1 is changing the field headers to a standardized naming convention. In Form 2 I want to standardize the data (but this only needs to be done to certain fields like Doc types). I will explain more to you so we are both on the same page. So we receive files on a daily basis from many different clients. These files contain different information in different columns with different field headers. We use SSIS to get them into our systems however, SSIS breaks every time we run the files through because of the differentiation between field headers and data. So, to get around this I am creating a prototype to standardize all the files which mainly include field headers and data related to some of the fields (i.e. Doc Type, Purpose Type, and so on). So For example, if the field header Doc Type comes in then in form 1 row 1 of the dgv there is a combo dropdown where the user can select the "New" header name. Then the second form needs to include the distinct data for the specific field headers so I can standardize them as well. This second form will activate upon clicking a button. Example:

File 1:

Loan Documentation Type(field name from file) = DocType(new header that will be selected in row 1 of the dgv on Form1)

Low Doc, Full, and No Doc(Doc Types data from file) = L, F, ND(translate doc types to standardized naming convention in dgv on Form 2)

File 2:

Docs(field name from file) = DocType(new header that will be selected in row 1 of the dgv on Form1)

1, 2, and 3 (data from file) = L, F, ND(translate doc types to standardized naming convention in dgv on Form 2)

So the files we get have 100's of loans. For this example, the field Doc Types can have many types (i.e. 50 Full docs, 25 Low Docs, and 25 No docs) which is why I would like to select Distinct values only having to map, in this case, 3 doc types rather then 100.

I am sure I have not explained myself very well in previous posts so please let me know if you need additional information or if you are confused on anything I have said to this point.

Going back to your previous post, you have said that for documentation type a client might have Full Doc, Limited Doc and No Doc; whilst another might have 1, 2 and 3.

Are the options finite? i.e. are there always 3 possibilities for documentation type, and is that the same for all fields? (not necessarily 3, it could be 10, 7 or whatever) The point is are all options (or at least the number of possible options) known to yourselves prior to recieving the file(s). Also will the same client always use the same headings.

On to this post.
I am not 100% on the reason for the multiple dgvs on the mapping form.
In Mapping form, is the purpose of the different dgvs to collate, based on documentation type (dgv1 = full doc, dgv2 = limited doc etc), or to summarise (dgv1 = doctype, with a row for count of full doc, another for count of limited doc etc) with the other dgvs for the other field types.

Is the final objective to just display the data, to stream it to another application or to write it back out to a file and if so what type of file (.csv, mdf, txt)

The point I made previously (about doing it backwards) is that to do anything with the clients data, whether selecting on a field type, collating on a field type, or whatever, you have at that stage to use the clients wording/coding scheme.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

No, there could be up to 30+ different doc types. I just used three for this example. Same goes for the other fields we need to map data for. Not all possible data is known until we receive the data and the same client doesn't always give us the same headings or file formats. nothing is considered finite between the data or the headers.

The reason for the multiple dgv's is to have a dgv per field where the data needs to be mapped. So, dgv1 is for Doc Types, dgv2 is for Purpose Types, dgv3 is for Occupancy Types and so on. So the second part of the third paragraph you wrote is correct "or to summarize (dgv1 = doctype, with a row for count of full doc, another for count of limited doc etc) with the other dgvs for the other field types". So the all the dgv's have three columns. The first will be the column that gets populated with the data from the file or the dgv on Form 1(example Full Documentation). The second column is a combo dropdown that contains the new code (F for Full Documentation). The third is a count of how many loans have Full Documentation. After all Doc Types have been recoded, then dgv on Form 1 needs to be updated. For example, Full Documentation is will be updated to F.

Once the dgv on Form 1 has been updated with the new codes from form 2, I will output it to excel for our SSIS (this is done and has been tested).

Hi there.

I have been thinking about this and have made a simple demo of something that I think might work. If you have the time would you give it a quick whirl to see if it might suit.

The demo consists of two forms each having a datagridview and a button, you will be able to work out the names of these controls from the code I will post. I will post the code for the two forms in separate posts, as it would otherwise be a very long post.

This is the test data I have used for the demo and if you could save it as TestData.csv in the directory with the executable for the demo, normally 'application-files-directory\bin\debug'

TestData.csv

Prop, Offer, Docs, Gtee, Figs
house, no, full, none, true
flat, true, part, complete, no
appt, no, no, yes, yes
bungalow, yes, full, no, no
mais, false, yes, no, comp

The code for the first form follows shortly.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Code for the first form.

public partial class ListEditorForm : Form
{
    List<string> standardList = new List<string>();
    Dictionary<string, string> headers = new Dictionary<string, string>();
    DataSet headerDataSet = null;
    DataTable headerTable = null;
    MappingForm mappingForm = null;
    readonly string dataFile = "TestData.csv";

    FieldMappingDictionary docsDictionary = null;

    public ListEditorForm()
    {
        InitializeComponent();
        InitStandardList();
    }

    private void InitStandardList()
    {
        standardList.AddRange(new string[] { "Property", "Offer", "Documentation", "Figures", "Guarantee" });
    }

    private void ProcessMods()
    {
        // Create a copy of original table structure with
        // standardized column names**
        // load original file data into it
        // then do a series of updates using the docsDictionary, and the
        // other Dictionaries
        // STEP THROUGH THIS TO WATCH UPDATE STATEMENT BUILDING
        StringBuilder selectBuilder = null;
        foreach (string key in this.docsDictionary.Keys)
        {
            foreach (string val in this.docsDictionary[key])
            {
                selectBuilder = new StringBuilder();
                selectBuilder.Append("UPDATE <yourtablenamehere**> SET Documentation = ");
                selectBuilder.Append(key);
                selectBuilder.Append(" WHERE Documentation = ");
                selectBuilder.Append(val);

                string updateQuery = selectBuilder.ToString();

                // execute the query here
                // you know OleDbConnection, OleDbcommand etc.
                // then go round for next mod
            }
            // go round for next key
        }

        // then do same for next dictionary
    }

    private Dictionary<string, string> MakeHeaderDictionary()
    {
        Dictionary<string, string> result = new Dictionary<string, string>();
        foreach (DataRow row in this.headerTable.Rows)
        {
            result.Add(row[1].ToString(), row[0].ToString());
        }

        return result;
    }

    #region ListEditorForm PROPERTIES ...............................
    public Dictionary<string, string> Headers
    {
        get
        {
            return this.headers;
        }

        private set
        {
            this.headers = value;
        }
    }

    private MappingForm MappingForm
    {
        get
        {
            if (this.mappingForm == null)
            {
                this.mappingForm = new MappingForm();
            }

            return this.mappingForm;
        }
    }
    #endregion ListEditorForm PROPERTIES

    private void ListEditorForm_Load(object sender, EventArgs e)
    {
        string headerLine;
        using (StreamReader sr = new StreamReader(dataFile))
        {
            headerLine = sr.ReadLine();
        }

        headerDataSet = new DataSet("HeaderDataSet");
        headerTable = new DataTable("ClientHeaders");
        DataColumn newColumn = new DataColumn("ClientHeader", typeof(string));
        headerTable.Columns.Add(newColumn);
        newColumn = new DataColumn("StandardHeader", typeof(string));
        headerTable.Columns.Add(newColumn);

        DataRow newRow = null;
        foreach (string s in headerLine.Split(','))
        {
            newRow = headerTable.NewRow();
            newRow[0] = s;
            newRow[1] = "Property";
            headerTable.Rows.Add(newRow);
        }

        headerDataSet.Tables.Add(headerTable);

        DataGridViewComboBoxColumn cbCol =
            this.dataGridView1.Columns["standardColumn"] as DataGridViewComboBoxColumn;
        cbCol.DataSource = this.standardList;

        this.dataGridView1.AutoGenerateColumns = false;
        this.dataGridView1.Columns[0].DataPropertyName = "ClientHeader";
        this.dataGridView1.Columns[1].DataPropertyName = "StandardHeader";
        this.dataGridView1.DataSource = headerDataSet;
        this.dataGridView1.DataMember = "ClientHeaders";
    }

    private void btnGetValue_Click(object sender, EventArgs e)
    {
        this.headers = this.MakeHeaderDictionary();
        this.MappingForm.HeaderDictionary = this.headers;
        this.MappingForm.DataFile = this.dataFile;
        this.MappingForm.FormClosing += new FormClosingEventHandler(MappingForm_Closed);
        this.MappingForm.Show();
    }

    void MappingForm_Closed(object sender, FormClosingEventArgs e)
    {
        this.docsDictionary = this.MappingForm.DocumentsDictionary;
        // get rest of dictionaries here

        this.ProcessMods();
    }
}

Code for second form follows

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Code for second form.

public partial class MappingForm : Form
{
    List<string> standardDocumentList = new List<string>();
    FieldMappingDictionary documentsDictionary = new FieldMappingDictionary();

    private Dictionary<string, string> headerDictionary = null;
    private string dataFile;

    public MappingForm()
    {
        InitializeComponent();

        InitLookupLists();
    }

    private void InitLookupLists()
    {
        this.standardDocumentList.AddRange(new string[] { "Full", "Partial", "None" });
    }

    private void MappingForm_Load(object sender, EventArgs e)
    {
        DataGridView dgv = null;
        string headerString = null;

        foreach (string key in this.headerDictionary.Keys)
        {
            headerString = this.headerDictionary[key];
            switch (key)
            {
                case "Documentation":
                    dgv = this.dgvDocumentation;
                    break;
                case "Property":
                    dgv = null;
                    break;
                case "Offer":
                    dgv = null;
                    break;
                case "Figures":
                    dgv = null;
                    break;
                case "Guarantee":
                    dgv = null;
                    break;
            }

            if (dgv != null)
            {
                this.FillDatagrid(headerString, dgv);
            }
        }
    }

    DataTable dFill = null;
    private void FillDatagrid(string headerString, DataGridView dgv)
    {
        //Connect to csv file
        String comString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            Path.GetDirectoryName(Path.GetFullPath(this.dataFile)) + @";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1\""";

        String strSql = "SELECT DISTINCT " + headerString + " FROM [" + Path.GetFileName(this.dataFile) + "]";
        using (OleDbConnection conCSV = new OleDbConnection(comString))
        {
            conCSV.Open();
            OleDbCommand dbCommand = new OleDbCommand(strSql, conCSV);
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(dbCommand);
            dFill = new DataTable();
            dAdapter.Fill(dFill);
        }

        bindingSource1.DataSource = dFill;
        dgv.DataSource = bindingSource1;

        // Set up lookup for dgvDocumentation
        // only temporarilly here for demo
        DataGridViewComboBoxColumn lookupColumn = new DataGridViewComboBoxColumn();
        this.dgvDocumentation.Columns.Add(lookupColumn);
        lookupColumn.ValueType = typeof(string);
        lookupColumn.HeaderText = "Standard";
        lookupColumn.DataSource = this.standardDocumentList;

        for (int i = 0; i < dgv.Columns.Count; i++)
        {
            dgv.Columns[i].SortMode = DataGridViewColumnSortMode.NotSortable;
            dgv.Columns[i].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
        }
    }

    private void CreateDocsDictionary()
    {
        foreach (DataGridViewRow row in this.dgvDocumentation.Rows)
        {
            //DataGridViewComboBoxCell lookupCell = row.Cells[1] as DataGridViewComboBoxCell;
            if (row.Cells[0].Value != null)
            {
                this.documentsDictionary.Add(row.Cells[1].Value.ToString(), row.Cells[0].Value.ToString());
            }
        }
    }

    #region MappingForm PROPERTIES ...............................
    public Dictionary<string, string> HeaderDictionary
    {
        get
        {
            return this.headerDictionary;
        }

        set
        {
            this.headerDictionary = value;
        }
    }

    public string DataFile
    {
        get
        {
            return this.dataFile;
        }

        set
        {
            this.dataFile = value;
        }
    }

    public FieldMappingDictionary DocumentsDictionary
    {
        get
        {
            return this.documentsDictionary;
        }
    }
    #endregion MappingForm PROPERTIES

    private void btnOK_Click(object sender, EventArgs e)
    {
        this.CreateDocsDictionary();
        // then the others

        //when done close
        this.Close();
    }
}

One more to come

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

public class FieldMappingDictionary : Dictionary<string, List<string>>
{
    public void Add(string key, string value)
    {
        // if the key is already in the list
        if (base.Keys.Contains(key))
        {
            // only do it if not already there
            if (!base[key].Contains(value))
            {
                // add the new value
                base[key].Add(value);
            }
        }
        else
        {
            // if the key ain't there, add it and a new values list
            List<string> list = new List<string>();
            list.Add(value);
            base.Add(key, list);
        }
    }
}

This is just a collection used in the demo.

Look forward to hearing from you.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Looking into this now...

Hi Henry, have a good weekend?

I copied the code into two forms but I'm getting an error in the collection code you sent over. The errors I'm getting are:

1. expected {
2. Invalid token '>' in class, struct, or interface member declaration.

The error occurs at:

public class FieldMappingDictionary : Dictionary>

I placed this code outside the public partial class ListEditorForm : Form. Should I place this code somewhere else?

Also, looks like I have a couple of things coming down the pipe today so I may be fairly busy today. Just want you to know incase you don't hear back from me until later today.

Thank you,

Brenton

Brenton

Yes thanks, very restful.

I am a silly boy.
The errors are because in the reply box if you paste a < the editor replaces it with the html equivalent and the same with >.
If you replace all the '& gt;' with > and '& lt;' with <. all should be well.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Sorry Henry,

That is what I have and is giving the error. There are no '& gt;' or '& lt;' in the code. That must have happend when I copied and paste it into the reply. The error is occuring at public class FieldMappingDictionary : Dictionary>. I tried Dictionary <string, string> but it didn't work.

Here it is again.
This time I'll try to paste it properly. Smile | :)

<pre>
     public class FieldMappingDictionary : Dictionary<string, List<string>>
     {
          public void Add(string key, string value)
          {
               // if the key is already in the list
               if (base.Keys.Contains(key))
               {
                    // only do it if not already there
                    if (!base[key].Contains(value))
                    {
                         // add the new value
                         base[key].Add(value);
                    }
               }
               else
               {
                    // if the key ain't there, add it and a new values list
                    List<string> list = new List<string>();
                    list.Add(value);
                    base.Add(key, list);
               }
          }
     }
</pre>

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Hi Henry,

Almost there! I have one more problem. I am getting the error:

'System.Collections.Generic.Dictionary<string,System.Collections.Generic.List<string>>.KeyCollection' does not contain a definition for 'Contains'

This occurs on line 6:

if (base.Keys.Contains(key))

I keep forgetting that you are using VS2005.

Slight modification:
<pre>
     public class FieldMappingDictionary : Dictionary<string, List<string>>
     {
          public void Add(string key, string value)
          {
               // if the key is already in the list
               if (this.KeysContains(key)) // <================= Modified Line
               {
                    // only do it if not already there
                    if (!base[key].Contains(value))
                    {
                         // add the new value
                         base[key].Add(value);
                    }
               }
               else
               {
                    // if the key ain't there, add it and a new values list
                    List<string> list = new List<string>();
                    list.Add(value);
                    base.Add(key, list);
               }
          }

          public bool KeysContains(string key) // <=========== New Method, just iterates over keys collection, returns true if found
          {
               bool result = false;

               foreach (string s in base.Keys)
               {
                    if (s == key)
                    {
                         result = true;
                         break;
                    }
               }

               return result;
          }
     }
</pre>

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Incidentally I keep forgetting this.

You could save one step in your overall scheme, if loading the modified data into Excel is not a requirement.

SSIS can read XML directly, and both DataSet and DataTable have a method called WriteXML, which believe it, or not, writes their data (and/or schema) to an XML file.

As I said, if the Excel step is not a requirement, simply write the data to an XML file ready for SSIS.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Hi Henry,

Looks like I have added all the code I need and the code compiles without warnings but the dgv doesn't get populated. I belive the only place I needed to add code too is here. If I am wrong then please advise further.

private void ProcessMods()
          {
               // Create a copy of original table structure with
               // standardized column names**
               // load original file data into it
               // then do a series of updates using the docsDictionary, and the
               // other Dictionaries
               // STEP THROUGH THIS TO WATCH UPDATE STATEMENT BUILDING
               StringBuilder selectBuilder = null;
               foreach (string key in this.docsDictionary.Keys)
               {
                    foreach (string val in this.docsDictionary[key])
                    {
                         selectBuilder = new StringBuilder();
                         selectBuilder.Append("UPDATE TestData SET Documentation = ");
                         selectBuilder.Append(key);
                         selectBuilder.Append(" WHERE Documentation = ");
                         selectBuilder.Append(val);

                         string updateQuery = selectBuilder.ToString();

                         // execute the query here
                         // you know OleDbConnection, OleDbcommand etc.
                         // then go round for next mod

                                                            //This is where I added the connection
                              DataTable dFill = null;
                              String comString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path.GetDirectoryName(Path.GetFullPath(this.dataFile)) + @";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1\""";
                              using (OleDbConnection conCSV = new OleDbConnection(comString))
                              {
                                    conCSV.Open();
                                    OleDbCommand dbCommand = new OleDbCommand(updateQuery, conCSV);
                                    OleDbDataAdapter dAdapter = new OleDbDataAdapter(dbCommand);
                                    dFill = new DataTable();
                                    dAdapter.Fill(dFill);
                              }
                              bindingSource1.DataSource = dFill;
                              dataGridView1.DataSource = bindingSource1;
                    }
                    // go round for next key

               }

               // then do same for next dictionary
          }

Do I need to add code for a ShowDialog and capture the full path of the file? Also, the test file is in the debug folder. Maybe it would be easier to hardcode the path of the file in the com string. What do you think?

Thank you,

Brenton

modified on Wednesday, May 27, 2009 11:50 AM

Strictly speaking ProcessMods() was intended merely, to create a copy of the table containing client-data but with your standard column names (See the comments at start of the method). I have left that bit for you to do as I am not sure about the best method for that process. Then it should process all the updates retrieved from MappingForm. You could fill the grid during that operation, but it would slow it down enormously, although it would show progress.

The alternative would be to show the modifications after they are completed. In which case your dgv filling code should either be the very last thing in ProcessMods(), or immediately after the call to ProcessMods() in the MappingForm_Closed event handler (this would be my preference).

For ProcessMods() the parts left to be done are, as mentioned above, copying the data to a new table with proper column names (incidentally I suggested copying so that the original data remains intact, in case of accidents. Smile | :)

), and this bit:

// execute the query here
// you know OleDbConnection, OleDbcommand etc.
// then go round for next mod

which is immediately above your new dgv fill code.

bwood2020 wrote:
Do I need to add code for a ShowDialog and capture the full path of the file?

Yes, for the production version, you probably would. Putting the test data in the debug folder was simply for convenience for what is a demo of a concept.

If you stepped through the query building bit, I hope that you could see that for each of your standardised pieces of data the method builds an update query for each of the versions the client has used. It might turn out that using a query is not the best way to perform this operation, but the point of the exercise is to show that the data required to do the mods, is in a format which will enable the changes to be done fairly easily.

I am currently looking at methods for copying the client data into a table with standard column names.

I'll be back with you shortly. Of course, if you already know how to do this, let me know.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Just when you thought it was safe to ......... Big Grin | :-D

Some mods to the first form, I am posting the whole thing, because there are some renames, as well as some new methods and fields:

    List<string> standardList = new List<string>();
    Dictionary<string, string> clientHeaders = new Dictionary<string, string>();
    Dictionary<string, string> standardHeaders = new Dictionary<string, string>();
    DataSet testDataSet = null;
    DataTable headerTable = null;
    DataTable clientDataTable = null;
    DataTable standardDataTable = null;
    MappingForm mappingForm = null;
    readonly string dataFile = "TestData.csv";

    FieldMappingDictionary docsDictionary = null;

    public ListEditorForm()
    {
        InitializeComponent();
        InitStandardList();
    }

    private void InitStandardList()
    {
        standardList.AddRange(new string[] { "Property", "Offer", "Documentation", "Figures", "Guarantee" });
    }

    private void ProcessMods()
    {
        // Create a copy of original table structure with
        // standardized column names**
        // load original file data into it
        this.GetClientData();
        this.CreateStandardTable();

        // then do a series of updates using the docsDictionary, and the
        // other Dictionaries
        // STEP THROUGH THIS TO WATCH UPDATE STATEMENT BUILDING
        //StringBuilder selectBuilder = null;
        foreach (string key in this.docsDictionary.Keys)
        {
            foreach (string val in this.docsDictionary[key])
            {
                //selectBuilder = new StringBuilder();
                //selectBuilder.Append("UPDATE <yourtablenamehere**> SET Documentation = ");
                //selectBuilder.Append(key);
                //selectBuilder.Append(" WHERE Documentation = ");
                //selectBuilder.Append(val);

                //string updateQuery = selectBuilder.ToString();

                // execute the query here
                // you know OleDbConnection, OleDbcommand etc.
                // then go round for next mod
                foreach (DataRow row in this.standardDataTable.Rows)
                {
                    if (row["Documentation"].ToString() == val)
                    {
                        row["Documentation"] = key;
                    }
                }
            }
            // go round for next key
        }

        // then do same for next dictionary
    }

    private void MakeHeaderDictionaries()
    {
        this.clientHeaders = this.MakeHeaderDictionary();
        this.standardHeaders = this.MakeStandardHeaderDictionary();
    }

    private Dictionary<string, string> MakeStandardHeaderDictionary()
    {
        Dictionary<string, string> result = new Dictionary<string, string>();
        foreach (DataRow row in this.headerTable.Rows)
        {
            result.Add(row[0].ToString().Trim(), row[1].ToString().Trim());
        }

        return result;
    }

    private Dictionary<string, string> MakeHeaderDictionary()
    {
        Dictionary<string, string> result = new Dictionary<string, string>();
        foreach (DataRow row in this.headerTable.Rows)
        {
            result.Add(row[1].ToString().Trim(), row[0].ToString().Trim());
        }

        return result;
    }

    private void GetClientData()
    {
        //Connect to csv file
        String comString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
            Path.GetDirectoryName(Path.GetFullPath(this.dataFile)) + @";Extended Properties=""Text;HDR=YES;FMT=Delimited;IMEX=1\""";

        String strSql = "SELECT * FROM [" + Path.GetFileName(this.dataFile) + "]";
        using (OleDbConnection conCSV = new OleDbConnection(comString))
        {
            conCSV.Open();
            OleDbCommand dbCommand = new OleDbCommand(strSql, conCSV);
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(dbCommand);
            clientDataTable = new DataTable("ClientData");
            dAdapter.Fill(clientDataTable);

            this.testDataSet.Tables.Add(clientDataTable);
        }
    }

    private void CreateStandardTable()
    {
        this.standardDataTable = this.clientDataTable.Copy();
        this.standardDataTable.TableName = "StandardData";
        for (int i = 0; i < this.standardDataTable.Columns.Count; i++)
        {
            this.standardDataTable.Columns[i].ColumnName =
                this.standardHeaders[this.clientDataTable.Columns[i].ColumnName.Trim()];
        }

        this.testDataSet.Tables.Add(standardDataTable);
    }

    #region ListEditorForm PROPERTIES ...............................
    public Dictionary<string, string> ClientHeaders
    {
        get
        {
            return this.clientHeaders;
        }

        private set
        {
            this.clientHeaders = value;
        }
    }

    public Dictionary<string, string> StandardHeaders
    {
        get
        {
            return this.standardHeaders;
        }

        private set
        {
            this.standardHeaders = value;
        }
    }

    private MappingForm MappingForm
    {
        get
        {
            if (this.mappingForm == null)
            {
                this.mappingForm = new MappingForm();
            }

            return this.mappingForm;
        }
    }
    #endregion ListEditorForm PROPERTIES

    private void ListEditorForm_Load(object sender, EventArgs e)
    {
        string headerLine;
        using (StreamReader sr = new StreamReader(dataFile))
        {
            headerLine = sr.ReadLine();
        }

        testDataSet = new DataSet("HeaderDataSet");
        headerTable = new DataTable("ClientHeaders");
        DataColumn newColumn = new DataColumn("ClientHeader", typeof(string));
        headerTable.Columns.Add(newColumn);
        newColumn = new DataColumn("StandardHeader", typeof(string));
        headerTable.Columns.Add(newColumn);

        DataRow newRow = null;
        foreach (string s in headerLine.Split(','))
        {
            newRow = headerTable.NewRow();
            newRow[0] = s;
            newRow[1] = "Property";
            headerTable.Rows.Add(newRow);
        }

        testDataSet.Tables.Add(headerTable);

        DataGridViewComboBoxColumn cbCol =
            this.dataGridView1.Columns["standardColumn"] as DataGridViewComboBoxColumn;
        cbCol.DataSource = this.standardList;

        this.dataGridView1.AutoGenerateColumns = false;
        this.dataGridView1.Columns[0].DataPropertyName = "ClientHeader";
        this.dataGridView1.Columns[1].DataPropertyName = "StandardHeader";
        this.dataGridView1.DataSource = testDataSet;
        this.dataGridView1.DataMember = "ClientHeaders";
    }

    private void btnGetValue_Click(object sender, EventArgs e)
    {
        this.MakeHeaderDictionaries();
        this.MappingForm.HeaderDictionary = this.clientHeaders;
        this.MappingForm.DataFile = this.dataFile;
        this.MappingForm.FormClosing += new FormClosingEventHandler(MappingForm_Closed);
        this.MappingForm.Show();
    }

    void MappingForm_Closed(object sender, FormClosingEventArgs e)
    {
        this.docsDictionary = this.MappingForm.DocumentsDictionary;
        // get rest of dictionaries here

        this.ProcessMods();

        this.testDataSet.Tables["StandardData"].WriteXml("TestData.xml", XmlWriteMode.WriteSchema);
    }
}

There are no changes to MappingForm, at this time.
TestData.xml will be in the debug directory. When examining it you must remember that the code, as is, only corrects the 'Documentation' column.

I also found out why the FieldMappingDictionary gave you errors the first time, here is the corrected version:

public class FieldMappingDictionary : Dictionary<string, List<string>>
{
    public void Add(string key, string value)
    {
        // if the key is already in the Dictionary
        if (base.ContainsKey(key))
        {
            // only do it if value not already there
            if (!base[key].Contains(value))
            {
                // add the new value
                base[key].Add(value);
            }
        }
        else
        {
            // if the key ain't there, add it in a new values list
            List<string> list = new List<string>();
            list.Add(value);
            base.Add(key, list);
        }
    }
}

Let me know what you think. Be gentle with me. Smile | :)

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

If I had more than two brain cells to rub together, I would have written this:

void MappingForm_Closed(object sender, FormClosingEventArgs e)
{
    this.docsDictionary = this.MappingForm.DocumentsDictionary;
    // get rest of dictionaries here

    this.ProcessMods();

    this.testDataSet.Tables["ClientData"].WriteXml("ClientTestData.xml", XmlWriteMode.WriteSchema); // <==== new
    this.testDataSet.Tables["StandardData"].WriteXml("StandardTestData.xml", XmlWriteMode.WriteSchema); // <== filename changed
}

This should make it easier to compare unmodified and modified data. You should also note that the column names have been modified in the StandardTestData.xml version.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Henry,

This is great! Very well organized and I like the idea of having the ability to contain the integrity of the original data set. I also like the idea of outputting to an xml file as well as comparing both the original data set to the standardized data set.

I did have to modify some of the code you sent over. I had to add <string> to List and <string, string> to the Dictionaries. Let me know if this is not correct and I will go back through and make the recommended changes.

I'm still having problems populating the dgv on form 1. Maybe it's that I have not added some functionality to it. On Form 1 I added a dgv and a button name dataGridView1 and btnGetValue. On Form 2 I added a dgv and a button named dgvDocumentation and btnOK. Am I forgetting anything? I have read through the code and it compiles but just can't pinpoint what's going on.

You’re the man Henry!!!

bwood2020 wrote:
I'm still having problems populating the dgv on form 1. Maybe it's that I have not added some functionality to it. On Form 1 I added a dgv and a button name dataGridView1 and btnGetValue. On Form 2 I added a dgv and a button named dgvDocumentation and btnOK. Am I forgetting anything? I have read through the code and it compiles but just can't pinpoint what's going on.

That sounds right.
I'll go through my designer code to see if I've forgotten to tell you something obvious.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Er..................
I did tell you that I had added two dgv columns, in the designer, didn't I?

No, you say!

ahem:

private void InitializeComponent()
{
    this.components = new System.ComponentModel.Container();
    this.dataGridView1 = new System.Windows.Forms.DataGridView();
    this.btnGetValue = new System.Windows.Forms.Button();
    this.clientColumn = new System.Windows.Forms.DataGridViewTextBoxColumn();
    this.standardColumn = new System.Windows.Forms.DataGridViewComboBoxColumn();
    this.headersBindingSource = new System.Windows.Forms.BindingSource(this.components);
    ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
    ((System.ComponentModel.ISupportInitialize)(this.headersBindingSource)).BeginInit();
    this.SuspendLayout();
    //
    // dataGridView1
    //
    this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
    this.dataGridView1.Columns.AddRange(new System.Windows.Forms.DataGridViewColumn[] {
    this.clientColumn,
    this.standardColumn});
    this.dataGridView1.Location = new System.Drawing.Point(12, 12);
    this.dataGridView1.Name = "dataGridView1";
    this.dataGridView1.Size = new System.Drawing.Size(284, 171);
    this.dataGridView1.TabIndex = 0;
    //
    // btnGetValue
    //
    this.btnGetValue.Location = new System.Drawing.Point(154, 236);
    this.btnGetValue.Name = "btnGetValue";
    this.btnGetValue.Size = new System.Drawing.Size(75, 23);
    this.btnGetValue.TabIndex = 1;
    this.btnGetValue.Text = "Value";
    this.btnGetValue.UseVisualStyleBackColor = true;
    this.btnGetValue.Click += new System.EventHandler(this.btnGetValue_Click);
    //
    // clientColumn
    //
    this.clientColumn.HeaderText = "Client Header";
    this.clientColumn.Name = "clientColumn";
    //
    // standardColumn
    //
    this.standardColumn.HeaderText = "Standard Header";
    this.standardColumn.Name = "standardColumn";
    //
    // ListEditorForm
    //
    this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 14F);
    this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
    this.ClientSize = new System.Drawing.Size(422, 288);
    this.Controls.Add(this.btnGetValue);
    this.Controls.Add(this.dataGridView1);
    this.Name = "ListEditorForm";
    this.Text = "List Editor";
    this.Load += new System.EventHandler(this.ListEditorForm_Load);
    ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
    ((System.ComponentModel.ISupportInitialize)(this.headersBindingSource)).EndInit();
    this.ResumeLayout(false);

}

#endregion

private System.Windows.Forms.DataGridView dataGridView1;
private System.Windows.Forms.Button btnGetValue;
private System.Windows.Forms.BindingSource headersBindingSource;
private System.Windows.Forms.DataGridViewTextBoxColumn clientColumn;
private System.Windows.Forms.DataGridViewComboBoxColumn standardColumn;

Here is the designer code for the first form. Just replace the code in your form1.designer.cs file with it.

I think I'll go for a lie down in a dark room now.

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”