Vista - End of Line

Hi everyone,

I have a .csv file where certain fields have carriage returns/new line
feeds. Because of such, I cannot properly import my file. Hence, I
wrote the following script and while it removes the carriage returns/
new line feeds through the file, it ALSO removes the END OF LINE
carriage return/line feed. So now, I have one file with one record.

Basically,I need to know how to remove the carriage returns/line feeds
in the file EXCEPT for the ones that is END OF LINE. Can somebody help
me please?

Dim strSearchString, objFSO, objFile
Const ForReading = 1
Const ForWriting = 2


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading)
strSearchString = objFile.ReadAll
objFile.Close


objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) &
Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2)
& ".csv"
Set objFile = objFSO.OpenTextFile("d:\Test\Project_" & DatePart
("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" &
DatePart("d", Date),2) & ".csv", ForWriting)
strSearchString = Replace(strSearchString, VbCrLf, "")
strSearchString = Replace(strSearchString, Vbtab, "")

objFile.Write strSearchString
objFile.Close

"Odd" <audrey.nsh@xxxxxx> wrote in message
news:8a2b8b75-480a-4315-88d4-849b5ceb3c96@xxxxxxHow do you distinguish a CRLF at the end of a field from a CRLF at the end
of a line?

"Odd" <audrey.nsh@xxxxxx> wrote in message
news:8a2b8b75-480a-4315-88d4-849b5ceb3c96@xxxxxxWhen I read a text file I use the ReadLine method of the file object. I loop
through all lines of the file until the AtEndOfStream condition is met. I
also check each line to see if it is blank:
======
Do Until objFile.AtEndOfStream
strLine = Trim(objFile.ReadLine)
If (strLine <> "") Then
' Process the line...
End If
Loop
=====
Alternatively, I use the ReadAll method and use the Split function to
convert into an array of lines. Again, any line can be blank, especially the
last line:
===
arrLines = Split(objFile.ReadLine, vbCrLf)
For Each strLine In arrLines
If (strLine <> "") Then
' Process the line...
End If
Next

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

On Sep 8, 11:41*am, "Pegasus [MVP]" <n...@xxxxxx> wrote:Hmm...well, through trial and error. Basically, when I open the .csv
file in Excel, the field (on one record) that has a carriage return is
represented as a square and the line feed is represented as a new line
(in Excel, it is similar to ALT-ENTER). Then, again, when I ran my
script, I know that there is a CRLF at the end of the line because I
can see that the second line moved up to the first........sorry for
the explanation, but truly, that's how I figured out.

"Odd" <audrey.nsh@xxxxxx> wrote in message
news:7c4672bc-7245-4f05-883c-dabe25f0227e@xxxxxx
On Sep 8, 11:41 am, "Pegasus [MVP]" <n...@xxxxxx> wrote:Hmm...well, through trial and error. Basically, when I open the .csv
file in Excel, the field (on one record) that has a carriage return is
represented as a square and the line feed is represented as a new line
(in Excel, it is similar to ALT-ENTER). Then, again, when I ran my
script, I know that there is a CRLF at the end of the line because I
can see that the second line moved up to the first........sorry for
the explanation, but truly, that's how I figured out.

==========

If you want your script to be robust then you must stop guessing and use a
proper tool to examine your file structure. XVI32.exe is such a tool. You
can download it from http://www.chmaas.handshake.de. It lets you examine any
file, binary or ASCII so that you can tell exactly where one record ends and
the next one starts.

On Sep 8, 11:45*am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote:Here is what I have and I still see the carriage returns and line feed

Dim strLine, objFSO, objInput, objOutput
Const ForReading = 1
Const ForWriting = 2


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objInput = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading)

objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) &
Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2)
& ".csv"
Set objOutput = objFSO.OpenTextFile("d:\Test\Project_" & DatePart
("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" &
DatePart("d", Date),2) & ".csv", ForWriting)


Do Until objInput.AtEndofStream
strLine = Trim(objInput.ReadLine)
If (strLine <> "") Then
strLine = Replace(strLine, vbCrLf, "")
strLine = Replace(strLine, vbtab,"")
objOutput.WriteLine strLine


