We help IT Professionals succeed at work.

C# -- small DataSet issue

finance_teacher
on
How can I fix the below so the
"CheckViewDedcode" column
populates correctly ?

Steps
 1. run below
 2. works.jpg shows column "CheckViewDedcode" has all values
 3. change attached "DEDUCTIONS.CSV" cell D4 from alpha to number
 4. run below again
 5. fails.jpg shows column "CheckViewDedcode" do NOT have all values

        private DataSet ReadFile(string filePath)
        {
            string connectionString = String.Format(
                @"Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Extended Properties=""Text;HDR=Yes;FMT=Delimited""",
                System.IO.Path.GetDirectoryName(filePath));

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                string command = String.Format(
                    @"SELECT * FROM {0}", System.IO.Path.GetFileName(filePath));
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(command, conn))
                {
                    DataSet ds = new DataSet("Temp");
                    adapter.Fill(ds);

                    // Loop through the data table and change names like "Doe,John" to "John Doe"
                    //foreach (DataRow row in ds.Tables[0].Rows)
                     //   row[0] = Regex.Replace((string)row[0], @"([^,]*),([^,]*)", "$2 $1");

                     return ds;
                }
            }
        }
works.jpg
fails.jpg
DEDUCTIONS.CSV
Comment
Watch Question

Thank you for providing a great and reproducible sample.  I'm sure that there is a rationale behind what's happening, but my first thought was to manually parse the csv and identify the problem..  along that line here is sample program that reproduces the problem and demonstrates a work-around solution (using different loading approach).

Create a windows application in C#, I built this using vs2008 so it would be more globally compatible with whatever version of .net you may be using and then change the code to point the csv file with the modified D cell as you describe.

The CSV parsing code comes from here:
http://knab.ws/blog/index.php?/archives/10-CSV-file-parser-and-writer-in-C-Part-2.html

Notice there are a couple of code adjustments listed in the comment section at the bottom of the page that may be useful depending on what you are doing.


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.Collections;

namespace Deductions
{
    public class Form1 : Form
    {
        private System.Windows.Forms.Button button1;

        private Button button2;

        /// <summary>
        /// Required designer variable.
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        private System.Windows.Forms.DataGridView dataGridView1;
        private DataGridView dataGridView2;

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataSet ds = ReadFile(@"c:\temp\ee\deductions.csv");
            if (ds != null && ds.Tables.Count > 0)
            {
                this.dataGridView1.DataSource = ds.Tables[0];
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string inputPath = @"c:\temp\ee\deductions2.csv";
            TextReader rdr = File.OpenText(inputPath);

            this.dataGridView2.DataSource = CsvParser.Parse(rdr, true );

            //DataSet ds = ReadFile(inputPath);
            //if (ds != null && ds.Tables.Count > 0)
            //{
            //     = ds.Tables[0];
            //}
        }

        private void dataGridView1_DataSourceChanged(object sender, EventArgs e)
        {
            for (int a = 0; a < dataGridView1.Columns.Count; a++)
            {
                DataGridViewColumn dc = dataGridView1.Columns[a];
                if (a == dataGridView1.Columns.Count - 1)
                {
                    dc.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill;
                }
                else
                {
                    dc.AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
                }


            }
        }

