473,406 Members | 2,843 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,406 software developers and data experts.

TextFieldParser problem with Double Quotes within Quotes

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
1 4834
Stang02GT
1,208 Expert 1GB
Please make sure you are using code tags.



Moved to Misc.
Oct 30 '08 #2

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

Similar topics

8
by: Johnny Knoxville | last post by:
I've added a favicon to my site (http://lazyape.filetap.com/) which works fine if you add the site to favourites the normal way, but I have some JavaScript code on a couple of pages with a link,...
4
by: Dennis M. Marks | last post by:
I generate a SELECT list dynamically by taking items from a table. I have DOCUMENT.WRITE statements that write a combination of literals and variables including < and > characters. The HTML seems...
7
by: Chris | last post by:
<< Translation in english of my main post >> Hi, I do not success to pass by parameter the contents of "param". in the function "Main" if I replace Suite('+param+') by Suite('+40+') dans la...
3
by: Stefania Scott | last post by:
How do I resolve the problem of passing a string that has quotes within in a SQL statement? Sometimes the string contains a single quote (') and some others it contains the double quote (")? Any...
2
by: sparks | last post by:
ok I was trying to do something real simple I thought Me.Clinic.DefaultValue = Me.Clinic.Value I got error either ! or . misused or not found...something like that I thought ok how can you not...
17
by: Mark | last post by:
I must create a routine that finds tokens in small, arbitrary VB code snippets. For example, it might have to find all occurrences of {Formula} I was thinking that using regular expressions...
3
by: al jones | last post by:
I’m using textfieldparser to read a data file. which contains, for example: Amondó Szegi Amondo Szegi andré nossek André Nossek © Characte Character Note the vowels with diacriticals...
2
by: nargish | last post by:
I am eager to replace double quotations in the output that is being displayed. input text : This is "within quotes" output text: This is <p class="quotes">within quotes</p> I have...
12
by: tom_kuehnert | last post by:
Hi! I'm trying to execute a program using system(). The program itself is located in some path which might contain whitespaces. Simple solution would be this: system("\"C:\A B\C.exe\""); ...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.