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

double quotes in memo field causing parsing issues

P: 35
Hi,

I am developing a DB that imports a CSV file from a commerce site. The file is a CSV file with double quotes to identify text. In this file, there is a memo field where people occassionally use double quotes. This causes the parsing to corrupt b/c the csv/text file generator is confusing this with the end of the string.

I am aware that using two double quotes is a way to get around this but i am not able to get the csv file to output data this way. is there any other workaround?

thanks

Todd
Jun 19 '09 #1
Share this Question
Share on Google+
11 Replies


ADezii
Expert 5K+
P: 8,679
@tfurubay
How about Low Level I/O Functions to Convert all Double Quotes ("") to Single Quotes (") immediately before Importing the CSV File as in:
Expand|Select|Wrap|Line Numbers
  1. Dim strLine As String
  2. Const conOriginalFile As String = "C:\Employees.txt"
  3. Const conConvertedFile As String = "C:\Employees_2.txt"
  4.  
  5. If Dir$("C:\Employees_2.txt") <> "" Then Kill conConvertedFile
  6.  
  7. Open conOriginalFile For Input As #1
  8. Open "C:\Employees_2.txt" For Output As #2
  9.  
  10. Do While Not EOF(1)             'Loop until end of file.
  11.     Line Input #1, strLine      'Read line into variable.
  12.     Print #2, Replace(strLine, """""", """")    'Convert "" to " then
  13.                                                 'Output to File
  14. Loop
  15.  
  16. Close #1
  17. Close #2
Jun 20 '09 #2

P: 35
Hi,

thank you for your kind assistance. where do i build this function? In a module? do i just paste it in and change the file name?

and will the syntax turn

this:
"joe","smith","I am considered "old" in my family"

to this:
"joe","smith","I am considered 'old' in my family"?

thanks,

Todd
Jun 22 '09 #3

ADezii
Expert 5K+
P: 8,679
@tfurubay
No, it will convert Double Quotes to Single Quotes, which I thought was the original intention.
Expand|Select|Wrap|Line Numbers
  1. "joe",""smith"","I am considered ""old"" in my family"
will be converted to:
Expand|Select|Wrap|Line Numbers
  1. "joe","smith","I am considered "old" in my family"
Jun 22 '09 #4

P: 35
that is the desired outcome. do i build this in a module/macro?

thanks so much!
Jun 22 '09 #5

ADezii
Expert 5K+
P: 8,679
@tfurubay
  1. Copy and Paste the following Sub-Routine Procedure to the General Declarations of a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub ConvertDoubleQuotes(strOriginalFile As String, strConvertedFile As String)
    2. If Dir$(strConvertedFile) <> "" Then Kill strConvertedFile
    3.  
    4. Open strOriginalFile For Input As #1
    5. Open strConvertedFile For Output As #2
    6.  
    7. Do While Not EOF(1)             'Loop until end of file.
    8.     Line Input #1, strLine      'Read line into variable.
    9.     Print #2, Replace(strLine, """""", """")    'Convert "" to " then
    10.                                                 'Output to File
    11. Loop
    12.  
    13. Close #1
    14. Close #2
    15. End Sub
  2. Call the Sub-Routine passing to it the Name/Path of the File to be converted, and the Name Path of the File after Conversion, as in:
    Expand|Select|Wrap|Line Numbers
    1. Call ConvertDoubleQuotes("C:\Test.txt", "C:\Windows\Test_Converted.txt")
Jun 22 '09 #6

P: 35
Hi, I think i'm almost there.

i was able to copy #1 into general declarations. I'm stuck on #2. Where do i paste the syntax? do i paste it in general declarations too? I'm trying to build a macro for it and paste the 2nd command.

Call ConvertDoubleQuotes("\\Easerver\Public\Res DB\ResDownloads\shdownload.csv", "\\Easerver\Public\Res DB\ResDownloads\shdownloadfixed.csv")


sorry, i'm a novice!

thank you,

Todd
Jun 24 '09 #7

ADezii
Expert 5K+
P: 8,679
@tfurubay
I'm stuck on #2. Where do i paste the syntax?
The most logical, and easiest, location to place the Code is in the Click() Event of a Command Button.
Jun 24 '09 #8

100+
P: 675
Am I missing something here?

There are quotes(") within quotes("). Some of the quotes delimit the strings in a CSV file, and some are internal and should either be doubled ("") or changed to single('). tfurubay wants to convert these to single quotes(').

10 Do While Not EOF(1)
11 Line Input #1, strLine .
12 Print #2, Replace(strLine, """""", """")
13
14 Loop
Line 12 needs to have a user function, say DoubleToSingle(strLine) instead of Replace(strLine, """""", """") as shown in line 12 above.

Expand|Select|Wrap|Line Numbers
  1. Public Function DoubleToSingle(strInput As String) As String
  2. 'This function assumes that the CSV separating commas 
  3. '        have no blanks next to them
  4. Dim i As Integer
  5. Dim strWk As String
  6. strWk = Trim(strInput)
  7. For i = 2 To Len(strWk) - 1
  8.     If Mid(strWk, i, 1) = """" Then
  9.         If Mid(strWk, i - 1, 1) <> "," And _
  10.                 Mid(strWk, i + 1, 1) <> "," Then
  11.             Mid(strWk, i, 1) = "'"
  12.         End If
  13.     End If
  14. Next i
  15. DoubleToSingle = strWk
  16. End Function 'DoubleToSingle
Jun 24 '09 #9

P: 35
birdman, i was going to extrapolate and test and substitute because i felt ADezii gave me enough to go on and i would learn in the process. but i thank you profoundly for giving me the exact code that i need.

I'm going to build it this evening. thanks so much for getting me there! And Adezii, thank you for your help and patience!

Todd
Jun 24 '09 #10

100+
P: 675
Glad I could help. But don't just paste code. The idea that
... i would learn in the process.
doesn't mean you can't learn here.
For example, you really don't need a new function because the code will only be used once, in Adezii's creation of a 'Converted' file. My code can simply be inserted at line #12, with a few modifications.
Make all of this code YOURS, and keep learning.

P.S. Are we neighbors? SF = San Francisco?
Jun 24 '09 #11

P: 35
sage advice birdman! thanks i will do as you recommend. yup, im in SF.
Jun 24 '09 #12

Post your reply

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