Ignore the comma inside a row from a csv

Question

So here is my issue.

I am reading a CSV into a datagridview. the first row of the csv is the column headers and the of the file becomes the row. I am reading the file and using as the datasource of the datagridview. However some lines in the CSV have something similar to

name,test,1,2,3,test,2,3,2,1,test,name

Where it is bolded above when I use .Split() it considers it as a new cell in the row however the number of columns is 10 instead of 11 and I get the following error:

Input array is longer than the number of columns in this table

How can I get around this.

Below is my C# Code.

OpenFileDialog openFile = new OpenFileDialog();
        openFile.InitialDirectory = "c:\\";
        openFile.Filter = "txt files (*.txt)|*.txt| CSV Files (*.csv) | *.csv| All Files (*.*) | *.*";
        openFile.FilterIndex = 2;
        openFile.RestoreDirectory = true;
        try {
            if (openFile.ShowDialog() == DialogResult.OK)
            {
                string file = openFile.FileName;
                StreamReader sr = new StreamReader(file);

                /* gets all the lines of the csv */
                string[] str = File.ReadAllLines(file);

                /* creates a data table*/
                DataTable dt = new DataTable();

                /* gets the column headers from the first line*/
                string[] temp = str[0].Split(',');

                /* creates columns of the gridview base on what is stored in the temp array */
                foreach (string t in temp)
                {
                    dt.Columns.Add(t, typeof(string));
                }

                /* retrieves the rows after the first row and adds it to the datatable */
                for (int i = 1; i < str.Length; i++)
                {
                    string[] t = str[i].Split(',');
                    dt.Rows.Add(t);
                }

                /* assigns the data grid view a data source based on what is stored in dt  */
                dataGridView1.DataSource = dt;
            }
        }
        catch (Exception ex) 
        {
                MessageBox.Show("Error: The CSV you selected could not be loaded" + ex.Message);
        }

@SteveWellens That's about comma inside quotes. There don't seem to be any quotes here, unless OP forgot them. — svick, Apr 06 '13 at 00:56

score 1 · Answer 1 · answered Apr 06 '13 at 01:01

This sounds more like a data issue than a programming issue. If your CSV file is supposed to have 10 columns yet some have 11, how are you to know which one is the extra column?

One thing you could do is check for the number of columns before adding the row.

for (int i = 1; i < str.Length; i++)
{
    string[] t = str[i].Split(',');
    if(t.Length == temp.Length)
        dt.Rows.Add(t);
}

Ignore the comma inside a row from a csv

1 Answers1