473,385 Members | 1,734 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,385 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 4828
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\""); ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
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...

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.