Hi,
I want to read csv (Comma Separated Values) file content in VB.Net
I want read entire values in file store in the fixed blocks ( say array )
Please guide me for reading the csv file content in VB.Net. Also how to get count of the data
in csv file so that i can create blocks of data at runtime.
Thanks
Mukesh
:)

Recommended Answers

All 14 Replies

Hey Mukesh

Private Sub ReadAllText()

        ' Hold the Parsed Data
        Dim ary(0) As String

        ' Reader to read from the file
        Dim sr As New System.IO.StreamReader("LocalFile.csv" Or "Localfile.txt")

        ' Hold the amount of lines already read in a 'counter-variable' 
        Dim placeholder As Integer = 0

        Do While sr.Peek <> -1 ' Is -1 when no data exists on the next line of the CSV file
            ary(placeholder) = sr.ReadLine
            placeholder += 1
        Loop

        ' NOTE: If you wish to parse the data inside the array,
        ' loop through all elements, use the String.Split() method
        ' delimited by a comma (for CSV).

    End Sub

Try the above code, hope this helps :)

Leigh

Tried using this, but i get a "Index was outside the bounds of the array." error at the

Ary(placeholder) = sr.ReadLine

Line ???

Please note guys, and anybode else who comes here from searching for a solution to the problem of parsing CSV data:

The code posted in this thread does not parse CSV data in any but it's most excruciatingly simple form.

You cannot just split on commas because a) character fields may contain commas, and b) even if they don't character fields will be surrounded by double-quote characters that have to be stripped away.

You also cannot use readline because character fields may contain newline characters.

Finally, you must un-escape the double double-quoting escaping system used for fields that contain double-quote characters.

The absence of a CSV parser in the .Net library is a bit shocking in view of the fact that Microsoft invented the format in the first place, it's such a common requirement, and there seems to be so few solutions out there.

I don't have a solution either. When I find one I'll post a link or the code...

In insert this:

Redim Preserve ary(placeholder)

after Do while...

Should work fine now. I love posting on old threads :)

I know this thread is inactive BUT VB.net has a built in text parser for handling delimited files such as CSV: MSDN: Read CSV File Content

Build a form, add the following to it:

Listbox1
button1

With all due respect, I find Microsoft's VisualBasic streamreader lacking. For a no-holdup-does-not-rely-on-other-plugin-crap I wrote a script that will load a .csv into a two-dimensional array, and for giggles, pump it back onscreen in a listbox, showing separation in the array with a pipe "|' symbol.

The file is read in two stages. The first determines the size of the array that needs to be defined (some may prefer an unbound array, if so, you know what to do about it.) Stage 2 loads the data into the array and dumps it to a listbox. I've tested this on a decent-sized .CSV that was 158 columns x 13224 rows and it worked under a minute. Mileage may vary depending on your hardware.

The script recognizes "," as the delimiter and quotes for strings. It will even ignore commas inside the string and recognize double-quotes in the string. What it doesn't do? It doesn't break down the double-quotes inside of strings.

I wrote this because 99% of us just need to load the d@mned .CSV into an array or listbox in the real world, and care only that IT WORKS.

Technogeeks and programming addicts need not add your two cents worth. If you could a wrote this, you would have done so and I wouldn't be here to show you up.

For everyone else, I only hope this is valuable to you.

