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
:)
MukeshZ 0 Newbie Poster
KillerOfDN -7 Junior Poster in Training
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 SubTry the above code, hope this helps :)
Leigh
pmardle 0 Newbie Poster
Tried using this, but i get a "Index was outside the bounds of the array." error at the
Ary(placeholder) = sr.ReadLineLine ???
Paul Norris 0 Newbie Poster
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...
AycheKay 0 Light Poster
I know this thread is probably not active anymore but I suggest checking out this helpful post at StackOverflow: http://stackoverflow.com/questions/736629/parse-delimited-csv-in-net
DmanMike 0 Newbie Poster
In insert this:
Redim Preserve ary(placeholder)after Do while...
Should work fine now. I love posting on old threads :)
G_Waddell 131 Posting Whiz in Training
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
Jason_5 0 Newbie Poster
.net CSV Reader
This library can read CSV files, including handling columns that have line feeds in them. It is inexpensive too.
Turtle28787 0 Newbie Poster
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
Arvind_4 0 Newbie Poster
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
JamesCherrill 4,733 Most Valuable Poster Team Colleague Featured Poster
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.
xrjf 213 Posting Whiz
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 xrjf 213 Posting Whiz
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 xrjf 213 Posting Whiz
It's more practical to change the file extension from .csv to .txt and, then, open with Excel.
deceptikon 1,790 Code Sniper Team Colleague Featured Poster
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.