VBSCript - Access - Clean Text Escape Regex

1
votes

Am using a vbscript file. -> .vbs extension file. To insert pieces of text into the access database.

Basically need to be able to put whatever characters are possible to be inserted , without throwing much issues.

Using This :

Function CleanUp (input)
     Dim objRegExp, outputStr
     Set objRegExp = New Regexp
     objRegExp.IgnoreCase = True
     objRegExp.Global = True
     objRegExp.Pattern = "[^\w+]"
     outputStr = objRegExp.Replace(input, " ")
     CleanUp = outputStr
End Function

But missing out a lot of special characters , just want be able to have the most commonly used characters like brackets , percentage , dot , comma etc inserted safely.

Can you suggest a better Regex.

Help with Parameter Query : I am using a .vbs file to perform my insert , basically a script file which I execute on my system to populate text files into access .mdb file.

Dim objConn,objRS,strSQL,rsins
Set objConn = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
filenpath = "D:\MDBFILES\"
filenname =  "test.mdb"
objConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ="& filenpath & filenname) 

strSQL = "insert into [mytable] (F1,F2,F3Date,F4,F5Integer,F6Double) values 
('"     &  rdoc  &  "','" &  rtype   & "','" &  CDate(rdate) &  "','"  &      
CleanUp(Trim(arrCells(0))) &  "','"  &  CDbl(arrCells(1)) &  "','"  &    
CDbl(Trim(arrCells(2))) &  "')" 

set rsins = objConn.Execute(strSQL)

This works perfectly for me. The insert statement is within a loop , where the values are updated continuously. Please advise how to create a parameter query and set the parameters with each execution.

Thanks.

2
regexms-accessvbscript
Exactly how are you inserting these characters? Surely a parameter query would allow you a lot more leeway? - Fionnuala
strSQL = "insert into [TABLE] (column) & " CleanUp(Trim(arrCells(0))) & " / forgive syntax - arcotenterprises
Use a parameter query. There is no clean up that is a substitute for a parameter. - Fionnuala
It seems you are getting the data from Excel, you can insert data from Excel into MS Access using a single SQL statement, there is no need to loop through the rows. You can also attach an Excel sheet or range as a table in MS Access. - Fionnuala
yex , the source files are .txt files ( | pipe seperated ). Using vb script , because the number of files is 20 - 40 everyday , all files data goes into a single mdb. This is working weel , as there are a few more checks I do - whether file is empty (with the header only). Please suggest use of parameters in this case , and also you can tell how to use a query to import the entire text file in one go. Thanks again. - arcotenterprises

2 Answers

0
votes

Common practice for sanitizing input is to define a list of valid characters and replace all non-matching characters with a safe character. Space usually are not considered safe. It's better to use underscores instead.

objRegExp.Global  = True
objRegExp.Pattern = "[^a-zA-ZäÄöÖüÜ0-9.,()_-]"
outputStr = objRegExp.Replace(input, "_")
0
votes

Some notes on a parameter query:

Set cmd = CreateObject("ADODB.Command")

cmd.ActiveConnection = con ''A connection
cmd.CommandType = 4 ''adCmdStoredProc =4, A stored query will be used

cmd.CommandText = "TheNameOfThequery"
''adInteger=3, adVarWChar = 202
''Parameters are in the same order in which they occur in the query
cmd.Parameters.Append cmd.CreateParameter("@param1", 3, 1, , param1)
cmd.Parameters.Append cmd.CreateParameter("@param2", 202, 1, 50, param2)

''Action query, so execute
cmd.Execute

Edit re new information

strSQL = "insert into [mytable] (F1,F2,F3Date,F4,F5Integer,F6Double) "
strSQL = strSQL & " Values (@1,@2,@3,@4,@5,@6)"    

Set cmd = CreateObject("ADODB.Command")

cmd.ActiveConnection = objConn
cmd.CommandType = 1 ''adCmdStoredProc =4, adCmdText=1

cmd.CommandText = strSQL
''adInteger=3, adVarWChar = 202, adDate = 7
''Parameters are in the same order in which they occur in the query
cmd.Parameters.Append cmd.CreateParameter("@1", 202, 1, 50, rdoc)
cmd.Parameters.Append cmd.CreateParameter("@2", 202, 1, 50, rtype)
''Not sure about this, because you have quotes on your date, so it may be text
cmd.Parameters.Append cmd.CreateParameter("@3", 7, 1, , CDate(rdate))
cmd.Parameters.Append cmd.CreateParameter("@4", 202, 1, 50, Trim(arrCells(0)))
cmd.Parameters.Append cmd.CreateParameter("@5", 202, 1, 50, Trim(arrCells(1)))
cmd.Parameters.Append cmd.CreateParameter("@6", 202, 1, 50, Trim(arrCells(2)))

''Action query, so execute
cmd.Execute  recs
''msgbox "updated " & recs

You can update text into Access in one statement, but you would need a schema.ini, because you have a non-standard delimiter, for example Handle TransferText Errors

As an aside, I would be inclined to use:

objConn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="& filenpath & filenname)

Or

objConn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="& filenpath & filenname)