Can I use a .csv file as a database

[ratcliffe_ic]

I have a .csv file which consists of the days of the year (date format) and associated data in another column. I would like to use this data in a VB project to look at the current data, then find the corresponding date in the csv file and then use the associated data for further functions.

Can I use a .csv file? How would I reference it? Would it be be better to use some other database eg Access?

Any help is much appreciated, thanks.

 

[MattP]

        Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=" & _
            ControlChars.Quote & "Text;HDR=NO;FMT=Delimited" & ControlChars.Quote)
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM C:\Temp\MyDoc.csv", cn)

        Dim da As New OleDbDataAdapter()
        Dim ds As New DataSet()

        cn.Open()

        da.SelectCommand = cmd
        da.Fill(ds)

        cn.Close()

        Me.DataGridView1.DataSource = ds.Tables(0)

 

[MattP]

Upon further testing it appears the HDR=NO portion of the Extended Properties is ignored.

Has anyone else come across this and found a workaround other than opening the file parsing it yourself?

 

[MattP]

I was able to get around the HDR=NO issue by writing a temporary schema.ini file in the same directory as the csv.

If anybody has a fix rather than a workaround I'd love to hear it.

        Dim document As String = "MyDoc.csv"

        Dim sw As New StreamWriter("C:\Temp\schema.ini", False)

        sw.Write("[" & document & "]" & Environment.NewLine & _
            "ColNameHeader = False")
        sw.Close()

        Dim cn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\;Extended Properties=" & _
            ControlChars.Quote & "Text; FMT=Delimited" & ControlChars.Quote)
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM C:\Temp\" & document, cn)

        Dim da As New OleDbDataAdapter()
        da.TableMappings.Add("Table", "File")
        Dim ds As New DataSet()

        cn.Open()

        da.SelectCommand = cmd
        da.Fill(ds)

        cn.Close()

        Me.DataGridView1.DataSource = ds.Tables(0)

        File.Delete("C:\Temp\schema.ini")

 

[ratcliffe_ic]

Could you tell me how I add this code, do I need to add a component to my form for this?

 

[MattP]

Could you tell me how I add this code, do I need to add a component to my form for this?

You'll need to import System.IO to use the StreamWriter and File.Delete and import System.Data.OleDb for the rest.

 

[ratcliffe_ic]

Private Sub button1_click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim fs, f  'define filesystem stuff for read
        Dim CSV_file
        Dim s

        'Reads file one line at a time into array
        fs = CreateObject("Scripting.FileSystemObject")
        f = fs.OpenTextFile("C:\Documents and Settings\Ian\Desktop\AUG08 tides.csv", 1)
        s = f.ReadAll
        f.Close()
        f = Nothing 'clear f and fs
        fs = Nothing

        CSV_file = Split(s, vbCrLf) ' create array with data by date

        TextBox1.Text = "File Read"

        Dim Date_pos As Integer
        Dim value As Object
        value = "20/08/2008"

        Date_pos = (Array.IndexOf(CSV_file, value))
        MsgBox("Date found at position " & Date_pos)

    End Sub

I've had some success with getting my CSV file in, I've got it into an array called CSV_file (the data is there as I've created a MSGBox to display CSV_file(20) for example and the info is there). I'm now searching for my date but it just displays -1 for Date_pos. Must be something simple but I can't see it.