Cut and paste the following:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles  Button1.Click
    Dim dx As Long = 0
    Dim dRow As Long = 0
    Dim dColumn As Long = 0
    Dim dTotalRows As Long = 0
    Dim dTotalColumns As Long = 0
    Dim dFileName As String = ""
    Dim dReadLine As String = ""
    Dim dChar As String = ""
    Dim dArray(1, 1) As String
    Dim dStart As Long = 1
    Dim dEnd As Long = 1
    Dim dLen As Long = 0
    Dim dLineLength As Long = 0
    Dim dQuoteCounter As Long = 0
    Dim dAdd2ItemList As String = ""
    dFileName = "<insert file name here>"
    FileOpen(1, dFileName, OpenMode.Input, OpenAccess.Default, OpenShare.Default, -1)
    Do While Not EOF(1)
        dReadLine = LineInput(1)
        dRow = dRow + 1
        dTotalColumns = dColumn
        dColumn = 0
        dLineLength = Len(dReadLine)
        For dx = 1 To dLineLength
            dChar = Mid(dReadLine, dx, 1)
            If dChar = Chr(34) Then
                dQuoteCounter = dQuoteCounter + 1
                If dQuoteCounter = 2 * (Int(dQuoteCounter / 2)) Then
                    dChar = Mid(dReadLine, dx, 2)
                    If dChar = Chr(34) + "," Then
                        dColumn = dColumn + 1
                        dQuoteCounter = -1
                    End If
                End If
            End If
            If dQuoteCounter = 0 And dChar = "," Then
                dColumn = dColumn + 1
            End If
            If dx = dLineLength Then
                dColumn = dColumn + 1
            End If
            If dQuoteCounter = -1 And dChar = "," Then dQuoteCounter = 0
        Next (dx)
    Loop
    dTotalRows = dRow
    ReDim dArray(dTotalRows, dTotalColumns)
    FileClose(1)
    dRow = 0
    FileOpen(1, dFileName, OpenMode.Input, OpenAccess.Default, OpenShare.Default, -1)
    Do While Not EOF(1)
        dAdd2ItemList = ""
        dRow = dRow + 1
        dStart = 1
        dEnd = 1
        dLen = 0
        dColumn = 0
        dReadLine = LineInput(1)
        dLineLength = Len(dReadLine)
        For dx = 1 To dLineLength
            dChar = Mid(dReadLine, dx, 1)
            If dChar = Chr(34) Then
                dQuoteCounter = dQuoteCounter + 1
                If dQuoteCounter = 1 Then dStart = dx + 1
                If dQuoteCounter = 2 * (Int(dQuoteCounter / 2)) Then
                    dChar = Mid(dReadLine, dx, 2)
                    If dChar = Chr(34) + "," Then
                        dEnd = dx
                        dLen = dEnd - dStart
                        dColumn = dColumn + 1
                        dArray(dRow, dColumn) = Mid(dReadLine, dStart, dLen)
                        dAdd2ItemList = dAdd2ItemList + dArray(dRow, dColumn) + "|"
                        dQuoteCounter = -1
                        dStart = dx + 2
                    End If
                End If
            End If
            If dQuoteCounter = 0 And dChar = "," Then
                dEnd = dx
                dLen = (dEnd - dStart)
                dColumn = dColumn + 1
                If dLen < 1 Then
                    dArray(dRow, dColumn) = ""
                    dAdd2ItemList = dAdd2ItemList + dArray(dRow, dColumn) + "|"
                Else
                    dArray(dRow, dColumn) = Mid(dReadLine, dStart, dLen)
                    dAdd2ItemList = dAdd2ItemList + dArray(dRow, dColumn) + "|"
                End If
                dStart = dx + 1
            End If
            If dx = dLineLength Then
                dEnd = dx
                dLen = (dEnd - dStart) + 1
                dColumn = dColumn + 1
                If dLen < 1 Then
                    dArray(dRow, dColumn) = ""
                    dAdd2ItemList = dAdd2ItemList + dArray(dRow, dColumn)
                Else
                    dArray(dRow, dColumn) = Mid(dReadLine, dStart, dLen)
                    dAdd2ItemList = dAdd2ItemList + dArray(dRow, dColumn)
                End If
                dStart = dx + 1
            End If
            If dQuoteCounter = -1 And dChar = "," Then dQuoteCounter = 0
        Next (dx)
        ListBox1.Items.Add(dAdd2ItemList)
        REM dRow = dRow + 1
    Loop

End Sub
Sub readCSVFileVerifyData(ByVal strFileName As String, ByVal value As String)
        Try
            ' Open the file using a stream reader.
            Using sr As New StreamReader(strFileName)
                Dim line As String
                ' Read the stream to a string and write the string to the console.
                'line = sr.ReadToEnd()
                line = sr.ReadLine()
                Dim testArray() As String = Split(line, ",")
                Dim storeArray As New ArrayList
                For Each it In testArray
                    'Console.WriteLine(it)
                    storeArray.Add(it)
                Next

                'If storeArray.Contains(value) Then
                 '   Console.WriteLine("Found")
                'Else
                 '   Console.WriteLine("Not Found")
                'End If
            End Using
        Catch e As Exception
            Console.WriteLine("The file could not be read:")
            Console.WriteLine(e.Message)
        End Try
    End Sub

