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,[email protected]

 

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.
' . mysql_error()); 

## Select the database to insert to ## 
mysql_select_db('usr_home') or die('Could not select database.
' . 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.
' . 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,[email protected]
"CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx",\\server\share\username,[email protected]
"CN=xx,OU=xx,OU=xx,OU=xx,OU=xx,OU=xx,DC=xx,DC=xx",\\server\share\username,[email protected]

 

After

DN,HomeDirectory,Username
hello,\\server\share\username,[email protected]
hello,\\server\share\username,[email protected]
hello,\\server\share\username,[email protected]

[glennda]

Thanks will give it a go tomorrow.

[glennda]

That script works really well cheers arthur

 

Toby

[glennda]

Me again!

 

How can i edit that so that it removes all " aswell as i need the file just to import hello rather then "hello" but also i have other fields which are "\\server\share\folder" but also need to be \\server\share\folder. and Also "username@domain" to username@domain

 

Cheers

[LosOjos]

You need to edit your SQL import script to tell it the fields are enclosed by speech marks, just change this line (this is the edited version of the line in the script you posted earlier):

 

mysql_query('LOAD DATA LOCAL INFILE "exportusershomedir.csv" INTO TABLE exportusershomedir FIELDS TERMINATED BY "," ENCLOSED BY '"' LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.
' . mysql_error());  

Edited February 2, 2011 by LosOjos

[glennda]

I tried this but i get an syntax error - unexpected T_CONSTANT_ENCAPSED_STRING

[LosOjos]

Try escaping the ", like this:

 

mysql_query('LOAD DATA LOCAL INFILE "exportusershomedir.csv" INTO TABLE exportusershomedir FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.
' . mysql_error());

[glennda]

Yes that works! Cheers

[Arthur]

How can I edit that so that it removes all " aswell as i need the file just to import hello rather then "hello"

This will remove the double quotes around everything if you still need to do it.

 

$file = ".\exportusershomedir_new.csv"
(gc $file) -replace('"','') | Out-File $file -Force

 

You can either add the two lines above onto the end of the previous PowerShell script or create a new one specifically to remove the quotes.

[glennda]

Cheers I'll bear that in mind as i have another use for the output rather then importing into sql.

 

Cheers

[glennda]

@LosOjos

 

Is there a way to change the import so that it either updates existing or imports new data as currently when the script is run is just adds all 1600 records on top of the records already in the database. So after a week i will have 5 duplicates of each item!

[LosOjos]

@LosOjos

 

Is there a way to change the import so that it either updates existing or imports new data as currently when the script is run is just adds all 1600 records on top of the records already in the database. So after a week i will have 5 duplicates of each item!

 

Yeah that's do-able just edit the same line as earlier to add the 'REPLACE' keyword to the import, like so:

 

mysql_query('LOAD DATA LOCAL INFILE "exportusershomedir.csv" REPLACE INTO TABLE exportusershomedir FIELDS TERMINATED BY "," ENCLOSED BY "\"" LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.
' . mysql_error());

 

EDIT: a little more info

 

The 'REPLACE' keyword tells the database that if it finds a record with a duplicate ID, it should replace it with the new version when importing. If you use the 'IGNORE' keyword instead, the database will skip entries that have the same ID (i.e. retain the old values)

Edited February 2, 2011 by LosOjos

[LosOjos]

Did you have any luck with that? I imagine not, as after I posted that I had a thought: there is no ID in your CSV file is there? In that case, the above method wouldn't work, and you'd have to look at deleting duplicates from the table - there are many different way to do this, the method you choose depends greatly on the size of the table - roughly how many records are you expecting it to hold?