Working with Delimited Text Files in C#

Introduction

This article shall describe an approach to working with delimited text files. In general it will discuss the construction of the connection strings and schema.ini files needed to bind to a delimited text file and it will define an approach for editing and saving delimited text files.



Figure 1: CSV Editor File displaying a delimited text file



Figure 2: Creating a schema file definition

Working with delimited text files can be a barrel of laughs but it is certainly something one expects to do from time to time. The article will focus on creating connections for binding to and reading/editing comma delimited text files, tab delimited text files, custom character delimited text files, and fixed column width text files. Different types of delimited files exist because not all delimiter types work for all files as based upon the contents of those files. For example, if one of the columns of the file contains a business name and the file is comma delimited, it is going to cause a bit of trouble if the business name is something like "Acme Tool Company, Inc." In such cases, if the fields may contain commas, using a comma as a delimiter is a bad idea.

In order to establish an OLE DB connection to a delimited file, you need to have a schema.ini file residing in the same location as the delimited file you are trying to open for viewing or edit (or some other processing). To that end, the approach used in this application is to provide a dialog used to capture the content needed to construct a schema.ini file. This information is also used to build a connection string used to connect to the file as a viable data source.



Figure 3: A Schema.ini file opened in Notepad

The schema.ini file shown in Figure 3 is used to open a comma delimited file that contains column headers in the first row and that contains four columns. The first column is called "ID" and the data type is a short, the next three columns are "First", "Last", and "City" and those columns are all text. This schema.ini file was build using the application and it may be used to open the file shown in brackets on the first line of the file.

Getting Started

The solution contains two projects; the first is a Windows form application entitled, "CsvEditor" and the second is a class library project entitled, "SchemaSpec". The windows form application contains three forms, the editor form is used to open, edit, save, and close a delimited text file. The schema form is used to build a viable schema definition that may be then used to build a schema.ini file. The last form is just an about box.

The schema definition file in the second project contains two classes; one is used to store the schema definition, and the other is used to hold the column specifications for each of the columns present in the delimited text file.



Figure 4: Solution Explorer with the Projects Visible



Figure 5: Application Settings used to hold the schema definition and connection string

First Project - SchemaSpec:

Code: Schema Definition Class File (SchemaDef.cs)

The schema specification file contains two classes; the details for the two classes are contained in the notation included with the file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

// Used to contain information about the delimited file to be opened
// using the CSV editor application

namespace SchemaSpec
{
    /// <summary>
    /// The item specification class is used to 
    /// define the columns contained within the 
    /// delimited file to be opened.  For each column
    /// we need to know the data type (and I am 
    /// using the jet data types here), the column 
    /// number, the column name, data type, and 
    /// column width (if the file is delimited 
    /// using fixed widths alone) - a list of 
    /// item specification is added to the 
    /// schema definition trailing this class
    /// </summary>
    [Serializable]
    public class ItemSpecification
    {
        // this enumeration is used to 
        // limit the type data property to 
        // a matching jet data type
        public enum JetDataType
        {
            Bit,
            Byte,
            Short,
            Long,
            Currency,
            Single,
            Double,
            DateTime,
            Text,
            Memo
        };

        // the position of the column beginning with 1 to n
        public int ColumnNumber { get; set; }

        // the column name
        public string Name { get; set; }

        // the data type
        public JetDataType TypeData { get; set; }

        // optional column width for fixed width files
        public int ColumnWidth { get; set; }
    }

    /// <summary>
    /// The schema definition class is used to hold the 
    /// contents of the schema.ini file used by the 
    /// connection to open a delimited file (using 
    /// an oledb connection).  The schema dialog is used 
    /// to define a schema definition which is stored as a 
    /// application property
    /// </summary>
    [Serializable]
    public class SchemeDef
    {
        /// <summary>
        /// the constructor will create a default comma delimited 
        /// file definition with an empty list of items specifications 
        /// and will default to set the first row is a header row 
        /// option to false
        /// </summary>
        public SchemeDef()
        {
            DelimiterType = DelimType.CsvDelimited;
            ColumnDefinition = new List<ItemSpecification>();
            UsesHeader = FirstRowHeader.No;
        }

        // this enumeration is used to limit the delimiter types 
        // to one of the four we are interested in which are 
        // comma delimited, tab delimited, custom delimited 
        // (such as a pipe or an underscore), or fixed column 
        // widths
        public enum DelimType
        {
            CsvDelimited,
            TabDelimited,
            CustomDelimited,
            FixedWidth
        };

