TextFieldParser problem with Double Quotes within Quotes

[Yearwood]

Hi, I'm basically trying to import a CSV into an ACCESS database. Sample date is shown below:

"",10173,"Development Manager - Social Economy Sector","Trust Bank",10153,,"Lolalll Pudd","Meet the requirements of Structured Finance & Relationship Teams for "transaction support" at all times, ensuring appropriate analysis and clarity in respect of required/request","",09-Sep-2008,42000,38000,"+ LTA + bonus",,,,41000,45000,,,"","",,"","","",,,"",,"A", "AUK,AUL,AUN,CBE,RBD"

The CSV data is malformed, as each field requires double quotes enclosed around them. Unfortunately, one of the fields contain double quotes within. So I'm trying to use the TextFieldParser to read the file and replace the double quotes with single quotes for that particular instance.

If I set the HasFieldsEnclosedInQuotes to True, an error is thrown at the line. If I use False the line is read, but the field data is spilt incorrectly.

Using csvFileReader As New TextFieldParser(App_Path() & csvFileFullPath)
 
csvFileReader.TextFieldType = FieldType.Delimited
 
csvFileReader.Delimiters = New String() {","}
 
'csvFileReader.HasFieldsEnclosedInQuotes = True
 
csvFileReader.HasFieldsEnclosedInQuotes = False
 
csvFileReader.TrimWhiteSpace = True
 
Dim currentRow As String()
 
While Not csvFileReader.EndOfData
 
Try
 
Dim i As Int32 = 1
 
Dim outputRow As New Text.StringBuilder()
 
currentRow = csvFileReader.ReadFields()
 
For Each currentField As String In currentRow
 
    currentField = currentField.Replace(Chr(34), Chr(39)) 'replace double quote with single quote if needed
 
    OutputRow.Append(currentField)
 
    If i < currentRow.Length Then
 
        outputRow.Append(Chr(9)) 'add a tab for each field except last one
 
    End If
 
    i = i + 1
 
Next
 
Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
 
    TextBox1.Text = ex.ToString
 
End Try
 
End While
 

Please can someone help, as I'm sure this type of issue with importing CSV files is a problem for many developers.

[Stang02GT]

Please make sure you are using code tags.



Moved to Misc.