Search and Replace VBs problem

[glennda]

I have a script which will search and replace for strings in a text file. Problem is i am trying to get it to remove everything from in between " speach marks

for example

"CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx",\\\\server\\share\\username,username@domain.internal

This is the VB script I have

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\exportusershomedir.csv", ForReading)

strText = objFile.ReadAll
objFile.Close
strNewText = Replace(strText, ""*"", "hello")

Set objFile = objFSO.OpenTextFile("C:\exportusershomedir.csv", ForWriting)
objFile.WriteLine strNewText
objFile.Close

Basicly i want to remove everything inside the speach marks and replace it with hello. But it doesn't like the speech marks. The reason i need to do this is the sql import i'm running is getting confused as it is reading each section inside the speach marks as new coloums in the database.

[JHeaton]

Use two sets of speech marks around speech marks to escape them.

So ""*"" would become """*""".

[glennda]

Ok thats cool - although it doesn't seem to like the * varible - is there something else for VBS? as i have never used it before?

[LosOjos]

The Replace function doesn't support wildcards, so you're going to have to hard code it, making heavy use of InStr() I imagine... before you go down that path, what exactly is the problem you're having with your SQL command? Because there may be a much simpler solution than writing a wildcard routine...

[LosOjos]

Just a thought: have you tried escaping your commas before you form the SQL statement?

For instance, try this:

strA="CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx"
strB=Replace(strText, ",", "\,")

That should add a backslash before your commas, which tells the server that the following comma is part of your string and not the start of a new query (i.e. escaping)

Alternatively, if that pasrt of your query is hardcoded, you can just escape it manually

strA="CN=xx\,OU=xx\,OU=xx\,OU=xx\,OU=xx\,OU=xx\,DC=xx\,DC=xx"

[glennda]

The sql table has the headers DN, Homedirectory, username.

When importing the csv because each part of the DN has a comma after it the table gets messed up. so cn goes in the first column, ou in the second etc. So i was trying to replace the contents of the speach marks with something else so that its imported into the table - as its not needed but the CSVDE program writes it and there is no option to remove from the output.

Enless you can think of another way to remove everything inside the speech marks.

Toby

[LosOjos]

Just in case you miss it (we must have both been typing at the same time), try my suggestion above, I think that should solve you're problem, although it will write the data to a column in the table...

If you have to write your own routine for wildcards I'll help you out if you want

[glennda]

I'm not quite sure i know what you mean. Do you mean adding

strA="CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx"
strB=Replace(strText, ",", "\,")

To the sql import script or to the VBS script?

I copied the Import script of somebody else which looks like this

 ## Connect to a local database server (or die) ## 
$dbH = mysql_connect('localhost', 'root', 'spider') or die('Could not connect to MySQL server.<br>' . mysql_error()); 
 
## Select the database to insert to ## 
mysql_select_db('usr_home') or die('Could not select database.<br>' . mysql_error(); 
 
## CSV file to read in ## 
$CSVFile = 'exportusershomedir.csv'; 
 
mysql_query('LOAD DATA LOCAL INFILE "exportusershomedir.csv" INTO TABLE exportusershomedir FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error()); 
 
## Close database connection when finished ## 
mysql_close($dbH); 
  

[LosOjos]

Ok that makes things a bit clearer, it's going to be a combination of the two (the SQL import and the VBS to prepare the file).

Is the "CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC= xx" part always the first column in the CSV? If so, there is an easier way of prepping the file, try this script (untested I'm afraid):

Const ForReading = 1
Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("C:\exportusershomedir.csv", ForReading)

Do Until objFile.AtEndOfStream
    strText = Split(objFile.ReadLine,",")

    for x=1 To Ubound(strText)
        strOut=strOut + strText(x)
    next x

    strOut=strOut + Chr(10)
Loop

objFile.Close

Set objFile = objFSO.OpenTextFile("C:\exportusershomedir.csv", ForWriting)
objFile.Write strOut
objFile.Close

That script should open the file and line by line remove the first column (the one you don't want) then write the result back to the file, ready for your SQL import

[glennda]

It is hitting a compilation error. Expected end of statement

Cheers for the help

Toby

[LosOjos]

Does it give you a line number?

[glennda]

it says (12, 10) which i'm guessing is a line number and charector?

[Arthur]

Basically I want to remove everything inside the speech marks and replace it with hello.

This PowerShell script should do it...

$users = import-csv .\exportusershomedir.csv
$users | foreach-object {
	$_.dn = [regex]::Replace($_.dn, "^(?:(?!hello).)*$",'hello');
}
$users | export-csv .\exportusershomedir_new.csv -notype

Before

DN,HomeDirectory,Username
"CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx",\\server\share\username,username@domain.internal
"CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx",\\server\share\username,username@domain.internal
"CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx",\\server\share\username,username@domain.internal

After

DN,HomeDirectory,Username
hello,\\server\share\username,username@domain.internal
hello,\\server\share\username,username@domain.internal
hello,\\server\share\username,username@domain.internal

[glennda]

Thanks will give it a go tomorrow.

[glennda]

That script works really well cheers arthur

Toby