Well, that's interesting code, but did you think that after 9 years anyone was still waiting for it?
And if you had bothered to read the thread before posting you would know that a CSV file may have quoted strings containing commas. People have already thought about that, discussed it here, and posted code that works (unlike your code).
I'm sure you posted with the best of intentions, but maybe next time you should check the dates, then read the entire thread before asking yourself how your post will add to whatever is already there.

I have tried to allow carriage returns:

Imports System.IO
Imports System.Text
Imports System.Text.RegularExpressions

Public Class CSV

    Dim separator As String
    Private Sub btnLoad_Click(sender As System.Object, e As System.EventArgs) Handles btnLoad.Click
        Try
            btnLoad.Enabled = False
            OpenFileDialog1.Filter = "CSV Files (*.CSV)|*.CSV|All Files (*.*)|*.*"
            OpenFileDialog1.FilterIndex = 1
            Dim r As DialogResult = OpenFileDialog1.ShowDialog
            If r <> Windows.Forms.DialogResult.OK Then
                Exit Try
            End If
            Dim dt As DataTable = parseCSV(OpenFileDialog1.FileName)
            DataGridView1.DataSource = dt
        Catch ex As Exception
        Finally
            btnLoad.Enabled = True
        End Try
    End Sub
    Function parseCSV(filename As String) As DataTable
        Dim dt As DataTable = Nothing
        Dim fs As FileStream = Nothing
        Try
            fs = New FileStream(filename, FileMode.Open)
            Dim sr As New StreamReader(fs)
            Dim sbCSV As New StringBuilder(sr.ReadToEnd)
            fs.Close()
            separator = tbSeparator.Text
            Dim pos As Int32 = 0
            Dim vFields(-1) As String, iv As Int32 = 0
            Dim firsRow() As String = nextRow(pos, sbCSV)
            dt = New DataTable
            For i As Int32 = 0 To firsRow.Length - 1
                dt.Columns.Add(firsRow(i))
            Next
            Do While pos < sbCSV.Length
                dt.Rows.Add(nextRow(pos, sbCSV))
            Loop
        Catch ex As Exception
        End Try
        Return dt
    End Function
    Function nextRow(ByRef pos As Int32, sbcsv As StringBuilder) As String()
        Dim vRet(-1) As String, iv As Int32 = 0
        Try
            Do
                Dim field As String = nextField(pos, sbcsv)
                If field = vbCrLf Then
                    Exit Do
                End If
                ReDim Preserve vRet(iv)
                vRet(iv) = field
                iv += 1
            Loop
        Catch ex As Exception

        End Try
        Return vRet
    End Function
    Function nextField(ByRef pos As Int32, sbcsv As StringBuilder) As String
        Dim sRet As String = ""
        Try
            Select Case sbcsv.Chars(pos)
                Case """"
                    ' find quotes, not escaped quotes:
                    Dim pos2 As Int32 = Regex.Match(sbcsv.ToString.Substring(pos + 1), "\""(?!"")").Index
                    If pos2 - pos Then
                        sRet = sbcsv.ToString.Substring(pos + 1, pos2)
                    End If
                    pos += pos2 + 2
                Case vbCr
                    If sbcsv.Chars(pos + 1) = vbLf Then
                        pos += 2
                    Else
                        pos += 1
                    End If
                    sRet = vbCrLf
                Case separator
                    pos += 1
                    Exit Try
                Case Else
                    Dim pos2 As Int32 = InStr(sbcsv.ToString.Substring(pos + 1), separator)
                    sRet = sbcsv.ToString.Substring(pos + 1, pos2)
                    pos += pos2
            End Select
            If pos < sbcsv.Length AndAlso sbcsv.Chars(pos) = separator Then
                pos += 1
            End If
        Catch ex As Exception

        End Try
        Return sRet
    End Function

End Class

Here is a modification because it worked for comma delimited, but not for my contacts.csv semicolon delimited.

Imports System.IO
Imports System.Text
Imports System.Text.RegularExpressions

Public Class CSV

    Dim separator As String
    Dim nErr As Int32
    Private Sub btnLoad_Click(sender As System.Object, e As System.EventArgs) Handles btnLoad.Click
        Try
            btnLoad.Enabled = False
            OpenFileDialog1.Filter = "CSV Files (*.CSV)|*.CSV|All Files (*.*)|*.*"
            OpenFileDialog1.FilterIndex = 1
            Dim r As DialogResult = OpenFileDialog1.ShowDialog
            If r <> Windows.Forms.DialogResult.OK Then
                Exit Try
            End If
            nErr = 0
            Dim ts As New TimeSpan(Now.Ticks)
            Dim dt As DataTable = parseCSV(OpenFileDialog1.FileName)
            Dim ts2 As New TimeSpan(Now.Ticks - ts.Ticks)
            DataGridView1.DataSource = dt
            MessageBox.Show((ts2.TotalMilliseconds).ToString)
        Catch ex As Exception
        Finally
            btnLoad.Enabled = True
        End Try
    End Sub
    Function parseCSV(filename As String) As DataTable
        Dim dt As DataTable = Nothing
        Dim fs As FileStream = Nothing
        Try
            fs = New FileStream(filename, FileMode.Open)
            Dim sr As New StreamReader(fs)
            Dim sbCSV As New StringBuilder(sr.ReadToEnd)
            fs.Close()
            separator = tbSeparator.Text
            Dim pos As Int32 = 0
            Dim vFields(-1) As String, iv As Int32 = 0
            Dim firsRow() As String = nextRow(pos, sbCSV)
            dt = New DataTable
            For i As Int32 = 0 To firsRow.Length - 1
                dt.Columns.Add(firsRow(i))
            Next
            Do While pos < sbCSV.Length
                dt.Rows.Add(nextRow(pos, sbCSV))
            Loop
        Catch ex As Exception
            If nErr <> 5 Then
                MessageBox.Show(ex.Message)
            End If
        End Try
        Return dt
    End Function
    Function nextRow(ByRef pos As Int32, sbcsv As StringBuilder) As String()
        Dim vRet(-1) As String, iv As Int32 = 0
        Try
            Do
                Dim field As String = nextField(pos, sbcsv)
                If field = vbCrLf Then
                    Exit Do
                End If
                ReDim Preserve vRet(iv)
                vRet(iv) = field
                iv += 1
            Loop
        Catch ex As Exception
            Throw ex
        End Try
        Return vRet
    End Function
    Function nextField(ByRef pos As Int32, sbcsv As StringBuilder) As String
        Dim sRet As String = ""
        Try
            Select Case sbcsv.Chars(pos)
                Case """"
                    ' find quotes, not escaped quotes: '
                    Dim pos2 As Int32 = Regex.Match(sbcsv.ToString.Substring(pos + 1), "\""(?!"")").Index
                    If pos2 - pos Then
                        sRet = sbcsv.ToString.Substring(pos + 1, pos2)
                    End If
                    pos += pos2 + 2
                Case vbCr
                    If pos + 1 < sbcsv.Length AndAlso _
                    sbcsv.Chars(pos + 1) = vbLf Then
                        pos += 2
                    Else
                        pos += 1
                    End If
                    sRet = vbCrLf
                Case vbLf
                    pos += 1
                    sRet = vbCrLf
                Case separator
                    pos += 1
                    Exit Try
                Case Else
                    Dim pos2 As Int32 = InStr(sbcsv.ToString.Substring(pos), separator) - 1
                    sRet = sbcsv.ToString.Substring(pos, pos2)
                    pos += pos2
            End Select
            If pos < sbcsv.Length AndAlso sbcsv.Chars(pos) = separator Then
                pos += 1
            End If
        Catch ex As Exception
            nErr += 1
            If nErr = 5 Then
                countAndComparePossibleSeparators(sbcsv)
                Throw ex
            End If
        End Try
        Return sRet
    End Function
    Sub countAndComparePossibleSeparators(sbscv As StringBuilder)
        Try
            Dim vSeparators() As String = {",", ";", "|", ":"}
            Dim max As Int32 = 0
            Dim iMax As Int32 = -1
            For i As Int32 = 0 To vSeparators.Length - 1
                Dim cnt As Int32 = Regex.Matches(sbscv.ToString, "\" + vSeparators(i)).Count
                If cnt > max Then
                    max = cnt : iMax = i
                End If
            Next
            If vSeparators(iMax) <> separator Then
                separator = vSeparators(iMax)
                MessageBox.Show("Possible separator seems to be: " + separator, _
                    "Found errors.", MessageBoxButtons.OK, MessageBoxIcon.Error)
                tbSeparator.Text = separator
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
End Class

It's more practical to change the file extension from .csv to .txt and, then, open with Excel.

I'm surprised that nobody has even mentioned the TextFieldParser class in Microsoft.VisualBasic.FileIO... :P

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.