        // This enum allows the first row is a header 
        // row option to be set to yes or no; that text 
        // is used in the connection string (rather than 
        // true or false)
        public enum FirstRowHeader
        {
            Yes,
            No
        };
        // The properties used to build the schema.ini file include the 
        // delimiter type, a custom delimiter (if used), a list of 
        // column definitions, and a determination as to whether 
        // the first row of the file contains header information rather 
        // than data
        public DelimType DelimiterType { get; set; }
        public string CustomDelimiter { get; set; }
        public List<ItemSpecification> ColumnDefinition { get; set; }
        public FirstRowHeader UsesHeader { get; set; }
    }

}

Second Project - CSV Editor

Code: Editor Form (frmEditor.cs)

The editor form is used to open, edit, and save a delimited text file. The code will create a connection string and a schema.ini file (placed in the path of the file you are trying to open) when a file is opened. The schema definition is stored in the application settings when the user creates a schema definition using the schema definition dialog; this form will recover that definition and use it to construct the schema.ini file and the connection string. Files are opened using an OLE DB connection and files saves are accomplished by overwriting the contents of the file using the contents of the data grid (which allows saving the edits made in the grid; it also allows cancelling the edits since nothing is committed until the file is overwritten). A binding source and navigator are used to traverse, view, and edit the data.

You may not need to do all of that, you might just want to connect to a delimited text file in some fixed format; if so, just look at the connection string and schema.ini file related code relevant to that particular task. If you just want to save grid based edits to a delimited text file, you might just want to look at the code used to save the file.

The code for this form class is annotated and should be easy enough to follow. The code contained in the LoadDataFile and SaveFileChanges methods will cover the more important parts of the form's code and the purpose of each is self-explanatory.

Lastly, there is a "Process File" button on the form; it does nothing but the intent was, if you wanted to open a delimited text file and do something with it, the code present will get the file open, you can use the button's click event handler to do something with the file contents. Once the connection to the file is open, you can loop through the contents as you would with any other OLE DB connection.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Net;

namespace CsvEditor
{
    public partial class frmEditor : Form
    { 

        #region Form Variable Declarations

        private string CsvFilePath;
        private BindingSource bsrc;
        private OleDbDataAdapter adp;
        private DataTable dt;

        #endregion

        /// <summary>
        /// Create a new, empty binding source in construction
        /// </summary>
        public frmEditor()
        {
            InitializeComponent();
            bsrc = new BindingSource();
        }
 

        /// <summary>
        /// Open the editor used to define the current schema; this tool will allow the user
         /// define the schema that will be used to create a connection string and a schema.ini file
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void createToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmSchema f = new frmSchema();
            f.ShowDialog();
        }

        /// <summary>
        /// Display the current connection string; this applies to the last 
               /// open file as the connection string is 
        /// not really defined and stored until a file is actually opened with the defined schema
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void viewCurrentSchemaToolStripMenuItem_Click(object sender, 
         EventArgs e)
        {
            MessageBox.Show(Properties.Settings.Default.ConnString, 
                 "Current Connection");
        }

        /// <summary>
        /// Use the current schema to build a connection string for the 
               /// delimited text file and use that connection
        /// string to open a delimited text file
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void openToolStripMenuItem_Click(object sender, EventArgs e)
        {
            // define the open file dialog particulars; you could add txt files if you were
            // so inclined
            openFileDialog1.Title = "Source CSV File";
            openFileDialog1.Filter = "csv files (*.csv)|*.csv";
            openFileDialog1.FileName = string.Empty;

            // return if the user cancels the open file dialog
            if (openFileDialog1.ShowDialog() == DialogResult.Cancel)
                return;
 
            // set the string variable used to hold the path to the delimited text file
            // to the open file dialog's file name
            CsvFilePath = openFileDialog1.FileName;

            // if the string is empty, give warning and return
            if (String.IsNullOrEmpty(CsvFilePath))
            {
                MessageBox.Show("File path does not exist", "File");
                return;
            }

            // make certain the file actually exists before proceeding
            if (System.IO.File.Exists(CsvFilePath) == false)
            {
                MessageBox.Show(String.Format("File {0} does not exist", 
                 CsvFilePath), "Missing File");
                return;
            }

            // Load the delimited file into the form's data grid
            LoadFileData(CsvFilePath);

            // if the file actually has content, enable saving
            if (bsrc.Count > 0)
                bindingNavigatorSave.Enabled = true;
        }

