"",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.
Expand|Select|Wrap|Line Numbers
- 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