Regex question

[Guus2005]

I have a problem and i want to use REGEX to fix it.
Now i have two problems, i know.

The solution is simple:
Remove all double quotes not directly preceded or directly followed by a semicolon.

The problem is that i have 3000 csv files which need to be checked and corrected if the following occures:

1;200;345;"Apotheker "Blue tongue"";"Apeldoorn";12;"ABCD12" 
2;232;312;"Cafe "Blue Oyster";"Rotterdam";33;"DCBA21"

When i import those in a table i get an error message on those lines

So i want to fix those files to look like this:

1;200;345;"Apotheker Blue tongue";"Apeldoorn";12;"ABCD12" 
2;232;312;"Cafe Blue Oyster";"Rotterdam";33;"DCBA21"

I hope you can help
Thanks for your time!

 

[Pat Hartman]

Since the records are invalid, you could open the files using the VBA Open method or use FSO. You would read record by record and write each record to the output file.

If this error is rare, you might want to use an exception method. Use TransferText to import each file and log the ones that fail. Then you go back and use the Open or FSO method on the failures.

 

[ebs17]

This seems to me to be unsolvable with RegEx.

One can implement a lookahead (finds double quote if no semicolon follows).
Generally there is also a lookbehind (finds double quote if no semicolon precedes it). But this is not implemented in the VB variant of RegEx.

In this case you would need a combination of both.

My idea would be to delete the double quotes in general and set the import specification accordingly. Or is there a special reason for the text delimiters?

i have 3000 csv files

You can do a loop over all files yourself, can't you?

 

[Josef P.]

Could this work with regex: Remove all " unless they are at the beginning/end of the line or are next to ;

 

[NauticalGent]

@arnelgp is our resident RegEx SME, hopefully he will see this and weigh in. I am certain it can be done, but I would not know how to do it.

This could be done VBA using the InStr() and InStrRev() functions - the lookahead/lookbehind solution Ebs17 mentioned. Have you considered that?

Edit: From your example and simple Replace(strInput, chr(34)&chr(34),chr(34)) should work. That assumes ALL double quotes are wrong.

 

[Josef P.]

My experiment with RegEx:

Private Sub RegExTest()
  
   Dim TestStrings(1 To 2) As String
   Dim i As Long
  
   TestStrings(1) = "1;200;345;""Apotheker ""Blue tongue"""";""Apeldoorn"";12;""ABCD12"""
   TestStrings(2) = "2;232;312;""Cafe ""Blue Oyster"";""Rotterdam"";33;""DCBA21"""
  
   For i = LBound(TestStrings) To UBound(TestStrings)
      Debug.Print i
      Debug.Print "in: "; TestStrings(i)
      Debug.Print "out:"; RemoveQuotesInStrings(TestStrings(i))
   Next

End Sub

Private Function RemoveQuotesInStrings(ByVal InputText As String) As String

   Static RegEx As Object
   Dim Temp As String

   If RegEx Is Nothing Then
      Set RegEx = CreateObject("Vbscript.Regexp")
      RegEx.Pattern = "([^;])(\"")([^;])"
   End If
  
   Temp = Replace(InputText, """""", """")
   Temp = RegEx.Replace(Temp, "$1$3")
      
   RemoveQuotesInStrings = Temp

End Function

 

[NauticalGent]

My experiment with RegEx:

Worked like a charm for me, well done!

 

[jdraw]

Nice! Seems initial issue is to get the input string into an acceptable string format for Access to avoid errors.

 

[Josef P.]

Temp = Replace(InputText, """""", """")
What could the regex pattern look like so that this line with Replace is not necessary?

/edit:
No sooner have you asked a question than you find the answer.

RegEx.Pattern = "([^;])(\"")([^;])"
change to:
RegEx.Pattern = "([^;]+)(\""+)([^;]+)"

Private Function RemoveQuotesInStrings(ByVal InputText As String) As String

   Static RegEx As Object
   Dim Temp As String

   If RegEx Is Nothing Then
      Set RegEx = CreateObject("Vbscript.Regexp")
      RegEx.Global = True
      RegEx.Pattern = "([^;])([""]+)([^;])"
   End If

   Temp = RegEx.Replace(InputText, "$1$3")
  
   RemoveQuotesInStrings = Temp

