×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Remove hidden characters using VBScript, from pipe delimited file

Remove hidden characters using VBScript, from pipe delimited file

Remove hidden characters using VBScript, from pipe delimited file

(OP)
Hi, this is my first time posting here and I'm a novice VB script user so please be kind :)

I have a file that is a basic pipe-delimited file. However, within the file there appears to be either Line Feeds or Carriage Returns that cause most software, like MS Access to think that these are additional rows within the file. Thus, creating additional rows within our tables when we import the file. What I'm looking for is a way to remove these and not impacting the way the file is imported. Originally I was going to attempt to change CRLF to CR, then remove LF and then change CR back to CRLF. I have created two VBSCRIPT, one which reads in the entire file and removes the CRLF but it does not remove the hidden character. I have another script that goes line by line and performs a similar function but also, because of the hidden characters, VBSCRIPT thinks that these are separate lines and does not remove anything.

I have attached a sample file. I'm assuming the hidden characters are not CR or LF and therefore will not remove with the script I've been using. But, here is the script.

CODE --> VBSCRIPT

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("sample.txt", ForReading)
strFile = objFile.ReadAll
objFile.close

strFile = replace(strFile, vbCrLf, vbCr)
strFile = replace(strFile, vbLf, " ")
strFile = replace(strFile, vbCr, vbCrLf)

Set objFile = objFSO.OpenTextFile("results.txt", ForWriting)
objFile.Write strFile
objFile.close
WScript.Echo "Finished" 

I'm sure this is a common problem and hope someone out there can assist!

RE: Remove hidden characters using VBScript, from pipe delimited file

Try something like this:

CODE

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("sample.txt", ForReading)
strFile = objFile.ReadAll
objFile.close

strFile = Replace(strFile, vbCrLf, "{CRLF}") 
strFile = Replace(strFile, vbCr, " ") 
strFile = Replace(strFile, vbLf, " ") 
strFile = Replace(strFile, "{CRLF}", vbCrLf)

Set objFile = objFSO.OpenTextFile("results.txt", ForWriting)
objFile.Write strFile
objFile.close
WScript.Echo "Finished" 

RE: Remove hidden characters using VBScript, from pipe delimited file

(OP)
Wow, that was quick. Thank you for looking at and addressing my issue!! Worked perfectly the first time.

RE: Remove hidden characters using VBScript, from pipe delimited file

Your welcome, glad it helped.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close