2

I'd like to delete lines 1 and 3 to 19 of a csv file. I am able to delete all the 19 lines but not able to preserve the line I need (2nd line) By using other posts, my script looks like the following 

@echo off                                                                                          
set "csv=test.csv"  
more +1 "%csv%" >"%csv%.new"   
move /y "%csv%.new" "%csv%" >nul  
----missing instruction ---  
more +17  "%csv%" >"%csv%.new"  
move /y "%csv%.new" "%csv%" >nul

So I delete the first line (then my desired line is now at the top), and I'd like to delete 17 lines starting in the 2nd one. I do not know how to skip the first line.

Mark
  • 3,332
  • 1
  • 19
  • 33
davidroch
  • 55
  • 1
  • 5

2 Answers2

2
(
set /p unused=
set /p line2=
)<%csv%
echo %line2%>%csv%.new
more +19 %csv% >>%csv%.new
rem move /y "%csv%.new" "%csv%" >nul

remove the rem when it looks ok

Stephan
  • 47,723
  • 10
  • 50
  • 81
  • +1, Nice solution if the file may have more than 19 lines. Note that use of SET /P limits line length to 1021 characters. Also, MORE will convert tabs into strings of spaces. – dbenham Apr 03 '14 at 11:35
  • Thanks! This works just fine with my .csv file. It was clear in my question, but the .csv has much more lines and I wanted to clear the header except for the 2nd line – davidroch Apr 03 '14 at 12:35
  • My script seems to work only for files below 65536 lines. Is that a limitation of DOS? When running larger files it just cuts the file. – davidroch Apr 03 '14 at 14:22
  • yes - it's a system limitation. But I'm sure, @dbenham has a solution for that. – Stephan Apr 03 '14 at 14:24
1

If your file only has 19 lines, then you only want to keep line 2. The easiest solution is to flip the logic and worry about the line you want to keep.

Here are a few options for extracting the 2nd line.

This one is limited to a maximum line length of 1021, and trailing control characters will be stripped.

@echo off
setlocal enableDelayedExpansion
set "csv=test.csv"
<"%csv%" (
  set /p "ln="
  set /p "ln="
)
(echo(!ln!)>"%csv%"

Use of a FOR /F supports line lengths up to nearly 8191.

@echo off
setlocal
set "csv=test.csv"
for /f "usebackq skip=1 delims=" %%A in ("%csv%") do (
  (echo(%%A)>"%csv%"
  goto :break
)
:break

If you know that your target line does not begin with :, then

@echo off
setlocal
set "csv=test.csv"
for /f "tokens=1* delims=:" %%A in (
  'findstr /n "^" "%csv%" ^| findstr /b "2:"'
) do (echo(%%B)>"%csv%"

EDIT

Now that I know the source file has more than 19 lines, than I would use Stephan's answer, unless one of the following limitations becomes a problem:

  • SET /P cannot read more than 1021 characters in a line
  • MORE will convert tabs into a string of spaces
  • If the file is large enough (I think more than 64K lines) then the output will eventually hang, waiting for a key press, even though the MORE output is redirected.

IF any of the above limitations are a problem, and all lines are less than 8191 characters long, and no line begins with :, then the following will work. But it is slower.

@echo off
setlocal
set "csv=test.csv"
>"%csv%.new" (
  for /f "tokens=1* delims=:" %%A in ('findstr /n "^" "%csv%"') do (
    if %%A equ 2  echo(%%B
    if %%A gtr 19 echo(%%B
  )
)
move /y "%csv%.new" "%csv%" >nul

But if you want a really fast solution without any restrictions, then you could use my REPL.BAT utility - a hybrid JScript/batch script that performs a regex search/replace operation on stdin and writes the result to stdout. It is pure script that will run natively on any modern Windows machine from XP onward. Full documentation is embedded within the script.

@echo off
setlocal
set "csv=test.csv"
findstr /n "^" "%csv%"|repl "^(1|10|11|12|1?[3456789]):" ":"|repl "^\d+:" "" A >"%csv%.new"
move /y "%csv%.new" "%csv%" >nul

The initial FINDSTR simply prefixes each line with the line number, followed by a colon.

The first REPL strips the line number before the colon if it is 1 or 3-19

The last REPL removes any prefix beginning with a number followed by a colon, and discards lines that are not modified.

Community
  • 1
  • 1
dbenham
  • 119,153
  • 25
  • 226
  • 353
  • Thanks dbenham. My question was not clear, the file has more than 19 lines. I need to keep the 2nd and all the lines after the 20th. I tested your solution and as you state it works well to keep the 2nd line, so the rest of the file is gone – davidroch Apr 03 '14 at 12:51
  • @user3493107 - OK, answer modified to support more than 19 lines – dbenham Apr 03 '14 at 14:22
  • `if %%A equ 1 echo(%%B`. Shouldn't be that a `2`? – Stephan Apr 03 '14 at 14:30
  • Thanks dbenham and Stephan. I had some files bigger than 64K and the solution based on cmd hanged. Then I used the solution based on repl.bat by dbenham and it works perfect! I am delighted with the quality of the responses to my questions – davidroch Apr 04 '14 at 09:30
  • I have a slightly different question. It turns out the size of the header is not constant - it depends on the configuration of a .csv file. So when I tested the batch over some 20 csv files in a folder, it worked fine to delete the lines 1,3-19 but some files still had some header lines. Now the question is how the script can be used to detect the end of the header. The last header line is always "Time(ms),1,2,3" – davidroch Apr 07 '14 at 08:57
  • Here the code that I used: for /f "tokens=*" %%a in (list.txt) do ( findstr /n "^" "%%a"|repl "^(1|10|11|12|1?[3456789]):" ":"|repl "^\d+:" "" A >"%%a.new" move /y "%%a.new" "%%a" >nul ) – davidroch Apr 07 '14 at 08:57
  • @davidroch - As you say, that sounds like a different question. I think it would be better to actually open a new question. – dbenham Apr 07 '14 at 12:26
  • Thanks you dbenham. However it is a question based on your repl-bat. That's why I didn't formulate it as a new question as I would get responses based on cmd batching which is limited to files of < 64K. – davidroch Apr 07 '14 at 12:29