我需要有关如何删除数据中的空格/emtpy的帮助,而不会损害其他数据的空间。这是我的示例数据。
12345," ","abcde fgh",2017-06-06,09:00,AM," ", US
预期输出:
12345,,"abcde fgh",2017-06-06,09:00,AM,, US
由于" "
应视为null。
我尝试了trim((函数,但它不起作用。我还尝试了以下方式模式,但仍然没有用。
这是我的示例功能。
Private Sub Transform(delimiter As String)
Dim sFullPath As String
Dim strBuff As String
Dim re As RegExp
Dim matches As Object
Dim m As Variant
If delimiter <> "," Then
strBuff = Replace(strBuff, delimiter, ",")
Else
With re
.Pattern = "(?!B""[^""]*)" & delimiter & "(?![^""]*""B)"
.IgnoreCase = False
.Global = True
End With
Set matches = re.Execute(strBuff)
For Each m In matches
strBuff = re.Replace(strBuff, ",")
Next
Set re = Nothing
Set matches = Nothing
End If
End Sub
我认为您在正确的轨道上。尝试将其用于正则表达式。连续的两个双引号是字符串文字中包含单个双引号的方式。有些人更喜欢使用Chr(34)
在字符串中包含双引号。
B(s)(?!(?:[^""]*""[^""]*"")*[^""]*$)
在示例字符串上使用该表达式
12345," ","abcde fgh",2017-06-06,09:00,AM," ", US
产生
12345,"","abcde fgh",2017-06-06,09:00,AM,"", US
示例功能
Private Function Transform(ByVal strLine As String) As String
Dim objRegEx As RegExp
On Error GoTo ErrTransForm
Set objRegEx = New RegExp
With objRegEx
.Pattern = "B(s)(?!(?:[^""]*""[^""]*"")*[^""]*$)"
.IgnoreCase = False
.Global = True
Transform = .Replace(strLine, "")
End With
ExitTransForm:
If Not objRegEx Is Nothing Then
Set objRegEx = Nothing
End If
Exit Function
ErrTransForm:
'error handling code here
GoTo ExitTransForm
End Function
和信用额到期信用。我使用了这个答案,以至于在单语引号之间替换whitespaces作为此处表达的基础。
我会添加一个输出字符串变量,并具有条件语句,说明如果输入不是空的,请将其添加到输出字符串中。例如(VB控制台应用程序格式,并提示用户输入许多输入(:
Dim input As String
Dim output As String
Do
input = console.ReadLine()
If Not input = " " Then
output += input
End If
Loop Until (end condition)
Console.WriteLine(output)
您可以将任何您不希望从输出中删除的输入。
您的CSV文件未正确格式。双引号不应该存在,然后用记事本打开您的CSV,然后用空字符串替换。
之后,您现在有一个可以导入Whitout问题的真实CSV文件。