Line feeds being replaced with string

Question

I'm trying to append a character string from a variable to the end of lines in a CSV. However, line feeds are being replaced by the string. So if the original line is:

fieldcharacters1,fieldcharacters string starts on one line

and continue after a line feed,fieldcharacters3

(where the blank line is a line feed)

and I want to append "fieldcharacter4" (without quotes) to the end of the line, it should show up like this:

fieldcharacters1,fieldcharacters string starts on one line

and continue after a line feed,fieldcharacters3,fieldcharacter4

But instead it's ending up like this:

fieldcharacters1,fieldcharacters string starts on one line,fieldcharacter4 ,fieldcharacter4 and continue after a line feed,fieldcharacters3,fieldcharacter4

Here's the code I have. I also tried a Do Until Loop using objCurrentFile.AtEndOfStream, but it didn't work either.

Set tsOut = objFSO.CreateTextFile(strCSV, FOR_WRITING)
strLine = objCurrentFile.ReadAll
objCurrentFile.close
strInsertText = ",testing123123" & chr(013)
WScript.Echo "Text to be appended= " & strInsertText
strNewText = Replace(strLine, Chr(013), strInsertText )
tsOut.Write strNewText

Answer 1

There are three end-of-line markers:

1. Linefeed|vbLf|Chr(10)|/n
2. CarriageReturn|vbCr|chr(13)|/r
3. CarriageReturn+Linefeed|vbCrLf|Chr(13) & Chr(10)|/r/n

When reading from a file, .ReadLine() will remove /n or /r/n, but not /r (see here); .ReadLine(), however, will return all contents of the file.

A simple model of your problem: you have a .CSV containing fields with embedded \r's and \r\n as eol; when 'trying to append a character string from a variable to the end of lines' via Replace, the embedded \r's are affected too:

>> sLine = "field 1,... one line\rand ...field 3\r\n"
>> WScript.Echo Replace(sLine, "\r", "[,append]")
>>
field 1,... one line[,append]and ...field 3[,append]\n

You may get away with Replacing \r\n:

>> WScript.Echo Replace(sLine, "\r\n", "[,append]")
>>
field 1,... one line\rand ...field 3[,append]

and dealing with an eventual replacement of a trailing \r\n in your file. If you can loop over the .CSV lines, append/concatenate (instead of using .Replace) the extra data and the proper eol marker (perhaps via .WriteLine).

The first approach will fail if the eol- and the embedded marker are identical. In that case (and probably in general) using the ADO Text driver to deal with .CSVs is a much better option.

(BTW: read the docs to learn why

objFSO.CreateTextFile(strCSV, FOR_WRITING)

is wrong)

Update wrt comment:

From your comment I conclude that your .CSV use \r\n for eol and embedded (paragraph?) marker. So Replace won't work:

>> sLine = "field 1,... one line\r\nand ...field 3\r\n"
>> WScript.Echo Replace(sLine, "\r\n", "[,append]")
>>
field 1,... one line[,append]and ...field 3[,append]

When using .ReadLine() you also have to expect to get 'short lines' terminated at the embedded \r\n. So your options are:

1. Talk to the creator of the .CSV and ask her to use different markers for the embedded vs. the eol marker. You can then use Replace(.ReadAll(), eol, extra & eol, provided you deal with the 'spurious replacement on the last (empty) line' mentioned above.
2. Depending on the format/layout/content of the .CSV - quoted strings, field separator embedded in (quoted) fields, data types, line length(es), position of \r\n in a line - you may be able to write a custom RegExp replacement function or a full-fledged parser that does the job (cf here). To help you with that I'd need a decent desription of the .CSV (think schema.ini) and representative sample data.
3. Use ADO text driver (start your research here, but don't stop there). The risk: I'm not sure whether the driver can cope with (mis)using the eol marker embedded.

A rough idea:

After a short look at the sample data, I can confirm that \r\n is used for eol and paragraph in some of the quoted fields. First tests show that the ADO text driver can deal with that.

But because the first field contains quoted IP Adresses, you can distinguish between eol (\r\n followed by " (or "IP Address")) and paragraph (\r\n not followed by " (or "IP Address")). If there are no " in the 'paragraphed' fields, a (not so) simple Replace for \r\n" on the .ReadAll will solve the problem; otherwise a RegExp refering to the IP Address would be needed.

Demo code:

Option Explicit

Dim sCsv : sCsv = Replace(Join(Array( _
     "'1.2.3.4','line1\r\nline2',1,'whatever'" _
   , "'5.6.7.8','linea\r\nlineb',2,'pipapopu'" _
   , "" _
), "eol"), "'", """")
WScript.Echo "faked .CSV (embedded \r\r, EOL \r\n)"
WScript.Echo Replace(sCsv, "eol", "\r\n" & vbCrLf) ' for display
sCsv = Replace(Replace(sCsv, "eol", vbCrLf), "\r\n", vbCrLf) ' 'real data'
WScript.Echo "raw display of .CSV"
WScript.Echo sCsv

If False Then
  Dim re : Set re = New RegExp
  re.Global = True
  re.Pattern = "(\r\n""\d+(\.\d+){3}"")" ' EOL followed by "IP Address" of next line
  ' dirty hack to append to last line
  sCsv = sCsv & """0.0.0.0"""
  sCsv = re.Replace(sCsv, ",""append""$1")
  ' remove trailing junk (\r\n"IP Address")
  sCsv = Left(sCsv, Len(sCsv) - 11)
Else
  ' dirty hack to append to last line
  sCsv = sCsv & """"
  sCsv = Replace(sCsv, vbCrLf & """", ",""append""" & vbCrLf & """")
  ' remove trailing junk (\r\n")
  sCsv = Left(sCsv, Len(sCsv) - 3)
End If
WScript.Echo "raw display of .CSV after appending"
WScript.Echo sCsv

output (for both methods):

cscript 24673618.vbs
faked .CSV (embedded \r\r, EOL \r\n)
"1.2.3.4","line1\r\nline2",1,"whatever"\r\n
"5.6.7.8","linea\r\nlineb",2,"pipapopu"\r\n

raw display of .CSV
"1.2.3.4","line1
line2",1,"whatever"
"5.6.7.8","linea
lineb",2,"pipapopu"

raw display of .CSV after appending
"1.2.3.4","line1
line2",1,"whatever","append"
"5.6.7.8","linea
lineb",2,"pipapopu","append"