Solved

Reading and writing to csv file vb.net

Posted on 2006-06-09
17
728 Views
Last Modified: 2008-01-09
Hello experts, I am trying to read and write to a csv file and I am not sure how to do it.  I was able to write to a xls file but I found out the computer that contains the program does not have excel installed so I need to write to a csv instead.  Below is my code for writing and reading to an excel how can i change this to read and write to a csv?

 Sub recordactual(ByVal record As String, ByVal daterecord As Date)
        Dim oExcel As Excel.Application
        Dim oWorkBook As Excel.Workbook
        Dim oWorkSheet As Excel.Worksheet
        Dim j As Integer
        j = 1
        Try
            oExcel = New Excel.Application
            oWorkBook = oExcel.Workbooks.Open("C:\smallcount\smallframe.xls")
            oWorkSheet = oWorkBook.Worksheets().Item(1)
            oWorkSheet.Activate()
            Dim o As Excel.Range = oWorkSheet.Cells.Item(j, 1)
            Dim s As String = o.Cells.Text
            Do While s <> ""
                j += 1
                o = o.Cells.Item(j, 1)
                s = o.Cells.Text
            Loop
            o.Cells(j, 1).Value = record
            o.Cells(j, 2).value = daterecord
            lblmactual.Text = o.Cells.Text
            oWorkBook.Application.DisplayAlerts = False

            oWorkBook.SaveAs("C:\smallcount\smallframe.xls")
            oWorkBook.is(Nothing)
            oWorkBook.Close()
            Try
                Dim currentProcess() As Process = Process.GetProcessesByName("excel")
                Dim proc As Process

                For Each proc In currentProcess
                    proc.CloseMainWindow()
                    proc.Kill()
                Next
            Catch ex As Exception
            End Try
        Catch ex As Exception
        End Try
    End Sub
0
Comment
Question by:tentavarious
  • 9
  • 8
17 Comments
 
LVL 35

Accepted Solution