End If
Loop



objInput.Close
objOutput.Close

On Sep 8, 12:09*pm, "Pegasus [MVP]" <n...@xxxxxx> wrote:Pegasus,

The file is in tabular format but comma-delimited. Excel opens
the .csv file properly. I have a starting column (Project_ID) and an
ending column (Project_Currency_ID). The problem that I have is that I
have a column in between the first column and the last column that has
entries that have carriage returns and line feeds. I need to get rid
of those...

On Sep 8, 12:21*pm, Odd <audrey....@xxxxxx> wrote:An example would be:

Project_ID, Project_Name, Major Assumptions, Project_Currency_ID
3, ProjectABC, SomeAssumptionsaaa, 2
SomeAssumptionsbbb
4, ProjectDEF, SomeAssumptionsccc, 2
5, ProjectGHI, SomeAssumptionsddd,2


So, if you see Project_ID = 3, it is the carriagereturn/line feed in
the Major Assumptions columns that I want to get rid of.

Odd wrote:[snip]"certain fields have carriage returns/new line feeds"

Do you mean EMBEDDED within the field?

If so, could you count the quotes - an odd number on a text-input line would
indicate a newline-within-a-field.

Do you mean randomly following a (nominally-comma) separator? Or in place of
that separating (comma)?

If so, could you count the number of fields in a line, where a field is
terminated by a comma except if the comma occurs between one quote and the
next? If you have insufficient fields, then the next line may be the final
fields of the current record.

CSV is a widespread but sloppy standard. Some people will insist that a
"properly formatted" csv will contain the separators whenever the field is
empty and others will insist that trailing commas are optional. I've heard
both arguments. Will this affect how your processing proceeds?

Would you be able to press a tool like SED or (g)awk into play to
pre-process your file to "standard" format?

"Odd" <audrey.nsh@xxxxxx> wrote in message
news:3ccd9e18-9354-4c57-8ec5-4298bc9be107@xxxxxx
On Sep 8, 11:45 am, "Richard Mueller [MVP]" <rlmueller-
nos...@xxxxxx> wrote:Here is what I have and I still see the carriage returns and line feed

Dim strLine, objFSO, objInput, objOutput
Const ForReading = 1
Const ForWriting = 2


Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objInput = objFSO.OpenTextFile("d:\Test\Project.csv", ForReading)

objFSO.CreateTextFile "d:\Test\Project_" & DatePart("yyyy",Date) &
Right("0" & DatePart("m",Date),2) & Right("0" & DatePart("d", Date),2)
& ".csv"
Set objOutput = objFSO.OpenTextFile("d:\Test\Project_" & DatePart
("yyyy",Date) & Right("0" & DatePart("m",Date),2) & Right("0" &
DatePart("d", Date),2) & ".csv", ForWriting)


Do Until objInput.AtEndofStream
strLine = Trim(objInput.ReadLine)
If (strLine <> "") Then
strLine = Replace(strLine, vbCrLf, "")
strLine = Replace(strLine, vbtab,"")
objOutput.WriteLine strLine


End If
Loop



objInput.Close
objOutput.Close
-------------
Pegasus's suggestion may be best. The only thing I can think of is that your
comma delimited file has carriage returns embedded in the field values. If
the Replace function doesn't remove them, maybe they are vbCr or vbLf
characters in addition to vbCrLf. I would try:
=======
Do Until objInput.AtEndofStream
strLine = Trim(objInput.ReadLine)
If (strLine <> "") Then
strLine = Replace(strLine, vbCrLf, "")
strLine = Replaced(strLine, vbCr, "")
strLine = Replaced(strLine, vbLf, "")
strLine = Replaced(strLine, vbFormFeed, "")
strLine = Replace(strLine, vbtab,"")
objOutput.WriteLine strLine
End If
Loop
========
vbCrLf is Chr(13) & Chr(10) (ANSI carriage return and line feed)
vbCr is Chr(13) (ANSI carriage return)
vbLf is Chr(10) (ANSI line feed)
vbFormFeed is Chr(12) (ANSI form feed)

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--