double quotes in memo field causing parsing issues

[tfurubay]

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

[ADezii]

@tfurubay
How about Low Level I/O Functions to Convert all Double Quotes ("") to Single Quotes (") immediately before Importing the CSV File as in:

Dim strLine As String
Const conOriginalFile As String = "C:\Employees.txt"
Const conConvertedFile As String = "C:\Employees_2.txt"
 
If Dir$("C:\Employees_2.txt") <> "" Then Kill conConvertedFile
 
Open conOriginalFile For Input As #1
Open "C:\Employees_2.txt" For Output As #2
 
Do While Not EOF(1)             'Loop until end of file.
    Line Input #1, strLine      'Read line into variable.
    Print #2, Replace(strLine, """""", """")    'Convert "" to " then
                                                'Output to File
Loop
 
Close #1
Close #2

[tfurubay]

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

[ADezii]

@tfurubay
No, it will convert Double Quotes to Single Quotes, which I thought was the original intention.

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

will be converted to:

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

[tfurubay]

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

thanks so much!

[ADezii]

@tfurubay

1. Copy and Paste the following Sub-Routine Procedure to the General Declarations of a Standard Code Module:
   

   Public Sub ConvertDoubleQuotes(strOriginalFile As String, strConvertedFile As String)
   If Dir$(strConvertedFile) <> "" Then Kill strConvertedFile
    
   Open strOriginalFile For Input As #1
   Open strConvertedFile For Output As #2
    
   Do While Not EOF(1)             'Loop until end of file.
       Line Input #1, strLine      'Read line into variable.
       Print #2, Replace(strLine, """""", """")    'Convert "" to " then
                                                   'Output to File
   Loop
    
   Close #1
   Close #2
   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:
   

   Call ConvertDoubleQuotes("C:\Test.txt", "C:\Windows\Test_Converted.txt")

[tfurubay]

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 ConvertDoubleQu otes("\\Easerve r\Public\Res DB\ResDownloads \shdownload.csv ", "\\Easerver\Pub lic\Res DB\ResDownloads \shdownloadfixe d.csv")


sorry, i'm a novice!

thank you,

Todd

[ADezii]

@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.

[OldBirdman]

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.

Public Function DoubleToSingle(strInput As String) As String
'This function assumes that the CSV separating commas 
'        have no blanks next to them
Dim i As Integer
Dim strWk As String
strWk = Trim(strInput)
For i = 2 To Len(strWk) - 1
    If Mid(strWk, i, 1) = """" Then
        If Mid(strWk, i - 1, 1) <> "," And _
                Mid(strWk, i + 1, 1) <> "," Then
            Mid(strWk, i, 1) = "'"
        End If
    End If
Next i
DoubleToSingle = strWk
End Function 'DoubleToSingle

[tfurubay]

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