By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,115 Members | 898 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,115 IT Pros & Developers. It's quick & easy.

TextFieldParser problem with Double Quotes within Quotes

P: 1
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.
Expand|Select|Wrap|Line Numbers
  1. Using csvFileReader As New TextFieldParser(App_Path() & csvFileFullPath)
  2.  
  3. csvFileReader.TextFieldType = FieldType.Delimited
  4.  
  5. csvFileReader.Delimiters = New String() {","}
  6.  
  7. 'csvFileReader.HasFieldsEnclosedInQuotes = True
  8.  
  9. csvFileReader.HasFieldsEnclosedInQuotes = False
  10.  
  11. csvFileReader.TrimWhiteSpace = True
  12.  
  13. Dim currentRow As String()
  14.  
  15. While Not csvFileReader.EndOfData
  16.  
  17. Try
  18.  
  19. Dim i As Int32 = 1
  20.  
  21. Dim outputRow As New Text.StringBuilder()
  22.  
  23. currentRow = csvFileReader.ReadFields()
  24.  
  25. For Each currentField As String In currentRow
  26.  
  27.     currentField = currentField.Replace(Chr(34), Chr(39)) 'replace double quote with single quote if needed
  28.  
  29.     OutputRow.Append(currentField)
  30.  
  31.     If i < currentRow.Length Then
  32.  
  33.         outputRow.Append(Chr(9)) 'add a tab for each field except last one
  34.  
  35.     End If
  36.  
  37.     i = i + 1
  38.  
  39. Next
  40.  
  41. Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
  42.  
  43.     TextBox1.Text = ex.ToString
  44.  
  45. End Try
  46.  
  47. End While
  48.  
Please can someone help, as I'm sure this type of issue with importing CSV files is a problem for many developers.
Oct 30 '08 #1
Share this Question
Share on Google+
1 Reply


Stang02GT
Expert 100+
P: 1,208
Please make sure you are using code tags.



Moved to Misc.
Oct 30 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.