# Vbscript Replacing Line Feeds Only



## Squashman (Apr 4, 2003)

Currently have a Vbscript that exports each worksheet within an Excel Workbook to tab delimited files. That is working fine but I was going to add in some code to open the delimited files to replace the Line Feeds with a space because some of the cells in the workbook have LineFeeds in them.

So currently I am just using this code as a standalone vbscript to test replacing the line feeds with a space.

```
Const ForReading = 1, ForWriting = 2
Dim fs, txt, contents

Set fs = CreateObject("Scripting.FileSystemObject")
Set txt = fs.OpenTextFile(WScript.Arguments(0), ForReading)
contents = txt.ReadAll
txt.Close

contents = Replace(contents, vbLf, " ")

Set txt = fs.OpenTextFile(WScript.Arguments(0), ForWriting)
txt.WriteLine contents
txt.Close
```
Now this does replace all the Line Feeds with a space but because there is also a Carriage Return Line Feed at the end of where the line should really end it also replaces that Line Feed with a space and then wraps that space to the next line.

So if my input is

```
foo1<tab>foo2<LF>bar<crlf>
blah1<tab>blah<LF>Blah<crlf>
```
The output comes out as

```
foo1<tab>foo2 bar<crlf>
 blah1<tab>blah Blah<crlf>
```
But what I need is

```
foo1<tab>foo2 bar<crlf>
blah1<tab>blah Blah<crlf>
```
So basically every line after the first line will always start with a space. Not sure how to get the REPLACE to only work on a Line Feed and not a Carriage Return Line Feed.


----------



## foxidrive (Oct 20, 2012)

I think you could do three search/replace passes.

The first one would replace CRLF with CR
Then replace LF with a space
and finally replace CR with CRLF


----------



## Squashman (Apr 4, 2003)

foxidrive said:


> I think you could do three search/replace passes.
> 
> The first one would replace CRLF with CR
> Then replace LF with a space
> and finally replace CR with CRLF


Of course. Someone just needed to put it right in front of my face. I was thinking too hard. Will put that code in when I get to work tomorrow.

Most of these files are pretty small so I don't think I will have an issue using READALL, but what if I started using this to process Excel Files with the max row limit. Which for exporting out of Excel 2007 is I believe over 2,000,000. I think I might get a memory error. Would I then just use READLINE?


----------



## foxidrive (Oct 20, 2012)

Squashman said:


> what if I started using this to process Excel Files with the max row limit. I think I might get a memory error. Would I then just use READLINE?


Yes, I think so, it'd just be less efficient.


----------