by:
YZlat earned 250 total points
ID: 16869951
just copy those two functions into your code:

 Public Sub WriteDataToCSV(ByVal path As String, ByVal dt As DataTable)
        Dim delim As String
        Dim sw As StreamWriter

        ' Write out the header row
        delim = ""
        Try
            sw = New StreamWriter(path, False, UnicodeEncoding.Default)
            For Each col As DataColumn In dt.Columns
                sw.Write(delim)
                sw.Write(col.ColumnName)
                delim = ","
            Next
            sw.WriteLine()

            ' write out each data row
            For Each row As DataRow In dt.Rows
                delim = ""
                For Each value As Object In row.ItemArray
                    sw.Write(delim)
                    If TypeOf value Is String Then
                        sw.Write(""""c) ' thats four double quotes and a c
                        sw.Write(value)

                        sw.Write(""""c) ' thats four double quotes and a c
                    Else
                        sw.Write(value)
                    End If
                    delim = ","
                Next
                sw.WriteLine()
            Next
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            sw.Close()
        End Try

    End Sub
   
    Public Function ReadDataFromCSV(ByVal path As String) As DataTable
        Dim fulltext As String
        Dim sr As StreamReader
        Dim arrColumnNames() As String
        Dim arrColumnValues() As String
        Dim arrRows() As String
        Dim i, j, n As Integer
        Dim dt As New DataTable
        Dim row As DataRow
        Try
            '' check that the file exists before opening it
            If (File.Exists(path)) Then

                sr = New StreamReader(path)
                fulltext = sr.ReadToEnd
                arrRows = Split(fulltext, vbCrLf)
                arrColumnNames = Split(arrRows(0), ",")
                ''add columns to a datatable
                For n = 0 To arrColumnNames.Length - 1
                    dt.Columns.Add(New DataColumn(arrColumnNames(n), System.Type.GetType("System.String")))
                Next
                For i = 1 To arrRows.Length - 1
                    arrColumnValues = Split(arrRows(i), ",")
                    row = dt.NewRow
                    For j = 0 To (arrColumnNames.Length - 1)
                        Try
                            If Not IsDBNull(arrColumnValues(j)) Then
                                row(arrColumnNames(j)) = arrColumnValues(j)
                            Else
                                row(arrColumnNames(j)) = ""
                            End If
                        Catch ex As Exception
                            Console.Write("ERROR: " & ex.Message)
                        End Try
                    Next
                    dt.Rows.Add(row)
                Next
            End If
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            sr.Close()
        End Try

        Return dt
    End Function

ReadDataFromCSV takes a full path to the csv file as an argument and returns DataTable object with data from the csv file.

WriteDataToCSV takes inn a full path to the file to be written to as a string(if file does not exist, it is created), and a DataTable object with datat to be written to a file.
Let me know if you have any questions

Elena
0
 

Author Comment

by:tentavarious
ID: 16870478
So basically I need to pass the file path for example: C:\smallcount\smallframe.csv and also I need to create a datatable with the content in which I want to write and pass it that.  Is the only way to write to a csv file is by creating a datatable.  Alls I want to do is pass it two values  a string and a date.  And I only want to read the last string value in the file.  Is there an easier way of writing to it?  
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16870514
what format is your data in? I mean the data that you want to write to csv file?

How many rows of data do you have?
0
 

Author Comment

by:tentavarious
ID: 16870593
Basically just once a day I need to write a value from a label and a time stamp
lblmactual.text and now.  The label will contain a string and it needs to write this record right after the last record. For example of over a 5 day period this is what the file will look like
34, 6/2/2006  8:00 PM
21, 6/3/2006  8:00 PM
10, 6/4/2006  8:00 PM
14, 6/5/2006  8:00 PM
0, 6/6/2006  8:00 PM

And I need to read in the last value of the file once a day also.  So the last value i read in should be 0.
I am doing exactly this in my excel write procedure above.  I loop through the rows until i find a empty field and write the value and I call that procedure once a day.
0
 

Author Comment

by:tentavarious
ID: 16870751
Just missing around I got it to work testing with the word hello
   
Dim dt As DataTable
        Dim drow As DataRow
        dt = New DataTable
        dt = ReadDataFromCSV("c:\smallcount\smallframe.csv")
        drow(0) = "hello2"
        drow(1) = Today
        dt.Rows.Add(drow)
        Me.WriteDataToCSV("c:\smallcount\smallframe.csv", dt)
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16870803
Public Sub WriteDataToCSV(ByVal path As String, ByVal val as Integer, ByVal dtDate as DateTime)
        Dim delim As String
        Dim sw As StreamWriter

        ' Write out the header row
        delim = ""
        Try
            sw = New StreamWriter(path, False, UnicodeEncoding.Default)
           
                sw.Write(delim)
                sw.Write("Value")
                delim = ","
            sw.Write(delim)
                sw.Write("Date")
           
            sw.WriteLine()

           
                    sw.Write(delim)
                   
                    sw.Write(val)
                   
                    delim = ","
               
                sw.WriteLine()
             sw.Write(delim)
                   
                    sw.Write(dtDate)
                   
           
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            sw.Close()
        End Try

you can call it to insert one row of data at a time the following way:\

Dim n as integer
dim myDate as Datetime
n=34
myDate=CDate("6/2/2006  8:00 PM")
WriteDataToCSV("C:\smallcount\smallframe.csv",n,myDate)
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16870881
This function will return the last row of the specified csv file in the form of an array of strings = {'0','6/6/2006  8:00 PM}'

Public Function ReadDataFromCSV(ByVal path As String) As String()
        Dim fulltext As String
        Dim sr As StreamReader
        Dim lastRow as string
        Dim arrRows() As String
      Dim arrResults(1) As string
        Dim i, j, n As Integer
        Dim dt As New DataTable
        Dim row As DataRow
        Try
            '' check that the file exists before opening it
            If (File.Exists(path)) Then

                sr = New StreamReader(path)
                fulltext = sr.ReadToEnd
                arrRows = Split(fulltext, vbCrLf)
                arrColumnNames = Split(arrRows(0), ",")

            ''read the last row in the file
            lastRow = arrRows(arrRows.Length - 1)
            arrResults=Split(lastRow,",")



        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            sr.Close()
        End Try

        Return dt
    End Function
0
 

Author Comment

by:tentavarious
ID: 16870882
Is there anyway i can jump to the next row, because it looks like it writing over the existing row.  Plus its putting both
values in the same column.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 35

Expert Comment

by:YZlat
ID: 16871435
let me test it
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16871444
before I start testing, tell me where exectly do you get the values that you want to write to a file?
0
 

Author Comment

by:tentavarious
ID: 16871533
The number value comes from a label that gets updated throughout the day.  I have a timer monitoring the label and when the time reaches 8 PM whatever the value of that label is write it to the csv file along with the current date.  So the label: lblmactual.text and Now are the values I would write.  I want to make sure that it doesnt write over the existing rows, so I probably need to read in the csv first then add the row and write it all back.   When my program loads it needs to read in the last value in the csv file.  So on form load lblmactual.text = "last row, column Value in csv file"
0
 

Author Comment

by:tentavarious
ID: 16871562
If you look at the code in my first posting I am doing exactly what I need except it is with an excel file.  I check for existing rows and write the value and current date, then I immediately read the value and set it equal to the lblmactual.text label.  I would be done with this, but my boss doesnt feel we need to install excel on the computer.
0
 

Author Comment

by:tentavarious
ID: 16871644
I got it working by using your intial post but the values have quotes around them

I put this in a button event every time I click it I get a new row in the csv file like so
Value      Date
"hello2"   "6/9/2006 12:00:00 AM"
But I dont like the quotes

    Dim dt As DataTable
        dt = New DataTable
        dt = ReadDataFromCSV("c:\smallcount\smallframe.csv")
         Dim drow As DataRow
        drow = dt.NewRow
        Dim i As Integer = dt.Rows.Count()
        dt.Rows.RemoveAt(i - 1)
        drow(0) = "hello2"
        drow(1) = Today
        dt.Rows.Add(drow)
        Me.WriteDataToCSV("c:\smallcount\smallframe.csv", dt)
0
 

Author Comment

by:tentavarious
ID: 16872446
Can you tell me why you need to add the 4 quotes and the c here:
sw.Write(""""c) ' thats four double quotes and a c
                        sw.Write(value)

                        sw.Write(""""c) ' thats four double quotes and a c
                    Else
0
 

Author Comment

by:tentavarious
ID: 16872814
Well i think i got it working the way i need it tell me if you see a problem

'Timer event that checks when to send
Private Sub tcount_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tcount.Tick
        '*******************************************************************************************
        'Timer used to write actual count to text file and reset to 0 at the end of month
        '*******************************************************************************************
        Dim timehold As Date
        Dim dtmtest As Date = Today
        Try
            timehold = Now.ToString("hh:mm tt")
            If timehold >= #8:01:00 PM# And timehold < #8:02:00 PM# Then
                tcount.Stop()
                If dtmtest = Me.GetEndOfCurrentMonth(Today) Then
                    '  writefile("0")
                    doreadwrite("0")
                Else
                    If lblmactual.Text.Substring(0, 1) = "+" Then
                            doreadwrite(Math.Abs(Val(lblmactual.Text))) 'gets rid of plus sign
                    Else
                            doreadwrite(lblmactual.Text)
                    End If
                End If
                runcurrent = True
                lblttarget.Text = 0
                System.Threading.Thread.Sleep(61000)
                readfile()
                tcount.Start()
            End If
        Catch ex As Exception
        End Try
    End Sub

'this procedure calls your read and write  functions and updates the csv file
Sub doreadwrite(ByVal sval As String)
        Dim dt As DataTable
        dt = New DataTable
        dt = ReadDataFromCSV("c:\smallcount\smallframe.csv")
        Dim drow As DataRow
        drow = dt.NewRow
        Dim i As Integer = dt.Rows.Count()
        dt.Rows.RemoveAt(i - 1) 'removes the white space
        drow(0) = sval
        drow(1) = Today
        dt.Rows.Add(drow)
        Me.WriteDataToCSV("c:\smallcount\smallframe.csv", dt)
        End Sub

'I run this procedure when the form loads to make sure lblmactual contains the latest value

 Function readfile() As String
        '*********************************************************************************
        'Reads the monthly actual when program loads from a textfile
        '*********************************************************************************
        Try
            Dim s As String
            Dim dt As DataTable
            dt = New DataTable
            dt = ReadDataFromCSV("c:\smallcount\smallframe.csv")
            Dim i As Integer = dt.Rows.Count - 1
            s = dt.Rows(i).Item(0)
            Do Until s <> ""
                i -= 1
                s = dt.Rows(i).Item(0)
            Loop
            lblmactual.Text = dt.Rows(i).Item(0)
            lblmactual.Text = lblmactual.Text.Replace("""", "")
        Catch ex As Exception

        End Try
    End Function
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16873962
"""" are there because if the value is a string, it get's wrapped in double quotes, and so that double quotes would be read, they need to be wrapped in another set of double quotes. The result vould be "value". But I guess you don't need that part since the values are Integer and Datetime
0
 
LVL 35

Expert Comment

by:YZlat
ID: 16873980
OK, to append data to existing csv file, you need to change an argument of s StreamWriter to True:

Public Sub WriteDataToCSV(ByVal path As String, ByVal val as Integer, ByVal dtDate as DateTime)
        Dim delim As String
        Dim sw As StreamWriter

        ' Write out the header row
        delim = ""
        Try
            sw = New StreamWriter(path, True, UnicodeEncoding.Default)
           
                sw.Write(delim)
                sw.Write("Value")
                delim = ","
          sw.Write(delim)
                sw.Write("Date")
           
            sw.WriteLine()

           
                    sw.Write(delim)
                   
                    sw.Write(val)
                   
                    delim = ","
               
                sw.WriteLine()
           sw.Write(delim)
                   
                    sw.Write(dtDate)
                   
           
        Catch ex As Exception
            Console.Write("ERROR: " & ex.Message)
        Finally
            sw.Close()
        End Try
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now