Reading a .CSV File in VB .Net (2008)
The CSV file specs I followed can be
read into / written from MS-Excel
Note: This code was taken
from VB 6, so it may not be the best .Net code
Public Function ParseLineExcel(ByRef
strLine As String) As String
'the string will be altered
'From
my VB 6 Library
'Excel's
formula SEEMS t be when quotes and commas are all mixed in
'ODD
number of quotes before a comma - the comma is part of the DATA
'EVEN
------------------------------------------------------------------------------
DELIMETER
'ODD
=> open => DATA
'EVEN
= closed =. valid delimeter
Const
ASCIIQUOTE As Long =
34
'(")
Const
ASCIICOMMA As Long = 44
'(,)
Dim
strBuild As String =
""
'Start with an empty
string
Dim
bOpen As Boolean =
False
'0 is an EVEN number for us right
now
Dim
chChar As Char
'Get nothing, give
nothing
If
strLine.Length = 0 Then
ParseLineExcel
= ""
Exit
Function
End If
'String
is just the last delimeter - return nothing!
If
(strLine.Length = 1) And (Asc(strLine) = ASCIIQUOTE Or Asc(strLine) =
ASCIICOMMA) Then
strLine
=
""
'Make sure that the line now
contains NOTHING
ParseLineExcel
=
""
'And gove nothing for just a
delimeter
Exit
Function
End If
'If
we're pass the bogus calls, lets start working!
chChar
= strLine.Substring(0, 1)
Do
While strLine.Length > 0
If
Asc(chChar) = ASCIIQUOTE Then
If
strLine.Length = 1
Then
'Assume last quote
ParseLineExcel
=
strBuild
'Last AQuote just dropped
strLine
=
""
'Empty outthe incoming string
Exit
Function
End
If
bOpen
= Not
bOpen 'Toggle the flag
End
If
If
(Asc(chChar) = ASCIICOMMA) And (Not bOpen)
Then 'Data closed, it's a delimeter
strLine
=
strLine.Substring(1) 'Dump delimiting comma
If
strBuild.Length > 0 Then
If
Asc(Right(strBuild, 1)) = ASCIIQUOTE Then
strBuild
= strBuild.Substring(0, strBuild.Length -
1) 'Return WITHOUT closing quote
End
If
End
If
ParseLineExcel
= strBuild
Exit
Function
End
If
'Drop the opening "
If
Not ((Asc(chChar) = ASCIIQUOTE) And (strBuild.Length = 0)) Then
strBuild
= strBuild + chChar
End
If
strLine
=
strLine.Substring(1) 'Drop first char, string becomes "the
rest"
chChar
= strLine.Substring(0,
1)
'Get the first char
Loop
If
strBuild.Trim.Length = 0 Then
ParseLineExcel
= ""
Else
ParseLineExcel
= strBuild
End If
End Function
Revised Jan 13, 2010