How to remove spaces from text lines in Visual Basic script

5 pts.
Tags:
VB script
Visual Basic
Visual Basic Script
I have a script that reads a text file and replaces a character string with a new string. The problem is that the data following the new string has spaces that are causing a problem. For example, here is how my data looks prior to running the script

Joe Manager Bob

Eric Manager Freddy

What I am doing is replacing the word Manager with a ; in order to import into Excel easier, making it delimited text. Problem I run into is the data following the ; which is column 2 in the spreadsheet, has leading spaces, which messes up the sorting capabilities.

I need to delete teh leading spaces so that the output text file looks like this:

Joe ;Bob

Eric ;Freddy

Here is my script so far:

Const ForReading = 1 Const ForWriting = 2

Set objFSO = CreateObject("Scripting.FileSystemObject") Set objFile = objFSO.OpenTextFile("C:Documents and SettingsadminDesktoptest_datamanager.txt", ForReading)

strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, " MANAGER ", " ; ")

Set objFile = objFSO.OpenTextFile("C:Documents and SettingsadminDesktoptest_datamanager.txt", ForWriting) objFile.WriteLine strNewText objFile.Close

ASKED: February 18, 2011  6:26 PM
UPDATED: February 18, 2011  8:30 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Get sed for cygwin and run a simple expression:

sed -r -e ‘s/[[:space:]]+<a-Za-z]+[[:space:]]+/ ;/’ file.txt > newfile.txt

Good luck

Well – no tags in the editor allow proper visualisation of the code on the web-page, so try pressing “Improve This Answer” button to view the full expression !!!

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    Why don't you just remove the blank space after the semicolon in this line ?
    strText = objFile.ReadAll objFile.Close strNewText = Replace(strText, " MANAGER ", " ;")
    65,110 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following