Read CSV/TXT file into DataGridView

[themaj]

Situation: need to read a CSV (txt) file that has no header and has 4 fields (all numeric), the number of lines will vary.

Sample data:
160827,00003,000000075,0000025
84144,00001,000000050,0000050
161060,00002,000000050,0000025
40015,00002,000000100,0000050
61955,00002,000000100,0000050
etc
etc

I want to read this file into a DataGridView for proofing and later insert all these lines into an Access db with 2 additional fields added.

I've been able to access, read/populate the CSV file into the Grid (code below) but I have the following problem(s):
If I set the 4 column headers and other parms of the Grid at design; my sub inserts 4 more columns when it reads/populates the Grid. OR
If I don't set the Headers and Parms, it reads the first line of the CSV into the Grid as Header info.

I tried setting the Gri'ds AutoGenerateColumns=false but that didn't help.

Can anyone point me in the right direction?

Thanks,

`Public Sub GetCsvData()

    Dim csvFileFolder As String = "<filepath>"
    Dim csvFileName As String = "CCPMIX201205.csv"


    Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
        & csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""

    Dim conn As New Odbc.OdbcConnection(connString)

    'Open a data adapter, specifying the file name to load
    Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
    'Then fill a data table, which can be bound to a grid
    Dim dt As New DataTable
    da.Fill(dt)

    DataGridView1.DataSource = dt

    With DataGridView1
    '.AutoGenerateColumns = True
        .ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        .DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
    End With

End Sub``

[poojavb]

Ok let me tell u what I have done....

I have a Datagridview in which I have added 6 columns manually through property window,,,
On button click I have the following code....but before that declare two variables

Dim rowvalue As String
Dim cellvalue(20) As String

then in button click write

Dim streamReader As IO.StreamReader = New IO.StreamReader("D:\abc.csv")
'Reading CSV file content 
While streamReader.Peek() <> -1
      rowValue = streamReader.ReadLine()
      cellvalue = rowvalue.Split(","c) 'check what is ur separator
      DataGridView1.Rows.Add(cellValue)
End While

Hope this is what u needed

[Reverend Jim]

I created a DataGridView and added four columns. I copied your sample input into d:\temp\testdata.txt and ran the following code:

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        For Each line As String In System.IO.File.ReadAllLines("d:\temp\testdata.txt")
            DataGridView1.Rows.Add(line.Split(","))
        Next

    End Sub

End Class

If ColumnHeadersVisible is set to True you get the output in attachment 1. If False you get attachment 2.

[Reverend Jim]

I would very much like to attach the two sample pictures but for some reason I cannot. Trust me. The first picture has columns headers and the second one does not.

[Reverend Jim]

Testing attach of two image files. Well bite me. Now if works. Here's the two files.

[themaj]

Interesting....3 approaches to the same issue. Both the Reverend's and Poojavb's does the trick quite nicely and satisfy the initial requirement of the Grid HeaderRow. They are both slower in processing the 2663 records in my file. But in my real world that wouldn't matter, it's probably 500 ms difference :)

In any case thank you both for your suggestions; I'd like to know more about those 2 strategies, their pros & cons.

If I added the requirement that the data in the CSV file is to be appended to an existing Access db would you both take your same approach? That's going to be my next phase of this project.

I have 3 similar CSV files coming from separate sources (once a month) but all in the exact same format. I need to take those files and introduce to the db while adding 2 more fields; Location & a long integer Date format (ie. 201206). Actually 2 of the fields in the current CSV are obsolete so I'll trash them somewhere along the way.

Would it be better to read the file into a dataset or datatable, populate the Grid and then Append the db from that dataset/table? Love to hear your individual strategies here; I'm all for learning more.

BTW...I'm still interested in figuring out why my approach does not work right; as I said before

If I set the 4 column headers and other parms of the Grid at design; my sub inserts 4 more columns when it reads/populates the Grid. OR
If I don't set the Headers and Parms, it reads the first line of the CSV into the Grid as Header info.

Please tell me more and thanks again

[themaj]

I thought I'd add a screenshot of the results of all solutions so far discussed. You can see the problem of my approach.

It has the Header as desired but no apparent records, but if you note the Horizontal Slider there is more to the right; you can see it peeking around the corner on the right side. it's where my process added columns and used the first row of data as a Header for those columns. Note the row count in the textbox.

Poojavb's looks just fine

The Reverend's result is the same as Poojavb's although I suppressed the ColumnHeader in testing.

Thanks again, love to hear more

[poojavb]

First let me clear myself with few things....
->which database are u using
->how many columns would u have in your database
->how many columns would u have in ur csv file...
->if the column numbers are different....then from where is the other data getting populated or which column will be deducted....

[Reverend Jim]

Most of my database experience has been in real-time processing where getting the data into the database was the most important step. Displaying the data was secondary because of processes downstream that required access to current data. In my case I was dealing with more than 8000 records at a time. My preference would be to insert the records into the database first, then populate the datagridview via a query to fetch the most recent records from the database.

I should add that my database inserts were done, for speed reasons, using the SQL bulk insert capability - something which is not available (as far as I know) in Access. Incidentally, when you ran your timing tests, did you use SuspendLayout and ResumeLayout? Doing a suspend before adding the records, then resume following would speed up the process by stopping the control from updating until all the records have been added.

[themaj]

I appreciate both of your input here since I have very limited experience in this type of db processing. Hopefully someone else is following along and might benefit from this thread as well.

As I explained; I have 3 TXT file for each month, one from each business sector, each file about 700-900 lines of data as described (ie. 160827,00003,000000075,0000025). The last 2 fields are not needed at all so somewhere along the process I plan to strip that data.
I also need to add several pieces of data to each line; one for the month as in YYYYMM, and one for the corresponding business unit (integer value). This all get imported (???) into the db along with a couple dbf files which will have a linked relationship.

I using an Access db for now, but I'm open to suggestions here as well. This is not going to grow into a huge db.

So my thoughts (suppress your laughter here) is to read CSV into a dataset or datatable, then Append the db table while adding the 2 additional static data fields to complete each record. With the relationship established I can now analyze and report the requested data. Eventually, I want to be able to export some of this data as a CSV file that get imported into another system.

Does that make sense at all?

Reverend, regarding the speed of the previous functions tested; it was really just an visual observation. I didn't explicitly time the operation and so far every method was certainly acceptable. I certainly would like to take a look at a code snippet to see/play with that function.

Keep in mind that I've had a 5 year hiatus from VB6 and just starting to get into VB.NET so I'm a little rusty and challenged. I’m very happy to break this down into multiple independent processes to get a good grasp of each. No need for speed here at this stage,

I appreciate every & all suggestions, thanks again