Should I use a datatable?

[chris_cs]

Hey Guys,

I have a little scenario I'd like some advice on. Some of our users have to quite often modify some text files which can be fairly cumbersome, so i thought I'd write a little program to make this easier for them.

The structure of the file is the following:

COLUMN1|COLUMN2.........
DATA1|DATA2.......

I thought it would be good if I could get the values of the data row into some kind of table so they could be easily viewed and modified. I thought the best way of doing this would be to loop through the lines of the file, add those rows to a datatable, and then bind the datatable to a datagridview. I managed to bind the column headings to the datatable but I'm struggling with the data rows.

Does this sound like the best way of doing this? If so, what is the best way of looping through the data lines in the file and creating the rows in the datatable?

 

[jmcilhinney]

There's no need for a loop. You can read a CSV file using ADO.NET in essentially the same way you do an Access database. You simply create an OleDbDataAdapter and call Fill. Check out ConnectionStrings.com for the appropriate connection string and the appropriate SQL syntax.

 

[chris_cs]

Thanks for the suggestion. I'll have a go at using this and let you know if I need any further help.

 

[chris_cs]

Ok I've managed to get this working thanks to your suggestion. My code is as follows:

If OpenFileDialog1.ShowDialog(Me) = Windows.Forms.DialogResult.OK Then
            Dim objDataset1 As New DataSet
            Dim fi As New FileInfo(OpenFileDialog1.FileName)
            Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fi.DirectoryName & ";Extended Properties=""Text;HDR=Yes;FMT=Delimited"""
            Dim objConn As New OleDbConnection(sConnectionString)
            objConn.Open()
            Dim objCmdSelect As New OleDbCommand("SELECT * FROM " & fi.Name, objConn)
            Dim objAdapter1 As New OleDbDataAdapter
            objAdapter1.SelectCommand = objCmdSelect
            objAdapter1.Fill(objDataset1, "test")
            DataGridView1.DataSource = objDataset1.Tables(0).DefaultView
            objConn.Close()
        End If

The one issue I'm having now is that there is a line of text before the row which contains the header that I'd lke to skip.

Any ideas how I can do this?

 

[jmcilhinney]

ADO.NET expects a well-formed file. A well-formed file doesn't have a blank line before the column headers. Get rid of that line before importing the the data.

 

[chris_cs]

Looks like I may have an issue then as I'm unable to get rid if that line which isn't blank. I guess I'd have to open the file and delete the line intially, and then restore it?

This doesn't seem like the most efficient way but I guess I may have to.

 

[jmcilhinney]

ADO.NET can only do so much. If you give it a CSV then it expects a valid CSV. A file with something before the column headers is not a valid CSV file. If you want to use ADO.NET then the data needs to be well-formed. If you can't give it well-formed data then you'll have to find another way to read the data. That's not the end of the world but it's more work. You can certainly edit the file in code first, creating a temporary copy if necessary.

 

[chris_cs]

As you say, not the end of the world but a bit if a pain!

Thanks for the help on this!