+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 27
Scripts Thread, Search and Replace VBs problem in Coding and Web Development; I have a script which will search and replace for strings in a text file. Problem is i am trying ...
  1. #1

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351

    Search and Replace VBs problem

    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

    Code:
    "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

    Code:
    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.

  2. #2

    Join Date
    Jun 2010
    Location
    Bury
    Posts
    82
    Thank Post
    9
    Thanked 9 Times in 9 Posts
    Rep Power
    11
    Use two sets of speech marks around speech marks to escape them.

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

  3. #3

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351
    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?

  4. #4

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,664
    Thank Post
    1,484
    Thanked 1,263 Times in 857 Posts
    Rep Power
    803
    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...

  5. #5

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,664
    Thank Post
    1,484
    Thanked 1,263 Times in 857 Posts
    Rep Power
    803
    Just a thought: have you tried escaping your commas before you form the SQL statement?

    For instance, try this:

    Code:
    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

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

  6. #6

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351
    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

  7. #7

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,664
    Thank Post
    1,484
    Thanked 1,263 Times in 857 Posts
    Rep Power
    803
    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

  8. #8

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351
    I'm not quite sure i know what you mean. Do you mean adding
    Code:
    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

    PHP Code:
    ## 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); 

  9. #9

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,664
    Thank Post
    1,484
    Thanked 1,263 Times in 857 Posts
    Rep Power
    803
    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):

    Code:
    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

  10. Thanks to LosOjos from:

    glennda (28th January 2011)

  11. #10

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351
    It is hitting a compilation error. Expected end of statement

    Cheers for the help

    Toby

  12. #11

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,664
    Thank Post
    1,484
    Thanked 1,263 Times in 857 Posts
    Rep Power
    803
    Does it give you a line number?

  13. #12

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351
    it says (12, 10) which i'm guessing is a line number and charector?

  14. #13


    Join Date
    Feb 2007
    Location
    51.403651, -0.515458
    Posts
    9,616
    Thank Post
    250
    Thanked 2,901 Times in 2,134 Posts
    Rep Power
    828
    Quote Originally Posted by glennda View Post
    Basically I want to remove everything inside the speech marks and replace it with hello.
    This PowerShell script should do it...

    Code:
    $users = import-csv .\exportusershomedir.csv
    $users | foreach-object {
    	$_.dn = [regex]::Replace($_.dn, "^(?:(?!hello).)*$",'hello');
    }
    $users | export-csv .\exportusershomedir_new.csv -notype
    Before
    Code:
    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
    Code:
    DN,HomeDirectory,Username
    hello,\\server\share\username,username@domain.internal
    hello,\\server\share\username,username@domain.internal
    hello,\\server\share\username,username@domain.internal

  15. Thanks to Arthur from:

    glennda (1st February 2011)

  16. #14

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351
    Thanks will give it a go tomorrow.

  17. #15

    glennda's Avatar
    Join Date
    Jun 2009
    Location
    Sussex
    Posts
    7,821
    Thank Post
    272
    Thanked 1,140 Times in 1,036 Posts
    Rep Power
    351
    That script works really well cheers arthur

    Toby



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [MS Office - 2003] Strange MSAccess printing problem with .vbs
    By RabbieBurns in forum Office Software
    Replies: 1
    Last Post: 31st January 2009, 08:36 PM
  2. Bat calling VBS problem
    By MK-2 in forum Scripts
    Replies: 5
    Last Post: 16th December 2008, 12:08 AM
  3. Search replace across multiple XML files
    By ajbritton in forum Windows
    Replies: 6
    Last Post: 7th April 2008, 12:07 AM
  4. SIMS Surname search problem
    By FN-GM in forum MIS Systems
    Replies: 5
    Last Post: 21st November 2007, 04:26 PM
  5. Mass search and replace
    By Fletcher_Bravo in forum Windows
    Replies: 2
    Last Post: 5th July 2006, 04:44 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •