Using CSV File As Data Reference

Codemonkey85

Okay, so I'm trying to make a program that refers to a table of prices for various items. The problem I am having is being able to easily retrieve data from the CSV file... I can read from it, but I want to store it to an easy-to-use lookup table so that I can pull whatever kind of information I want at any given time. To test this, I tried to make a program that consists only of a listbox, which upon loading of said program would be populated with the names of all of our items. But so far, I cannot figure out how to store this data. I tried using a multidimensional string array, but for some reason my code isn't working. It seems like the program gets to a certain point in my code and stops doing what it's supposed to do, and the functions end and I'm staring at my form. Can anyone help me with this? I would really appreciate it!

Imports System.IO
Imports System.Data.OleDb

Public Class Form1

Dim ITEM\_FILE\_PATH As String = ""
Dim ITEM\_FILE As String = ""
Dim ITEM\_LIST As String(,)
Dim ITEM\_LINE As String()
Dim fs As FileStream
Dim sR As StreamReader
Dim frstComma As Integer = 0
Dim thisLine As String = ""
Dim NumLines As Integer = 0

Private Sub ReadCSVFile()
    ITEM\_FILE\_PATH = Application.StartupPath & "\\All\_Item\_Prices.csv"

    fs = New FileStream(ITEM\_FILE\_PATH, FileMode.Open)
    sR = New StreamReader(fs)

    sR.ReadLine()
    'ITEM\_FILE = sR.ReadToEnd

    While sR.EndOfStream = False

        thisLine = sR.ReadLine
        NumLines += 1
        'MsgBox(thisLine)
        frstComma = InStr(thisLine, ",")
        thisLine = thisLine.Substring(frstComma, thisLine.Length - frstComma)
        frstComma = InStr(thisLine, ",")
        thisLine = thisLine.Substring(frstComma, thisLine.Length - frstComma)
        frstComma = InStr(thisLine, ",")
        thisLine = thisLine.Substring(frstComma, thisLine.Length - frstComma)
        'MsgBox(thisLine)
        Dim COMMAS As String = ",,"
        For i As Integer = 2 To 6
            'MsgBox(COMMAS)
            thisLine = Replace(thisLine, COMMAS, "")
            'MsgBox(thisLine)
            COMMAS &= ","
        Next
        'ITEM\_FILE &= thisLine & vbNewLine
        'For iC As Integer = 0 To thisLine.Split(",").Count
        '    ITEM\_LINE(iC) = thisLine.Split(",")(iC)
        'Next
        MsgBox(thisLine)
        MsgBox(thisLine.Split(",").Cou

Steven J Jowett

I think you need to use a DataTable. Create a DataTable and add columns as necessary, then read your csv file line by line, and for each line create a new row, move your data to each column within the row, then add the row to the DataTable. You can now use a DataView to filter and sort your data, and you can bind the DataTable to controls such as ListBox, DataGrid etc. You can even write the data back to an XML file.

Steve Jowett ------------------------- It is offen dangerous to try and see someone else's point of view, without proper training. Douglas Adams (Mostly Harmless)

Mycroft Holmes

Steven has the right of it - use a datatable and dataview. However if possible store the data as XML then you can read/write it with 1 line: dataset.ReadXML(sfileName) saves all that crap reading in the CSV. Only issue it that it is not human readable but you can always output to CSV if required.

Never underestimate the power of human stupidity RAH

Codemonkey85

So, I followed your advice and tried going with a data table. However, my code is just plain not producing results. I frequently ask my code to msgbox me values here and there to see what's going on, and the last batch of code I posted, as well as this one, seems to ignore these msgbox requests, or the code is somehow not executing. I'm not getting a message box. Below is the sub that I run when the form loads:

Private Sub ReadCSVToDataTable()
'http://vbcity.com/forums/topic.asp?tid=119121

    'Dim dt As New DataTable 'class level

    'in a method
    Dim constr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""

    Dim conn As New OleDbConnection
    conn.ConnectionString = constr
    Dim cmd2 As New OleDbCommand

    cmd2.Connection = conn
    cmd2.CommandText = "Select \* From All\_Item\_Prices.csv"
    Dim da As New OleDbDataAdapter(cmd2)
    Try
        da.Fill(dt)
    Catch ex As OleDbException
        MessageBox.Show(ex.Message)
    End Try

    Dim row0col1 As String = dt.Rows(0).Item(1).ToString

    'In the above code, the following assumptions were made:
    '1) file is located in F:\\ directory
    '2) file name is testA.txt
    '3) the file is a comma delimited file
    '4) the file does not contain a header row

    'MsgBox(dt.Rows(1))


End Sub

Any ideas?

As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836

Mycroft Holmes

Ah sorry you got the wrong handle on the solution I proposed. 1. Read in the CSV file as you are doing now. 2. Move it to a datatable 3. Use the data in your app 4. Write the datatable to an XML file Discard the CSV file Replace 1 with read XML file into the datatable from now on. CSV is great for transporting large loads of data (XML is NOT) but XML is easier to work with.

Never underestimate the power of human stupidity RAH

Codemonkey85

But... I can't move the data to a Datatable. That's my problem. It isn't working. Is there something wrong with my code? I tried doing it that way, and also reading the file line by line as previously suggested. The data stops being read after like, the third column. And I don't know why.

As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836

Mycroft Holmes

Your existing code puts the data into a list of items, can you successfully create a datatable based on the first row from the CSV file? Can you load any data into the table? Is the file a well formed CSV file? Have you stepped through the process using debug to identify the error?

Never underestimate the power of human stupidity RAH

Codemonkey85

I haven't really figured out all of the debugging tools yet, to be honest. I'll have to try the step through thing (when I get time, tomorrow probably). And I can load any data into a datatable, I've done it before. The CSV was an Excel spreadsheet that I exported, so I think it's well-formed. But what could cause the code to just stop executing at a certain point? I have it clearly setup as a For loop to run until 114 or something, and it stops at like, 3.

As I sit here, I contemplate the last words of Socrates: "I drank what?". Pokémon Pearl Friend Code: 4554-2418-6836