How to remove CR/LF in between the line

[aaksar]

Hi,

I am getting input file , but it contains extra CR/LF in b/w the line.

after 1 line CR/LF its fine, but when it comes in b/w the line s , it will start new lines from there only.

how can i remove the extra cr/lf
pls help me

Thanks

[mfm4aa]

Try this on the command line:

Code: Select all

>output.txt (for /f "tokens=*" %i in (input.txt) do @echo.%i)

[aaksar]

Hi ,

Its removing the CRLF but if my line contain 2 CRLF its removing 1, it should remove CRLF which are comin in the middle of line, and Its removing CRLF which are coming End of the line also

[mfm4aa]

CR/LF in the middle of a line? Please post such a line.

[jeb]

CR/LF in the middle of a line? Please post such a line.

It's impossible to have two CR/LF in one line
As a line is defined as text terminated by a CR/LF.
So the next CR/LF is part of the next line

jeb

[foxidrive]

I suspect he's using WMIC and it's a CR alone that is causing the issue.

aaksar, show us the command that is causing the issue...

[aaksar]

Actually Its coming in 1 Line,

[foxidrive]

You aren't helping us understand what you're doing.

[aaksar]

below is 1 line, but 3 CRLF is coming in/bw, RED color one is the 1 column but coming in 3 lines and with 2 CRLF,

"0711105","11","00000","119",2010-05-03-22.13.00.000000,"119",2012-12-12-12.13.00.000000,"M15",08/01/2008,"13",07/15/2007,07/15/2008,"E",," ",,"Prop XOL 10M x5M + 5M Swiss 41",,0,"N",0,"N","N",,"900","B5","30","46","44","4","N","P","P","D","119","119",07/15/2007,07/14/2008," ","00000","N","119"," ",,,," "," "," "," "," "," ",,0.00,0.00,0.00," "," "," ",0.00,"D"," "," ","N",,"","Wind excluded only to all 1st and 2nd tier counties from TX to SC inclusive.

Reinstatement equals 1 x 150% "," "," "," "

[foxidrive]

How is it created? That's an invalid CSV file.

Fixing it as it being created is the best solution.

[aaksar]

actually there is 1 description filed in the file.

and in that field user has entered value and then enter and 2nd value enter 3rd value..

its a free text field.

so that column value is coming in multiple line with CRLF. i need to remove the CRLF from that column only.

[Aacini]

@aaksar:

- A file is a series of lines/records, each one terminated in CR+LF.

- If your file have several CR+LF embedded in one record, then THERE IS NO WAY to fix your problem, unless there is a way to distinguish the "real" CR+LF that terminate normal records from the additional CR+LF that comin "in the middle of the line". You should started your description with these details!

- I have assumed that your file have records with a fixed number of fields (70 in your example) and that the fields are separated by commas. The Batch file below read lines from the input file and concatenates several lines until complete 70 fields in one record. Note that this method would fail if a field in a "broken" record have a comma (even if it is enclosed in quotes).

Code: Select all

@echo off
setlocal EnableDelayedExpansion

set numOfFields=70

set record=
set fields=1
(for /F "delims=" %%a in (inputFile.txt) do (
   set "record=!record!%%a"
   rem Count the fields in this line
   set "line=%%a"
   call :StrLen line allChars=
   set "noCommas=!line:,=!"
   call :StrLen noCommas withoutCommas=
   set /A fields+=allChars-withoutCommas
   if !fields! geq %numOfFields% (
      echo !record!
      set record=
      set fields=1
   )
)) > outputFile.txt
goto :EOF


:StrLen var len=
set "str=0!%1!"
set len=0
for /L %%A in (12,-1,0) do (
   set /A "len|=1<<%%A"
   for %%B in (!len!) do if "!str:~%%B,1!" == "" set /A "len&=~1<<%%A"
)
set %2=%len%
exit /B

Output:

"0711105","11","00000","119",2010-05-03-22.13.00.000000,"119",2012-12-12-12.13.00.000000,"M15",08/01/2008,"13",07/15/2007,07/15/2008,"E",," ",,"Prop XOL 10M x5M + 5M Swiss 41",,0,"N",0,"N","N",,"900","B5","30","46","44","4","N","P","P","D","119","119",07/15/2007,07/14/2008," ","00000","N","119"," ",,,," "," "," "," "," "," ",,0.00,0.00,0.00," "," "," ",0.00,"D"," "," ","N",,"","Wind excluded only to all 1st and 2nd tier counties from TX to SC inclusive.Reinstatement equals 1 x 150% "," "," "," "

Antonio

[Squashman]

I get messed up data from clients like this all the time and there is no real easy way to fix it unless it is just a single LF and not a CR\LF. If it is just a LF you could just remove all the Line Feeds from the file and then you will just have CR. You could then change the CR back to CRLF.

Most of the time this happens because whomever was doing their data entry in their order entry software decided they needed to hit the enter key a couple of times. Or they have Excel files with embedded Line Feeds in a cell because of formatting.

[dbenham]

How is it created? That's an invalid CSV file.

Fixing it as it being created is the best solution.

Actually, it is valid to embed line feeds within a CSV column as long as the column is quoted. But the majority of CSV readers fail to properly handle such records. Quote literals within a quoted column are represented as 2 consecutive quotes.


Dave Benham

[foxidrive]

it is valid to embed line feeds within a CSV column as long as the column is quoted.

LF or CR/LF ?