Replace 2 double quotes into 1 double quote during data load from csv file in VBA

Question

I loaded line from csv file into array (Open dataFilePath For Input As #1 and so on…). Loaded in array data are in 2 double quotes. Examples: “””” (empty string) , “”myText””, “”75823””. Problem is that later in the code program can’t correctly detect empty array element or other data. I found method how to replace 2 double quotes in the beginning of string and at the end of the string into 1 double quote:

For i = 0 To lastArrayIndex
    thisString = columnsDataInThisLine(i)
    theFirstStringSymbol = Left(thisString, 1)
    If theFirstStringSymbol = """" Then
        'Replace double double quotes in the string beginning and _
        'the end into single double quotes
        thisString = Mid(thisString, 2, Len(thisString) - 2) 
    End If
    columnsDataInThisLine(i) = thisString
Next i

after this code I got what I need - Examples: “” (empty string) , “myText”, “75823”, but maybe I missed something during data load from csv file (encoding or something else). Maybe it is simpler way to remove this 2 double quotes at the beginning and at the end of loaded into array strings during csv file reading?

Answer 1

This double quotes are language specific. If these are the only characters that you would like to avoid and you do not have any other "strange" characters, then you can loop through the characters of your cells in Excel and check whether they are non-standard (e.g. not part of the first 128 ASCII chars):

Public Sub TestMe()

    Dim stringToEdit    As String
    Dim cnt             As Long
    Dim myCell          As Range
    Dim newWord         As String

    For Each myCell In Range("A1:A2")
        newWord = vbNullString
        For cnt = 1 To Len(myCell)
            Debug.Print Asc(Mid(myCell, cnt, 1))
            If Asc(Mid(myCell, cnt, 1)) >= 127 Then
                'do nothing
            Else
                newWord = newWord & Mid(myCell, cnt, 1)
            End If
        Next cnt
        myCell = newWord
    Next myCell

End Sub

Thus, imagine that you have input like this:

It would realize, that the quotes are a bit strange and should not be included in the original text, because they are not part of the first 128 units in the ASCII table.

After running the code you would get an empty cell and 75823.