逗号和其他字符的验证 [英] validation of comma and other characters

问题描述

这是我的示例文件！

col1,col2,colx,col3,col4,col5
    1,A,,AA,X,Y
    2,B,,,*/;wBB,D    --invalid or bad
    3,E,,,....;*()//FF,Y  --invalid or bad
    4,G,,,.,;'()XX,P   --invalid or bad
    5,P,Kk,,...(),D

遵循此处的说明后我有

    2,B,,,BB,D   
    3,E,,,FF,Y 
    4,G,,,XX,P  

作为 Csv 文件中的坏数据，我的任务是通过拆分每一列来验证记录并检查是否有额外的分隔符，如果发现删除分隔符

As bad data in a Csv file my task is to validate records through splitting each column and check for a extra delimiter,if found remove the delimiter

我试过了！

    Sub File validation()

        Dim goFS: Set goFS = CreateObject("Scripting.FileSystemObject")  ' (2)

        Dim tsIn: Set tsIn = goFS.OpenTextFile("....bad.csv")
        Do Until tsIn.AtEndOfStream
         sLine = tsIn.ReadLine()

        If sLine = EOF then exit else Loop ' I get a error here
   Dim str : strconv(sLine) 'error

    End Sub

    Function strConv(ByVal str As String) As String

        Dim objRegEx As Object, allMatches As Object
        Set objRegEx = CreateObject("VBScript.RegExp")

        With objRegEx
            .MultiLine = False
            .IgnoreCase = False
            .Global = True
            .Pattern = ",,,"
        End With

        strConv = objRegEx.Replace(str, ",,")

    End Function

我需要一个有或没有正则表达式的解决方案来验证这个文件并将其放回源文件！

I need a solution with or without Regex to validate this file and put back into source file!

我对 vba 脚本很陌生，谁能帮帮我！

I am very new to to vba scripting can somebody Help me!

验证后我需要文件看起来像这样

After validation I need file to look something like this

  col1,col2,colx,col3,col4,col5
        1,A,,AA,X,Y
        2,B,,BB,D,            
        3,E,,FF,Y,          
        4,G,,XX,P,          
        5,P,Kk,,,D

推荐答案

实验功能"(参见此处)计算将坏行转换为好行的 RegExp:

An 'experimental function' (see here) to work out the RegExp for converting bad to good lines:

Function demoRegExp()
  demoRegExp = 0
  Dim aTests : aTests = Array( _
      "2,B,,,BB,D", "2,B,,BB,D," _
    , "3,E,,,FF,Y", "3,E,,FF,Y," _
    , "field,no comma here,,,what,ever", "field,no comma here,,what,ever," _
  )
  Dim sC : sC = ","
  Dim sF : sF = "[^,]+"
  Dim r  : Set r = New RegExp
  r.Pattern = Join(Array("^(", sF, sC, sF, sC, sC, ")(", sC, ")(", sF, sC, sF, ")$"), "")
  WScript.Echo "pattern:", qq(r.Pattern)
  Dim i
  For i = 0 To UBound(aTests) Step 2
      Dim sInp : sInp = aTests(i + 0)
      Dim sExp : sExp = aTests(i + 1)
      Dim sAct : sAct = r.Replace(sInp, "$1$3$2")
      WScript.Stdout.Write qq(sInp) & " => " & qq(sAct)
      If sAct = sExp Then
         WScript.Echo " ok"
      Else
         WScript.Echo " Fail - exp:", qq(sExp)
      End If
  Next
End Function

输出:

pattern: "^([^,]+,[^,]+,,)(,)([^,]+,[^,]+)$"
"2,B,,,BB,D" => "2,B,,BB,D," ok
"3,E,,,FF,Y" => "3,E,,FF,Y," ok
"field,no comma here,,,what,ever" => "field,no comma here,,what,ever," ok

这篇关于逗号和其他字符的验证的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！