从VBA中的CSV文件加载数据期间，将2个双引号替换为1个双引号 [英] Replace 2 double quotes into 1 double quote during data load from csv file in VBA

问题描述

我将csv文件中的行加载到了数组中（打开dataFilePath作为输入＃1，依此类推……）。加载到数组中的数据用2个双引号引起来。示例：（空字符串）， myText， 75823。问题是代码程序后面无法正确检测到空数组元素或其他数据。我找到了一种方法，该方法如何将字符串开头的2个双引号和字符串结尾的2个双引号替换为1个双引号：

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

在此代码之后，我得到了我所需要的-示例：（空字符串）， myText， 75823，但是也许我在从csv文件加载数据时错过了某些内容（编码或其他内容）。也许更简单的方法是在读取csv文件期间在装入数组字符串的开头和结尾处删除这两个双引号？

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?

推荐答案

此双引号是特定于语言的。如果这些是唯一要避免的字符，并且没有其他奇怪字符，则可以在Excel中遍历单元格的字符并检查它们是否为非标准字符（例如，不是标准字符的一部分）。前128个ASCII字符）：

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.

运行代码后，您将获得一个空单元格和75823。

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

这篇关于从VBA中的CSV文件加载数据期间，将2个双引号替换为1个双引号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！