Remove all Carriage returns in a file 2

[camidon]

I have a file that is a flat text file and should not have any carriage returns in it, however it is loaded with them. Is there anyone that can help me with the logic to loop through this code and remove the carriage returns.

I can do this in Word with a module if I need to. Or if someone knows a good way to do it with Access that would work very well also.

Thank you,

Chris

 

[KenWright]

Don't know Access that well, but will Excel do?

Open it in Excel and use Dave McRitchies great macro TrimAll:-

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

Regards
Ken............

 

[camidon]

I can't do this in excel. The reason is because the information is in a flat text file and has multiple delimiters. The fields in the data are * delimited and the different "loops" or sets of data are ~ delimited. It's a very complex database. I'm trying to parse this data into an Access database to make it easier to read, but my first step is going to be removing all of the carriage returns.

THEN, I have to figure out how to read that data from the text file. I'm sure it's going to be both complicated and fun!

Thanks for the responce, Any more ideas??

Chris

 

[sfvb]

Do you want to remove the carriage return, the line feed or both?
If you remove a CR, LF or CRLF the following two lines:
Line1
Line2
may end up as:
Line1Line2

So you want to be sure that's what you want.

If you really want to remove the carriage returns, you could use Excel, Word or even Access, for that matter, but a VB script would probably be the quickest to write and use. The following opens a file named Myfile.txt and removes all the carriage returns, then saves the file as Myfile2.txt.

It replaces the CR with a space, but you can use whatever you want for replacement character(s).
If you really want to remove the carriage return and not replace it, then change:
thisTxt = Replace(thisTxt, vbCR, " &quot
with
thisTxt = Replace(thisTxt, vbCR, "&quot

I commented out two lines, in case what your really after if the LineFeed, or the CarriageReturn/LineFeed.


Const ForReading = 1, ForWriting = 2, ForAppending = 8

Set fso = CreateObject("Scripting.FileSystemObject&quot
Set ReadFile = fspenTextFile("c:\Myfile.txt", ForReading)
thisTxt = ReadFile.ReadAll
Readfile.close

thisTxt = Replace(thisTxt, vbCR, " &quot
'thisTxt = Replace(thisTxt, vbLF, " &quot
'thisTxt = Replace(thisTxt, vbCRLF, " &quot

set WriteFile = fso.CreateTextFile("c:\Myfile2.txt", ForWriting, True)
WriteFile.Write(thisTxt)
WriteFile.Close
Msgbox "Done removing the carriage returns"


If you haven't used VB script files, just paste the above code into Notepad and save it with a .vbs extention.
Example: CRreplacement.vbs
Then open up Windows Explorer and double-click on CRreplacement.vbs (or whatever you named it).

When the script is finished, a message will pop-up, so that you know something happened.

After you run the script don't forget to refresh the Windows explorer filelist (shortcut is F5), otherwise depending on which flavor of windows you're using you may not see the new file that got created.

 

[camidon]

sfvb, that is EXTACTLY what I needed. I am trying to remove both the CR and the LF so that worked out perfectly. This file will now be a little easier to parse into access tables.

Now all I have to do is figure out how to do that!!! The database fields are asterisk delimited which should be easy to handle but since there can be more than one record for each person, the loops are delimited a different way. So basically, it is kinda like a relational database in a way. There could be 20 main records, but then there could be 3000 records that attatch to each of the 20 main records. This is gunna be tough.....

 

[MaxEd]

In Excel is there anyway to concatenate a bunch of cells but have the items listed under each other in one single cell. I used a concatenating method with the vbCrLF but that leaves the character in the cell. Is there a way to do it so it wouldn't show the line feed character?

-Laughter works miracles.

 

[PHV]

MaxEd, just concatenate with vbLf instead of vbCrLf.

Hope This Help
PH.

 

[MaxEd]

PHV,

Thank you!! It worked out great!

-Laughter works miracles.