Skip to content
+1-888-319-3663

COMMUNITY FORUM

Replace single double quote from CSV

Anthony asked 1 year ago
We receive a POP source file (CSV) that includes a description column which sometimes has a single double quote (“) to indicate inches for the item in question.  When SmartConnect encounters the double quote it ignores the rest of the columns for that record resulting in incorrect data being imported.  I need a way to find and replace one double quote.  
So far I have the following code:

dim sItem as string = _ITEMDESCRIPTION
sItem = sItem.Replace(“”””, “in”)
return sItem

Since I only have one double quote in my data this code doesn’t work.  When I remove one of the double quotes from the REPLACE formula (e.g. sItem.Replace(“””, “in”) SmartConnect sees the uneven double quotes and is interpreting everything after the second double quote as text.  I’ve also tried to use CHR(34) in my replace formula but it says I need to declare CHR.  I also tried to do an escape (“\””) but again, it sees everything after the second double quote as text. 
I appreciate any ideas that the community may have.  I’m not a developer btw so forgive any amateur-looking code :).  Thanks.
 
Answers
Patrick Roth Staff answered 1 year ago
When SmartConnect the ODBC Driver encounters the double quote it ignores the rest of the columns
The underlying issue is that your file is formatted wrong.
I’m lazy and so I won’t google the RFC specification for csv files and double quotes, however the jist of it is that double quotes are used to go around fields in CSV files.  They are actually optional (I very seldom see csv files with double quotes) and don’t have to be used.
But since the double quote CAN be used in field data (as yours is), the proper formatting in the source file is double “double quotes”.
So:
12INCHPIPE, 12″ Plastic Pipe, 8.25
would be incorrectly formatted and would cause the ODBC Driver to see the double quote and assume everything inside it is part of one field (so your data is cutoff as you note)
and should be instead:
12INCHPIPE, 12″” Plastic Pipe, 8.25
Your idea to replace a single double quote with “in” (for inch I assume) isn’t so bad.  But the problem is, the data is broken coming in so you can’t fix it in a calculation as you are attempting.  You have to fix it in the source data before the integration even runs.
I don’t have an example of that specifically, but here in this blog:
https://www.eonesolutions.com/tech-tuesday-source-file-name-from-folder-data-source/
I add the file name of my file to the source file.  In your case, you don’t need that.  However you can use the “for each” loop in it to spin through each line.
You should be able to use a .Replace on each line and either use the “escaped” double quote as you were (which works) but I like to use the Chr(34) since less clunky.
mystring = mystring.Replace(Microsoft.VisualBasic.Chr(34), Microsoft.VisualBasic.Chr(34) & Microsoft.VisualBasic.Chr(34))
so in the above, we’d replace one double quote with two double quotes and the file would be ready by ODBC as expected.
 
 
 
 

If you would like to submit an answer or comment, please sign in to the eOne portal.