        /// <summary>
        /// Clean up any resources being used.
        /// </summary>
        /// <param name="disposing">true if managed resources should be disposed; otherwise, false.</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        /// <summary>
        /// Required method for Designer support - do not modify
        /// the contents of this method with the code editor.
        /// </summary>
        private void InitializeComponent()
        {
            this.dataGridView1 = new System.Windows.Forms.DataGridView();
            this.button1 = new System.Windows.Forms.Button();
            this.button2 = new System.Windows.Forms.Button();
            this.dataGridView2 = new System.Windows.Forms.DataGridView();
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView2)).BeginInit();
            this.SuspendLayout();
            // 
            // dataGridView1
            // 
            this.dataGridView1.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Top | System.Windows.Forms.AnchorStyles.Left)
                        | System.Windows.Forms.AnchorStyles.Right)));
            this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.dataGridView1.Location = new System.Drawing.Point(16, 12);
            this.dataGridView1.Name = "dataGridView1";
            this.dataGridView1.Size = new System.Drawing.Size(529, 181);
            this.dataGridView1.TabIndex = 0;
            this.dataGridView1.DataSourceChanged += new System.EventHandler(this.dataGridView1_DataSourceChanged);
            // 
            // button1
            // 
            this.button1.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)));
            this.button1.BackColor = System.Drawing.Color.LightSalmon;
            this.button1.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.button1.Location = new System.Drawing.Point(16, 408);
            this.button1.Name = "button1";
            this.button1.Size = new System.Drawing.Size(109, 33);
            this.button1.TabIndex = 1;
            this.button1.Text = "Load w/Jet";
            this.button1.UseVisualStyleBackColor = false;
            this.button1.Click += new System.EventHandler(this.button1_Click);
            // 
            // button2
            // 
            this.button2.Anchor = ((System.Windows.Forms.AnchorStyles)((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Right)));
            this.button2.BackColor = System.Drawing.Color.LightGreen;
            this.button2.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.button2.Location = new System.Drawing.Point(436, 408);
            this.button2.Name = "button2";
            this.button2.Size = new System.Drawing.Size(109, 33);
            this.button2.TabIndex = 2;
            this.button2.Text = "Load w/Parser";
            this.button2.UseVisualStyleBackColor = false;
            this.button2.Click += new System.EventHandler(this.button2_Click);
            // 
            // dataGridView2
            // 
            this.dataGridView2.Anchor = ((System.Windows.Forms.AnchorStyles)(((System.Windows.Forms.AnchorStyles.Bottom | System.Windows.Forms.AnchorStyles.Left)
                        | System.Windows.Forms.AnchorStyles.Right)));
            this.dataGridView2.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.dataGridView2.Location = new System.Drawing.Point(16, 199);
            this.dataGridView2.Name = "dataGridView2";
            this.dataGridView2.Size = new System.Drawing.Size(529, 181);
            this.dataGridView2.TabIndex = 3;
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(562, 457);
            this.Controls.Add(this.dataGridView2);
            this.Controls.Add(this.button2);
            this.Controls.Add(this.button1);
            this.Controls.Add(this.dataGridView1);
            this.Name = "Form1";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Form1";
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView2)).EndInit();
            this.ResumeLayout(false);

        }

        private DataSet ReadFile(string filePath)
        {
            string connectionString = String.Format(
                @"Provider=Microsoft.Jet.OleDb.4.0;Data Source={0};Extended Properties=""Text;HDR=Yes;FMT=Delimited""",
                System.IO.Path.GetDirectoryName(filePath));

            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                string command = String.Format(
                    @"SELECT * FROM {0}", System.IO.Path.GetFileName(filePath));
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(command, conn))
                {
                    DataSet ds = new DataSet("Temp");
                    adapter.Fill(ds);

                    // Loop through the data table and change names like "Doe,John" to "John Doe"
                    //foreach (DataRow row in ds.Tables[0].Rows)
                    //   row[0] = Regex.Replace((string)row[0], @"([^,]*),([^,]*)", "$2 $1");

                    return ds;
                }
            }
        }

        #region designer code


        #region Windows Form Designer generated code


        #endregion


        #endregion
    }

    public class CsvParser
    {
        public static DataTable Parse(string data, bool headers)
        {
            return Parse(new StringReader(data), headers);
        }

        public static DataTable Parse(string data)
        {
            return Parse(new StringReader(data));
        }

        public static DataTable Parse(TextReader stream)
        {
            return Parse(stream, false);
        }

        public static DataTable Parse(TextReader stream, bool headers)
        {
            DataTable table = new DataTable();
            CsvStream csv = new CsvStream(stream);
            string[] row = csv.GetNextRow();
            if (row == null)
                return null;
            if (headers)
            {
                foreach (string header in row)
                {
                    if (header != null && header.Length > 0 && !table.Columns.Contains(header))
                        table.Columns.Add(header, typeof(string));
                    else
                        table.Columns.Add(GetNextColumnHeader(table), typeof(string));
                }
                row = csv.GetNextRow();
            }
            while (row != null)
            {
                while (row.Length > table.Columns.Count)
                    table.Columns.Add(GetNextColumnHeader(table), typeof(string));
                table.Rows.Add(row);
                row = csv.GetNextRow();
            }
            return table;
        }

        private static string GetNextColumnHeader(DataTable table)
        {
            int c = 1;
            while (true)
            {
                string h = "Column" + c++;
                if (!table.Columns.Contains(h))
                    return h;
            }
        }

        //The CsvStream class does the actual work - 
        //read the CSV source in one character at a 
        //time and return meaningful chunks of decoded data, 
        //namely data items and rows.

        private class CsvStream
        {
            private TextReader stream;

            public CsvStream(TextReader s)
            {
                stream = s;
            }

            public string[] GetNextRow()
            {
                ArrayList row = new ArrayList();
                while (true)
                {
                    string item = GetNextItem();
                    if (item == null)
                        return row.Count == 0 ? null : (string[])row.ToArray(typeof(string));
                    row.Add(item);
                }
            }

            private bool EOS = false;
            private bool EOL = false;

            private string GetNextItem()
            {
                if (EOL)
                {
                    // previous item was last in line, start new line
                    EOL = false;
                    return null;
                }

                bool quoted = false;
                bool predata = true;
                bool postdata = false;
                StringBuilder item = new StringBuilder();

                while (true)
                {
                    char c = GetNextChar(true);
                    if (EOS)
                        return item.Length > 0 ? item.ToString() : null;

                    if ((postdata || !quoted) && c == ',')
                        // end of item, return
                        return item.ToString();

                    if ((predata || postdata || !quoted) && (c == '\x0A' || c == '\x0D'))
                    {
                        // we are at the end of the line, eat newline characters and exit
                        EOL = true;
                        if (c == '\x0D' && GetNextChar(false) == '\x0A')
                            // new line sequence is 0D0A
                            GetNextChar(true);
                        return item.ToString();
                    }

                    if (predata && c == ' ')
                        // whitespace preceeding data, discard
                        continue;

                    if (predata && c == '"')
                    {
                        // quoted data is starting
                        quoted = true;
                        predata = false;
                        continue;
                    }

                    if (predata)
                    {
                        // data is starting without quotes
                        predata = false;
                        item.Append(c);
                        continue;
                    }

                    if (c == '"' && quoted)
                    {
                        if (GetNextChar(false) == '"')
                            // double quotes within quoted string means add a quote       
                            item.Append(GetNextChar(true));
                        else
                            // end-quote reached
                            postdata = true;
                        continue;
                    }

                    // all cases covered, character must be data
                    item.Append(c);
                }
            }

            private char[] buffer = new char[4096];
            private int pos = 0;
            private int length = 0;

            private char GetNextChar(bool eat)
            {
                if (pos >= length)
                {
                    length = stream.ReadBlock(buffer, 0, buffer.Length);
                    if (length == 0)
                    {
                        EOS = true;
                        return '\0';
                    }
                    pos = 0;
                }
                if (eat)
                    return buffer[pos++];
                else
                    return buffer[pos];
            }
        }
    }
}

Open in new window

The Jet OLEDB support guesses the column data type based on values in the first 8 rows. If half or more of the values are numeric, it fixes the type as numeric and the import ignores any non-numeric data in that column. The accepted answer in the question below was to use a utility called ExcelDataReader
http://www.experts-exchange.com/Programming/Languages/C_Sharp/Q_27462391.html