        /// <summary>
        /// Load the CSV file into the data grid view for 
        /// user inspection and edit
        /// </summary>
        /// <param name="inputFile">Path to the source CSV file</param>
        private void LoadFileData(string inputFile)
        {
            // verify the file path string has content
            if (String.IsNullOrEmpty(inputFile))
            {
                MessageBox.Show("You must provide a source file in order to proceed",
                  "Missing File");
                return;
            }

            // get the path to use in defining the connection string
            string path = Path.GetDirectoryName(inputFile);

            // create the schema file using the requested file path and the existing schema definition;
            // the schema definition is stored in app settings as an instance of the SchemaDef class
            CreateSchemaIni(inputFile);

            // create a new schema definition, make sure the user has created a schema file
            // before proceeding - the schema definition is stored in the application properties
            SchemaSpec.SchemeDef sdef = new SchemaSpec.SchemeDef();
            if (Properties.Settings.Default.SchemaSpec == null)
            {
                MessageBox.Show("No schema has been defined; prior to opening 
                 a CSV file, use the Schema tool to construct a schema definition", 
                 "Missing Schema");
                return;
            }
            else
            {
                sdef = Properties.Settings.Default.SchemaSpec;
            }

            // create a variable to hold the connection string
            string connbit = string.Empty;

            // based upon the use of a header line and the delimiter type, create the correct
            // type of connection string
            switch (sdef.DelimiterType)
            {
                case SchemaSpec.SchemeDef.DelimType.CsvDelimited:
                    if(sdef.UsesHeader == SchemaSpec.SchemeDef.FirstRowHeader.Yes)
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=Yes;FMT=CsvDelimited""";
                    else
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=No;FMT=CsvDelimited""";
                    break;
                case SchemaSpec.SchemeDef.DelimType.CustomDelimited:
                    if (sdef.UsesHeader == SchemaSpec.SchemeDef.FirstRowHeader.Yes)
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=Yes;FMT=Delimited(" 
                          + sdef.CustomDelimiter + ")" + "\"";
                    else
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=No;FMT=Delimited(" 
                          + sdef.CustomDelimiter + ")" + "\"";
                    break;
                case SchemaSpec.SchemeDef.DelimType.FixedWidth:
                    if (sdef.UsesHeader == SchemaSpec.SchemeDef.FirstRowHeader.Yes)
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=Yes;FMT=FixedLength""";
                    else
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=No;FMT=FixedLength""";
                    break;
                case SchemaSpec.SchemeDef.DelimType.TabDelimited:
                    if (sdef.UsesHeader == SchemaSpec.SchemeDef.FirstRowHeader.Yes)
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=Yes;FMT=TabDelimited""";
                    else
                        connbit = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
                          + path + @";Extended Properties=
                          ""Text;HDR=No;FMT=TabDelimited""";
                    break;
                default:
                    break;
            }

            // put the connection string into the properties and save the properties
            Properties.Settings.Default.ConnString = connbit;
            Properties.Settings.Default.Save();

            // make sure we have a connection string before proceeding
            if (String.IsNullOrEmpty(connbit))
            {
                MessageBox.Show("Invalid Schema; use the scheme utility to 
                 define a schema for the file you are attempting to open", 
                 "Invalid Schema");
            }

            // use the connection string to open an ole db connection to the delimited file
            using (OleDbConnection conn = new OleDbConnection(connbit))
            {
                using (OleDbCommand cmd =
                    new OleDbCommand("SELECT * FROM " + 
                     Path.GetFileName(inputFile), conn))
                {
                    conn.Open();

                    adp = new OleDbDataAdapter(cmd);
                    dt = new DataTable();
                    adp.Fill(dt);

                    // set the binding source's data source to the data table
                    // and set the grid's data source to the binding source
                    bsrc.DataSource = dt;
                    dgvMaster.DataSource = bsrc;

                    // create a variable to keep track of the column position
                    int i = 0;

                    foreach (SchemaSpec.ItemSpecification s in sdef.ColumnDefinition)
                    {
                        // set the column header text to match the column name from the 
                        // item specification
                        dgvMaster.Columns[i].HeaderText = s.Name;
                        i++;
                    }

                    // widen out the last row to fill the available space to tidy up 
                    // the grid a bit
                    dgvMaster.Columns[dgvMaster.Columns.Count - 1]
                    .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;

                    // set the binding source navigator to the binding source
                    bnav.BindingSource = bsrc;

                    // display the file name to the user
                    groupBox1.Text = "Input File" + " -  Reading " + inputFile;
                }
            }
        }

        /// <summary>
        /// Overwrite the existing input file will all changes made 
        /// thus far. 
        /// </summary>
        private void SaveFileChanges()
        {
            // if the data adapter is not null, end the edit session
            if (adp != null)
            {
                dgvMaster.EndEdit();

                // make the the schema definition exists and set the current schema 
                // definition to the stored schema definition
                SchemaSpec.SchemeDef sdef = new SchemaSpec.SchemeDef();
                if (Properties.Settings.Default.SchemaSpec == null)
                {
                    MessageBox.Show("No schema has been defined; prior to 
                     working with a CSV file, use the Schema tool to construct 
                    a schema definition", "Missing Schema");
                    return;
                }
                else
                {
                    sdef = Properties.Settings.Default.SchemaSpec;
                }

                // create a string builder instance to hold our text from the
                // data grid
                StringBuilder sb = new StringBuilder();
                int intCols = dt.Columns.Count;

                switch (sdef.DelimiterType)
                {
                    case SchemaSpec.SchemeDef.DelimType.CsvDelimited:
                        // if the file uses a header, add the column header text 
                                             // back in as the column name source
                        // we will just get it from the grid though the grid may not 
                                             // initially match the text file as the 
                        // user may define the columns using any name they want to apply to it
                        if (Properties.Settings.Default.UsesHeader == true)
                        {
                            StringBuilder sbTitleRow = new StringBuilder();
                            for(int i=0; i<dgvMaster.ColumnCount; i++)
                            {
                                sbTitleRow.Append(dgvMaster.Columns[i].HeaderText);
                                if ((i + 1) != intCols)
                                    sbTitleRow.Append(",");
                                else
                                    sbTitleRow.Append("\n");
                            }
                            sb.Append(sbTitleRow.ToString());
                        }
                        // populate the rows with the data (including any edits); that version 
                                             // will overwrite the 
                        // existing content in the file so as to capture those edits – 
                                             // of course in your version you
                        // may wish to disable editing altogether.
                        foreach (DataRowView dr in dt.DefaultView)
                        {
                            for (int x = 0; x < intCols; x++)
                            {
                                sb.Append(dr[x].ToString());
                                if ((x + 1) != intCols)
                                {
                                    sb.Append(","); // comma delimited
                                }
                            }
                            sb.Append("\n"); // terminate the row
                        }
                        break;
                    case SchemaSpec.SchemeDef.DelimType.CustomDelimited:
                        // if the file uses a header, add the column header 
                                             // text back in as the column name source
                        if (Properties.Settings.Default.UsesHeader == true)
                        {
                            StringBuilder sbTitleRow = new StringBuilder();
                            for (int i = 0; i < dgvMaster.ColumnCount; i++)
                            {
                                sbTitleRow.Append(dgvMaster.Columns[i].HeaderText);
                                if ((i + 1) != intCols)
                                    sbTitleRow.Append(sdef.CustomDelimiter);
                                            // user defined delimiter such as a pipe
                                else
                                    sbTitleRow.Append("\n"); // terminate the row
                            }
                            sb.Append(sbTitleRow.ToString());
                        }
                        // populate the rows
                        foreach (DataRowView dr in dt.DefaultView)
                        {
                            for (int x = 0; x < intCols; x++)
                            {
                                sb.Append(dr[x].ToString());
                                if ((x + 1) != intCols)
                                {
                                    sb.Append(sdef.CustomDelimiter);
                                            // user defined delimiter
                                }
                            }
                            sb.Append("\n"); // terminate the line
                        }
                        break;
                    case SchemaSpec.SchemeDef.DelimType.FixedWidth:
                        // if the file uses a header, add the column header 
                                             //text back in as the column name source
                        if (Properties.Settings.Default.UsesHeader == true)
                        {
                            StringBuilder sbTitleRow = new StringBuilder();
                            for (int i = 0; i < dgvMaster.ColumnCount; i++)
                            {
                                // being fixed width, we need to right pad the 
                                                           // values to attain the proper
                                // column widths-the column widths are stored with the 
                                                            //schema definition,
                                // we don't use any delimiters on this type so just the 
                                                           // padding goes in
                                sbTitleRow.Append(dgvMaster.Columns[i].HeaderText.
                                   ToString().Trim().PadRight(
                                   sdef.ColumnDefinition[i].ColumnWidth));

                                if ((i + 1) == intCols)
                                    sbTitleRow.Append("\n");
                            }
                            sb.Append(sbTitleRow.ToString());
                        }
                        // populate the rows
                        foreach (DataRowView dr in dt.DefaultView)
                        {
                            for (int x = 0; x < intCols; x++)
                            {
                                sb.Append(dr[x].ToString().Trim().
                                   PadRight(sdef.ColumnDefinition[x].ColumnWidth));
                            }
                            sb.Append("\n");
                        }
                        break;
                    case SchemaSpec.SchemeDef.DelimType.TabDelimited:
                        // if the file uses a header, add the column header text back 
                                             //in as the column name source
                        if (Properties.Settings.Default.UsesHeader == true)
                        {
                            StringBuilder sbTitleRow = new StringBuilder();
                            for (int i = 0; i < dgvMaster.ColumnCount; i++)
                            {
                                sbTitleRow.Append(dgvMaster.Columns[i].HeaderText);
                                if ((i + 1) != intCols)
                                    sbTitleRow.Append("\t"); // tab delimited
                                else
                                    sbTitleRow.Append("\n"); // terminate the row
                            }
                            sb.Append(sbTitleRow.ToString());
                        }
                        // populate the rows
                        foreach (DataRowView dr in dt.DefaultView)
                        {
                            for (int x = 0; x < intCols; x++)
                            {
                                sb.Append(dr[x].ToString());
                                if ((x + 1) != intCols)
                                {
                                    sb.Append("\t"); // tab delimited
                                }
                            }
                            sb.Append("\n"); // terminate the row
                        }
                        break;
                    default:
                        break;
                }

                // use a stream writer to overwrite the existing file with the new content 
                // including any new rows and edits to existing rows
                using (StreamWriter sw = new StreamWriter(CsvFilePath))
                {
                    try
                    {
                        sw.Write(sb.ToString());
                        MessageBox.Show("File " + CsvFilePath + " saved.", 
                          "File Saved");
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Unable to save: " + CsvFilePath + 
                          ", Error " + ex.Message, "Save Error");
                    }
                }
            }
        }

        /// <summary>
        /// Create a schema.ini file to control the format and data types used 
        /// within the applications - this must be saved in the path of the input file.
        /// It will overwrite any existing schema.ini file there but the whole process
        /// is transparent to the end user.  The specification of the actual input file
        /// received from Intuit/Medfusion must match here exactly.
        /// 
        /// If you wish to conceal any information from the end user just hide the column 
        /// in LoadFileData()
        /// </summary>
        /// <param name="filePath"></param>
        private void CreateSchemaIni(string filePath)
        {
            try
            {
                // define a new schema definition and populate it from the 
                // application properties
                SchemaSpec.SchemeDef sdef = new SchemaSpec.SchemeDef();
                if (Properties.Settings.Default.SchemaSpec == null)
                {
                    MessageBox.Show("No schema has been defined; prior to opening a 
                    CSV file, use the Schema tool to construct a schema definition", 
                          "Missing Schema");
                    return;
                }
                else
                {
                    sdef = Properties.Settings.Default.SchemaSpec;
                }

                // start a string builder to hold the contents of the schema file as it is construction
                StringBuilder sb = new StringBuilder();

                // the first line of the schema file is the file name in brackets
                sb.Append("[" + Path.GetFileName(filePath) + "]" 
                 + Environment.NewLine);

                // the next line of the schema file will be used to determine whether or not
                // the first line of the file contains column headers or not
                string colHeader = sdef.UsesHeader == 
                 SchemaSpec.SchemeDef.FirstRowHeader.No ? 
                 "ColNameHeader=False" : "ColNameHeader=True";
                sb.Append(colHeader + Environment.NewLine);

                //  next we need to add the format to the schema file
                switch (sdef.DelimiterType)
                {
                    case SchemaSpec.SchemeDef.DelimType.CsvDelimited:
                        // a comma delimited file
                        sb.Append("Format=CsvDelimited" + Environment.NewLine);
                        break;
                    case SchemaSpec.SchemeDef.DelimType.CustomDelimited:
                        // a custom delimiter is used here; need to check and make sure the user
                        // provided a character to serve as a delimiter
                        if (String.IsNullOrEmpty(sdef.CustomDelimiter))
                        {
                            MessageBox.Show("A custom delimiter was not identified 
                             for this schema.", "Invalid Schema");
                            return;
                        }
                        sb.Append("Format=Delimited(" + sdef.CustomDelimiter + ")" + 
                          Environment.NewLine);
                        break;
                    case SchemaSpec.SchemeDef.DelimType.FixedWidth:
                        // the file columns here have a fixed width; no other delimiter is supplied
                        sb.Append("Format=FixedLength" + Environment.NewLine);
                        break;
                    case SchemaSpec.SchemeDef.DelimType.TabDelimited:
                        // the columns here are tab delimited
                        sb.Append("Format=TabDelimited" + Environment.NewLine);
                        break;
                    default:
                        break;
                }

                // next each column number, name and data type is added to the schema file
                foreach (SchemaSpec.ItemSpecification s in sdef.ColumnDefinition)
                {
                    string tmp = "Col" + s.ColumnNumber.ToString() + "=" + s.Name 
                    + " " + s.TypeData;

                    if (s.ColumnWidth > 0)
                        tmp += " Width " + s.ColumnWidth.ToString();

                    sb.Append(tmp + Environment.NewLine);
                }

                // the schema.ini file has to live in the same folder as the file we 
                             // are going to open; it has to carry the name
                // schema.ini.  When we connect to the file, the connection will 
                             // find and use this schema.ini file to 
                // determine how to treat the file contents; only the correct 
                              // schema.ini file for a particular file type can 
                // be used you cannot, for example, open a comma delimited 
                             // file with a schema.ini file defined for a 
                // pipe delimited file.
                using (StreamWriter outfile = new StreamWriter(Path.GetDirectoryName(
                 filePath) + @"\schema.ini"))
                {
                    outfile.Write(sb.ToString());
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }

        /// <summary>
        /// Exit the application
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        /// <summary>
        /// We will just exit, you might want to do something else with cancel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnCancel_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        /// <summary>
        /// Assuming that you want to open the delimited text file for some purpose, you could 
        /// add that processing routine here
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSubmit_Click(object sender, EventArgs e)
        {
            // Process the file to do whatever it is you want to do with the 
            // delimited text file import 
            MessageBox.Show("Add your code here to process the delimited file as 
             needed.", "Feature Not Implemented");
        }

        /// <summary>
        /// the click event handler for the navigator's save button
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bindingNavigatorSave_Click(object sender, EventArgs e)
        {
            SaveFileChanges();
        }

        /// <summary>
        /// Show the about box
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void aboutCSVEditorToolStripMenuItem_Click(object sender, EventArgs e)
        {
            frmAbout f = new frmAbout();
            f.ShowDialog(this);
        }

    }
}

Code: Schema Definition Form (frmSchema.cs)

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SchemaSpec; 

namespace CsvEditor
{
    public partial class frmSchema : Form
    { 
        // contains the schema definition
        // defined for use in opening the
        // delimited file within the application
        public SchemeDef sdef;
 
        // a binding source used for navigating and editing the 
        // file contents
        private BindingSource bsrc;
 
        public frmSchema()
        {
            InitializeComponent();
            bsrc = new BindingSource();
 
            // if an existing schema file has been included, use it to 
            // set the schema definition and to populate the form for edit
            if (Properties.Settings.Default.SchemaSpec != null)
            {
                sdef = Properties.Settings.Default.SchemaSpec;
                LoadFromSdef();
            }
        }
 
        /// <summary>
        /// Close the form without saving anything
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }
        /// <summary>
        /// Populate the form fields using the current 
        /// schema definition
        /// </summary>
        private void LoadFromSdef()
        {
            if (sdef != null)
            {
               switch(sdef.DelimiterType.ToString())
                {
                    case "CsvDelimited":
                        cboDelimType.Text = "Csv Delimited";
                        txtCustomDelimiter.Enabled = false;
                        txtCustomDelimiter.Text = string.Empty;
                        break;
                    case "TabDelimited":
                        cboDelimType.Text = "Tab Delimited";
                        txtCustomDelimiter.Enabled = false;
                        txtCustomDelimiter.Text = string.Empty;
                        break;
                    case "CustomDelimited":
                        cboDelimType.Text = "Custom Delimited";
                        
                        if (!string.IsNullOrEmpty(sdef.CustomDelimiter))
                            txtCustomDelimiter.Text = sdef.CustomDelimiter;
 
                        txtCustomDelimiter.Enabled = true;
                        break;
                    case "FixedWidth":
                        cboDelimType.Text = "Fixed Width";
                        txtCustomDelimiter.Enabled = false;
                        txtCustomDelimiter.Text = string.Empty;
                        break;
                    default:
                        break;
                }
 
                if(sdef.UsesHeader == SchemeDef.FirstRowHeader.Yes)
                    chkHeaders.Checked = true;
                else
                    chkHeaders.Checked = false;
 
                // show the defined schema columns and data in the grid
                dgvRowView.DataSource = null;
                bsrc.DataSource = sdef.ColumnDefinition;
                dgvRowView.DataSource = bsrc;
                bnav.BindingSource = bsrc;
                dgvRowView.Columns[dgvRowView.Columns.Count - 1].AutoSizeMode = 
                  DataGridViewAutoSizeColumnMode.Fill;
 
                // clear input fields; leave the data type the same, probably be text most of the time
                txtColumnName.Text = string.Empty;
                txtColWidth.Text = string.Empty;
            }
        }
 
         /// <summary>

        /// Set the delimiter type upon selection from an option in the 
        /// delimiter type combo box - starts a new configuration
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void cboDelimType_SelectedIndexChanged(object sender, EventArgs e)
        {
            // if no schema exists, create on to set the delimiter type to the default
            if(sdef == null)
                sdef = new SchemeDef();

           // if clear is called, clear the headers checkbox and return
            if (cboDelimType.SelectedItem == null)
            {
                chkHeaders.Checked = false;
                return;
            }

            // set the delimiter type from the drop down
            switch (cboDelimType.SelectedItem.ToString())
            {
                case "Csv Delimited":
                    txtCustomDelimiter.Enabled = false;
                    txtCustomDelimiter.Text = string.Empty; 
                    sdef.DelimiterType = SchemeDef.DelimType.CsvDelimited;
                    break;
                case "Tab Delimited":
                    txtCustomDelimiter.Enabled = false;
                    txtCustomDelimiter.Text = string.Empty; 
                    sdef.DelimiterType = SchemeDef.DelimType.TabDelimited;
                    break;
                case "Custom Delimited":
                    sdef.DelimiterType = SchemeDef.DelimType.CustomDelimited;
                    txtCustomDelimiter.Enabled = true;
                    break;
                case "Fixed Width":
                    txtCustomDelimiter.Enabled = false;
                    txtCustomDelimiter.Text = string.Empty; 
                    sdef.DelimiterType = SchemeDef.DelimType.FixedWidth;
                    break;
                default:
                    break;
            }
        } 

        /// <summary>
        /// Add a row to the schema definition, each row added defines
        /// a schema column – we need a column number, name, data type, and optional column width
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnAddRow_Click(object sender, EventArgs e)
        {
            // create a new row item specifiation
            ItemSpecification itm = new ItemSpecification();
            int rows = dgvRowView.Rows.Count;
 
            // set row number
            itm.ColumnNumber = rows + 1;
 
            // set the column name
            if (!String.IsNullOrEmpty(txtColumnName.Text))
                itm.Name = txtColumnName.Text;
            else
            {
                MessageBox.Show("Specify a column name to continue", 
                  "Invalid Column Name");
                return;
            }
 
            // set the column data type
            if (cboDataType.SelectedItem != null)
                itm.TypeData = 
                 (ItemSpecification.JetDataType)Enum.Parse(typeof(
                  ItemSpecification.JetDataType), 
                 cboDataType.SelectedItem.ToString());
            else
            {
                MessageBox.Show("Specify a data type to continue", 
                  "Invalid Data Type");
                return;
            }
 
            // set the column width (ignored unless the delimiter type is fixed width)
            if (!String.IsNullOrEmpty(txtColWidth.Text))
            {
                itm.ColumnWidth = Convert.ToInt32(txtColWidth.Text);
            }
            else
            {
                if (sdef.DelimiterType == SchemeDef.DelimType.FixedWidth)
                {
                    MessageBox.Show("A fixed width delimited file requires a 
                          column width", "Missing Column Width");
                    itm = null;
                    return;
                }
            }
 
            // update rows and grid display - each row defines a column in the schema
            sdef.ColumnDefinition.Add(itm);
            dgvRowView.DataSource = null;
 
            bsrc.DataSource = sdef.ColumnDefinition;
            bnav.BindingSource = bsrc;
 
            dgvRowView.DataSource = sdef.ColumnDefinition;
            dgvRowView.Columns[dgvRowView.Columns.Count - 1]
                 .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
 
            // clear input fields; leave the data type the same, probably be text most of the time
            txtColumnName.Text = string.Empty;
            txtColWidth.Text = string.Empty;
        }
 
         /// <summary>
        /// Save the schema definition to the application settings 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnSave_Click(object sender, EventArgs e)
        {
            if (dgvRowView.Rows.Count > 0)
            {
                Properties.Settings.Default.SchemaSpec = sdef;
                Properties.Settings.Default.Save();
 
                MessageBox.Show("The schema model has been saved and is 
                 now available to the application.", "Schema Saved");
                this.Dispose();
            }
            else
            {
                MessageBox.Show("The specification contains no column specifications"
                  , "Invalid Specification");
            }
        }
 
        /// <summary>
        /// The user may clear out any existing schema and start defining one from scratch;
        /// by default the user will be put into edit of the existing schema
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnClearAll_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("If you continue, the existing schema 
                 will be overwritten", "Continue?", 
                 MessageBoxButtons.YesNo, 
                 MessageBoxIcon.Question) == DialogResult.No)
                return;
 
            sdef = new SchemeDef();
            dgvRowView.DataSource = sdef;
            cboDataType.SelectedIndex = -1;
            cboDelimType.SelectedIndex = -1;
            txtColumnName.Text = string.Empty;
            txtColWidth.Text = string.Empty

            txtCustomDelimiter.Text = string.Empty;
            txtCustomDelimiter.Enabled = false;
        }
 
         /// <summary>
        /// Set the custom delimiter character
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void txtCustomDelimiter_TextChanged(object sender, EventArgs e)
        {
            if (sdef != null && !String.IsNullOrEmpty(txtCustomDelimiter.Text))
                sdef.CustomDelimiter = txtCustomDelimiter.Text.Substring(0, 1);
        }
 

        /// <summary>
        /// Set whether or not the first row contains column headers
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void chkHeaders_CheckedChanged(object sender, EventArgs e)
        {
            if (chkHeaders.Checked)
            {
                sdef.UsesHeader = SchemeDef.FirstRowHeader.Yes;
                Properties.Settings.Default.UsesHeader = true;
                Properties.Settings.Default.Save();
            }
            else
            { 
                sdef.UsesHeader = SchemeDef.FirstRowHeader.No;
                Properties.Settings.Default.UsesHeader = false;
                Properties.Settings.Default.Save();
            }
        }
    }
}

Code:  About Box (frmAbout.cs)

The about box code is not important and is not described in this document.

That wraps up the sum of the code needed to drive the CSV Editor application.  With the code provided, it is possible construct a viable CSV editor and to build a schema on the fly that may be used to connect to and interact with a delimited text file, regardless of format.

Summary.

While this article was written to demonstrate an approach to working with delimited text files, you might need to connect to only one particular type of delimited text file; the information contained in this document could be used to build a canned scheme.ini file and a connection string that might be then used to open a delimited text file of a matching schema as much and as often as is necessary.

The download includes examples of each delimited file type (comma delimited, tab delimited, fixed width, and custom character (in this case pipe delimited) with and without headers.  The files are named such that you can figure out the delimiter type and whether or not it includes a header row.  To open any delimited text file, create a schema definition that works with that file type and save it using the schema definition dialog prior to opening it.

Looking at the Schema dialog (Schema Generator) shown in the following figure, start by setting the delimiter type.  If the delimiter is a custom character, the delimiter text box will enable and you can enter the delimiter there.  If the file you are trying to open uses the first row to define the column header, check the box below the delimiter type selection.

You can then begin to add rows to the definition.  For each row, enter a column name and a JET data type (you can use text for all of the fields if you want to or you can set them to the correct type).  If the delimiter type is fixed width; you will also need to set the column width value before adding the column definition to the schema.

Once all of the columns have been added, you can save the schema definition using the Save button at the bottom of the dialog.  After saving, when you return to the editor form, you can open any CSV file that matches the schema definition.  When a file is opened, the editor code will look at the schema definition and create a schema.ini file in the same path as the file to be opened, it will also construct a connection string to enable opening the delimited text file using and OLE DB connection.

Figure 6: Defining a Schema