End Function

/edit 2 ... dont work for
1;200;345;"Apotheker ""Blue tongue"";"Apeldoorn";12;"ABCD12"

/edit 3 .. forgot "Gobal"

 

[NauticalGent]

Temp = RegEx.Replace(InputText, "$1$3")

What exactly does this bit do? I googled it and read about numbered substitutions and all, but was hoping you could be more specific about what it is doing in this case...?

 

[theDBguy]

What exactly does this bit do? I googled it and read about numbered substitutions and all, but was hoping you could be more specific about what it is doing in this case...?

Just a guess, but I read that as saying to replace any ; followed by one or more " with a single ; based on the pattern used.

 

[ebs17]

RegEx.Pattern = "(...)(...)(...)"
                   $1   $2   $3

Subpatterns are defined in the pattern by round brackets.
A part of them is used for the replacement.

 

[NauticalGent]

RegEx.Pattern = "(...)(...)(...)"
                   $1   $2   $3

Subpatterns are defined in the pattern by round brackets.
A part of them is used for the replacement.

Excellent explanation, thank you

 

[Josef P.]

The next improvements:

Private Sub RegExTest()
  
   Dim TestStrings(1 To 2) As String
   Dim i As Long
  
   TestStrings(1) = """A"""";1;200;345;""Apotheker """"Blue tongue"""";""Apeldoorn"";12;""ABCD12"""""
   TestStrings(2) = """""B"";2;232;312;""Cafe ""Blue Oyster"";""Rotterdam"";33;""DCBA21"""
  
   For i = LBound(TestStrings) To UBound(TestStrings)
      Debug.Print i
      Debug.Print "in: "; TestStrings(i)
      Debug.Print "out:"; RemoveQuotesInStrings(TestStrings(i))
      Debug.Print String(30, "-")
   Next
  
   Debug.Print "Multi line:"
   Debug.Print "in:"
   Debug.Print Join(TestStrings, vbNewLine)
   Debug.Print "out:"
   Debug.Print RemoveQuotesInStrings(Join(TestStrings, vbNewLine))
  
End Sub

Private Function RemoveQuotesInStrings(ByVal InputText As String) As String

   Static RegEx As Object
   Dim Temp As String

   'Set RegEx = Nothing  '<-- uncomment for testing (reset static var)
   If RegEx Is Nothing Then
      Set RegEx = CreateObject("Vbscript.Regexp")
      RegEx.Global = True
      RegEx.Pattern = "([^;\n\r])([""]+)([^;\r\n])"
   End If

   Temp = RegEx.Replace(InputText, "$1$3")
     
   RemoveQuotesInStrings = Temp

End Function

output in immediate window:

1
in: "A"";1;200;345;"Apotheker ""Blue tongue"";"Apeldoorn";12;"ABCD12""
out:"A";1;200;345;"Apotheker Blue tongue";"Apeldoorn";12;"ABCD12"
------------------------------
2
in: ""B";2;232;312;"Cafe "Blue Oyster";"Rotterdam";33;"DCBA21"
out:"B";2;232;312;"Cafe Blue Oyster";"Rotterdam";33;"DCBA21"
------------------------------
Multi line:
in:
"A"";1;200;345;"Apotheker ""Blue tongue"";"Apeldoorn";12;"ABCD12""
""B";2;232;312;"Cafe "Blue Oyster";"Rotterdam";33;"DCBA21"
out:
"A";1;200;345;"Apotheker Blue tongue";"Apeldoorn";12;"ABCD12"
"B";2;232;312;"Cafe Blue Oyster";"Rotterdam";33;"DCBA21"

Show Regex match online: https://regex101.com/r/UivEbr/1

 

[Guus2005]

thank you all for your input. I'll try it next time at work!

 

[ebs17]

After Josef has solved the question 1:1 and comprehensively, I would like to remind you of my hint:

My idea would be to delete the double quotes in general and set the import specification accordingly. Or is there a special reason for the text delimiters?

Content = Replace(Content, Chr(34), "")

Such replacement is simpler and less prone to failure.