CSV to a form ??? please help .... [resolved]

[dinosaur_uk]

Hi there,

I have a csv file which i would like to display in a control on my form which behaves like excel spreadsheet.

here is a screenshot of the datatable which i use to put the data on. Is there a more efficient way of doing this ?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim SR As StreamReader = IO.File.OpenText("badger.csv")
        Dim strFileString As String
        Dim al As New ArrayList
        Dim filetext() As String
        Dim i, j As Integer
 
        Dim dt As New DataTable
        Dim dr As DataRow
 
        Do While SR.Peek <> -1
            al.Add(SR.ReadLine)
 
        Loop
 
        filetext = Split(al(0), ",")
        For j = 0 To filetext.Length - 1
            ListBox1.Items.Add(filetext(j))
            dt.Columns.Add(filetext(j))
        Next
 
        For i = 1 To al.Count - 1
            Dim r As DataRow = dt.NewRow
            filetext = Split(al(i), ",")
            For j = 0 To filetext.Length - 1
 
                'ListBox1.Items.Add(filetext(j))
                r(j) = filetext(j)
            Next
            dt.Rows.Add(r)
            '  ListBox1.Items.Add(filetext(3).ToString)
        Next
 
        DataGrid1.DataSource = dt
    End Sub

Any ideas folks ?

[CyberHawke]

Hello, yes, it's a heck of a lot easier than that

Before you begin, you have to realize that Microsoft in it's less than infinite wizdom regocnizes that any structured storage system is a potential data source. Bearing that in mind, realize that a csv file is a structured storage source, and because it is, we can open it with ADO.

I recoded your routine to make use of a few ADO objects and now it works like this:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DataFile As FileInfo = New FileInfo("C:\Temp\badger.csv")
        Dim cnCSV As OdbcConnection
        Dim daCSV As OdbcDataAdapter
        Dim dt As DataTable = New DataTable("badger")
 
        cnCSV = New OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & DataFile.Directory.FullName & ";")
        daCSV = New OdbcDataAdapter("SELECT * FROM [" & DataFile.Name & "]", Conn)
        daCSV.Fill(dt)
        DataGrid1.DataSource = dt
    End Sub

[dinosaur_uk]

What is ADO ? i am so sorry for these silly questions...

is it Advanced Data Objects ?

Cheers ...

Will try it out now

you guys are beautiful people

[dinosaur_uk]

Apparently i need to define the
OdbcConnection
OdbcDataAdapter

etc

how do i define them ?

Sorry for the stupid questions

[CyberHawke]

Howdy,

You need to add an imports statement at the top of your module:

Imports System.Data.Odbc

ADO is ActiveX Data Objects, which isn't appropriately named anymore because the .NET framework isn't based on com or ActiveX.

so it's really ADO.NET, Come on Micro$oft, give it a new name.

[dinosaur_uk]

Now i need to declare conn, what do i need to put for the connection string ?

[dinosaur_uk]

I get this error if i just run it anyway...


An unhandled exception of type 'System.Data.Odbc.OdbcException' occurred in system.data.dll

Additional information: System error.

[CyberHawke]

Hey, do me a favor, let me know if you have an Instant Messaging client, AOL, Yahoo, MSIM?

[dinosaur_uk]

I do have Instant messenger, [email protected] but i cant access it at work ....

[CyberHawke]

If you carefully review my code you will see that every object you need to open the data and connect it to your grid is coded. You only need to change the file location for where your file is located and then it will work. Would you like me to send you an example project?

[dinosaur_uk]

Yes please, that would be amazing !!!!

[CyberHawke]

Actually, as I was reviewing my post, I realized that I left a piece of code in there by mistake. Here is the corrected code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DataFile As FileInfo = New FileInfo("C:\Temp\badger.csv")
        Dim cnCSV As OdbcConnection
        Dim daCSV As OdbcDataAdapter
        Dim dt As DataTable = New DataTable("badger")
 
        cnCSV = New OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & DataFile.Directory.FullName & ";")
        [COLOR=red]daCSV = New OdbcDataAdapter("SELECT * FROM [" & DataFile.Name & "]", cnCSV)[/COLOR]
        daCSV.Fill(dt)
        DataGrid1.DataSource = dt
    End Sub

I'll send you an example project in a few minutes.

[dinosaur_uk]

It works !!!!
Oh yes! !!!

you are a beautiful person !!!!!!

[rybrit514]

I had a quick question cyberhawke, would it be possible to incorporat a WHERE statement into the "OdbcDataAdapter("SELECT * FROM [" & DataFile.Name & "]", cnCSV)" line? I am trying to make a program that will select from live data and I want to be able to give the program a reference to search for. Would that be possible using this method at all? If not how else could I do it. Thanks a bunch for your help!!!!

P.S. I have not written VB in years and I have very limited knowledge sorry.

[si_the_geek]

This thread is quite old, so I dont know if CyberHawke will see it.. you should be able to just add a Where clause after the ] tho, as that is the end of the SQL statement.

[CyberHawke]

An easier route would be to open the dataset as shown, and then build a set of datarows from the table in the opened dataset using the DataTable.Select method.

[gigemboy]

Or you can check out the CSV and TAB file parser codebank submission in my sig

[CyberHawke]

File parsers are great if you want to look at the text in a file, but not very useful if you actually want to use the data in the CSV as data in a table driven system. Depends on the need of your system.

[joseantonio]

Hello CiberHawke
your code runs great, with a few modifications to run it in VB 2005 express, it's very good. The only thing is that I don't get the parsed fields in the datagrid, everything gets in the first (and only) column. Was this code supposed to do the parsing, or do I have to add some code? I'm kind of lost here.

Thanks

[Crash893]

Actually, as I was reviewing my post, I realized that I left a piece of code in there by mistake. Here is the corrected code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DataFile As FileInfo = New FileInfo("C:\Temp\badger.csv")
        Dim cnCSV As OdbcConnection
        Dim daCSV As OdbcDataAdapter
        Dim dt As DataTable = New DataTable("badger")
 
        cnCSV = New OdbcConnection("Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & DataFile.Directory.FullName & ";")
        [COLOR=red]daCSV = New OdbcDataAdapter("SELECT * FROM [" & DataFile.Name & "]", cnCSV)[/COLOR]
        daCSV.Fill(dt)
        DataGrid1.DataSource = dt
    End Sub

I'll send you an example project in a few minutes.

How would i change this so that the first row did not become the collum names

( bouns question) how would make my own collum names if i knew they were static, with out altering the csv file?