Remove hidden characters using VBScript, from pipe delimited file 1

[collector00]

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.

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!

 

[guitarzan]

Try something like this:

Const ForReading = 1
Const ForWriting = 2
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("sample.txt", ForReading)
strFile = objFile.ReadAll
objFile.close
[highlight #FCE94F]
strFile = Replace(strFile, vbCrLf, "{CRLF}") 
strFile = Replace(strFile, vbCr, " ") 
strFile = Replace(strFile, vbLf, " ") 
strFile = Replace(strFile, "{CRLF}", vbCrLf)[/highlight]

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

 

[collector00]

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

 

[guitarzan]

Your welcome, glad it helped.