C#

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

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

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();
    }
}

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();
    }
}

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

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

bwood2020 wrote:

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

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

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

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
}