0

I have to read a CSV (That is opened as Excel) file and store the records in database. What I am doing is first read all lines in to a string array and load data to a DataTable and then push it to database. Some fields have commas in them and since I split fields using the comma itself, this is causing trouble. CSV is what I get from customers and I have no authority over asking them to format it correctly. Any idea how to work around this?

code:

Dim dt As New DataTable
Dim fields() As String
Dim lines() As String = File.ReadAllLines(fileName)

'Create headers
lines(1) = lines(1).Replace(Chr(34), "")
        For Each header As String In lines(1).Split(",")
            dt.Columns.Add(header)
        Next

'Fill data
For i As Integer = 1 To lines.Count() - 1
        fields = lines(i).Split(",")
        dt.Rows.Add(fields)
Next

sample data row:

540,TestName,**$2,136.02**,0.15%,8004310/01

So this breaks in to 6 values where as it should be 5

Joel Coehoorn
  • 380,066
  • 110
  • 546
  • 781
voddy
  • 954
  • 1
  • 11
  • 21
  • 1
    Split()-ing on a the comma is a horrible way to read csv data. You should look for a dedicated CSV parser instead, such as Microsoft.VisualBasic.TextFieldParser, FastCSV, LinqToCSV, etc. – Joel Coehoorn Apr 14 '14 at 01:35
  • Agreed. I will look in to these and try to read CSV efficiently. – voddy Apr 14 '14 at 01:53
  • It worked! I tried it with HasFieldsEnclosedInQuotes = True. – voddy Apr 14 '14 at 04:05

2 Answers2

2

Unfortunately, CSV format is prone to issues like the one you describe.

The typical workaround is enclosing values with another delimiter. For example, you can have double quotes around each value and request the customer to enforce that:

"540","TestName","$2,136.02","0.15%","8004310/01"

From there, you should have a line parser that reads values between double quotes that are also split by commas.

This won't guarantee all issues will go away (for example, what if one of the values has double quotes in between), but it should reduce it when most values are numeric.

Check out this other thread which contains useful links

CSV parser/reader for C#?

Community
  • 1
  • 1
DanielCuadra
  • 957
  • 9
  • 17
0

This is how I sorted it with the tip from Joel Coehoorn. Using TextFieldParser is a much cleaner way. (eventhough it doesnt look like it, in Excel, fields were surrounded with quotes)

Dim parser As TextFieldParser

parser = New TextFieldParser(fileName)
parser.HasFieldsEnclosedInQuotes = True
parser.SetDelimiters(",")

While Not parser.EndOfData
      fields = parser.ReadFields()
      <--Handle data-->
End While
voddy
  • 954
  • 1
  • 11
  • 21