Open CSV into Datagridview

mike Delvotti

Guys,

I'm trying to load a CSV into my datagridview and it nearly works, apart from the following error thrown: Input array is longer than the number of columns in this table.

On this line: dataset.Tables[tablename].Rows.Add(items);

Below is my event code am I missing something else from the above line?

                string delimiter = ",";
                string tablename = "Table";
                DataSet dataset = new DataSet();
                OpenFileDialog openFileDialog1 = new OpenFileDialog();
                openFileDialog1.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
                openFileDialog1.FilterIndex = 1;
                if (openFileDialog1.ShowDialog() == DialogResult.OK)
                    {
                        filename = openFileDialog1.FileName;
                        StreamReader sr = new StreamReader(filename);
                        string csv = File.ReadAllText(openFileDialog1.FileName);
                        dataset.Tables.Add(tablename);
                        dataset.Tables[tablename].Columns.Add("Stock Number");
                        dataset.Tables[tablename].Columns.Add("Order Number");

string allData = sr.ReadToEnd();
string[] rows = allData.Split("\r".ToCharArray());

                        foreach (string r in rows)
                        {
                            string[] items = r.Split(delimiter.ToCharArray());
                            dataset.Tables[tablename].Rows.Add(items);
                        }
                        this.dataGridView1.DataSource = dataset.Tables[0].DefaultView;
                      }
            }

public string filename { get; set; }

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Wim Sturkenboom12y ago
I'm quite sure I've posted this little csv demo content before

record 1,"a,b","c""d","line1
line 2"
record 2,x\y\z,x/y/z,empty ;)

You can copy and paste it into a file with the extension csv and see what Excel (or a dedicated csv editor) makes from it. In those, you will get 2 records with each 4 columns. Now try that with your solution, it will not give the correct result.

That's the reason why I use some code that I have found on the web that can properly read and write csv files. It took a while to find the right one that could do everything correctly. Have a look at http://knab.ws/blog/index.php?/archives/3-CSV-file-parser-and-writer-in-C-Part-1.html (writer) and http://knab.ws/blog/index.php?/archives/10-CSV-file-parser-and-writer-in-C-Part-2.html (reader).

For reading only, you can consider the use of the TextFieldParser class.
0
mike Delvotti12y ago
Ahh I see, thanks Vulpes,

I agree with your last line, as for me I want to manage what colunms get displayed in my app, there's lots of fields I don't need in the CSV so as you mention it's easier for me to just pick the ones I want :-)
0
Vulpes12y ago
That's a good question, Mike.

I wouldn't have thought it would be too difficult to add basic ReadCSV and WriteCSV methods to the DataTable class.

All you'd need to know is what the delimiter is and whether there's a header line or not.

In fact, I've seen several examples on this forum of people using custom methods for this purpose. It's also possible to read CSV files as if they were Excel files.

Personally, I prefer not to do that in my own work so I can maintain flexibility about setting the Database column types etc. but that's just me :)
0
mike Delvotti12y ago
Awsome! - thank you Vulpes that works a treat..

Can I ask a question, how come datasets read xml so easy yet CSV take a bit more coding?

i.e. with xml it's a few lines of code such as Dataset1.readxml?
0
Vulpes12y ago
Try it like this:

char delimiter = ',';
string tablename = "Table";
DataSet dataset = new DataSet();
OpenFileDialog openFileDialog1 = new OpenFileDialog();
openFileDialog1.Filter = "CSV Files (*.csv)|*.csv|All Files (*.*)|*.*";
openFileDialog1.FilterIndex = 1;

if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
filename = openFileDialog1.FileName;
dataset.Tables.Add(tablename);
dataset.Tables[tablename].Columns.Add("Stock Number");
dataset.Tables[tablename].Columns.Add("Order Number");

using (StreamReader sr = new StreamReader(filename))
{
string row;
while((row = sr.ReadLine()) != null)
{
string[] fields = row.Split(delimiter);
if (fields.Length < 2) continue;
dataset.Tables[tablename].Rows.Add(fields[0], fields[1]);
}

this.dataGridView1.DataSource = dataset.Tables[0].DefaultView;
}
}

0