Geek asks and answers

Validating commas and other characters

This is my sample file!

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

After following the instructions here I have

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

As bad data in a Csv file, my task is to check the records by separating each column and check the additional separator, if it is found, remove the separator

I tried it!

    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 check this file and return to the original file!

I am very new to vba script, can anyone help me!

After checking, I need a file to see 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
0
source share
2 answers

Are you saying strings without a value for colX are "bad"? They just seem to have no value. Despite this, you can easily check the value in colX.

Do While Not tsIn.AtEndOfStream

    ' Read and split the line...
    a = Split(tsIn.ReadLine, ",")

    ' Check for a value in "colX"...
    If Len(Trim(a(2))) = 0 Then
        ' Not sure what you want to do here. Replace it with another value?
        a(2) = "0"
    End If

    ' Write the line to another file...
    tsOut.WriteLine Join(a, ",")

Loop
0

" " (. ) RegExp :

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
0

More articles:


All Articles