Need help deleting the second line in all csvs in a folder

[capella]

I just spent an hour and a half Googling how to do what I need to do, but everything I read doesn't explain the syntax of what's going on. I'm just not getting how to do this.

I need to delete the second line from all CSV files in a folder.

The folder will be in the form of a variable, "%OUTPUTFOLDER%" and all CSVs in that folder will have a second line that needs to be deleted. So pseudo code would look something like this:

go through each CSV file in %outputfolder%
In the current CSV delete the second line
save the CSV with the same name in the same location
go to the next CSV in the folder

I would greatly appreciate your help and an explanation of what's going on at each step!

[penpen]

If your csv files only have lines with less then 8190 characters,
if all poisonous characters ('&','|',...) are are encapsulated within doublequotes,
if no line in your csv files start with a double colon character ':', and
if your line endings are "\r\n" (carriage return, newline/ i hex "0d 0a")
then you could use a for/f loop (untested):

Code: Select all

@echo off
setlcoal enableExtensions disableDelayedExpansion

for %%a in ("%outputfolder%\*.CSV") do (
	(
		for /f "tokens=1* delims=:" %%b in ('findstr /n "^" "%%~a"') do if not "%%~a" == "2" (
			echo(%%c
		)
	) > "temp.csv.txt"
	copy "temp.csv.txt" "%%~a"
)

The outer for-loop loops over all *.csv files in "%outputfolder%".
The inner for/f loop loops over all lines produced by the inner command, seperating the line in two parts (before and after the first group of colons).
The part after the colon group is printed by the echo statement, if the part before the colon is not "2" (if statement).
The inner (findstr) command echoes the file content of the actual file referenced by the outer for loop and adds the line number and a double colon character.
The redirection writes all echoed output to a file named "temp.csv.txt".
The copy command overwrites the old file (actually i don't know how to supress the overwrite confirmation ("/y" or something like that?).

Note that you always should make a backup before experimenting on data.


penpen

[capella]

Thanks, penpen. Apparently, I should have been more specific about what's in my file!

The text in the files will look very much like the following:

Code: Select all

Name|Address1|Address2|City|State|Zip|ID
----|--------|--------|----|-----|---|--------
John Smith|10 Fourth Avenue SE||Some City|MN|12345|82X9Z43
Mary Anderson|7 4th Avenue SE|PO Box 1234|Another City|CO|54321-1234|9Y28H913

These are pipe-delimited CSVs with no quotes in the cells, however, there may be quotes in some of the cells as cell data, and the data could contain your "poisonous characters", but again, will not have quotes around the individual cell data.

Not necessarily all of the files will have demographic data, but the consistent part is the second line. Dashes, pipe, dashes, pipe, etc, etc. The number of dashes in each column will be dictated by the max width of the data in all rows in that column (my sample above is just that: a sample, as the dashes don't match the column data widths).

And yes, the lines end in \r\n and I am using test files for this, thanks!

[Squashman]

So the second line is always a bunch of dashes with the delimiter?

[capella]

That's correct.

[Squashman]

That's correct.

Then it could be as simple as a reverse match on a simple regular expression using the FINDSTR command.

[capella]

Okay, looking at SS64.com's FINDSTR page I put the following together:

Code: Select all

@echo off

set _outputfolder="D:\A Folder\A Subfolder\Test Folder"

echo.
echo %_outputfolder%

@findstr /r /b /c:"-*|" %_outputfolder%\*.csv

echo.
echo errorlevel: %errorlevel%

@pause

And when I run that my output is this:

Code: Select all

"D:\A Folder\A Subfolder\Test Folder"
D:\A Folder\A Subfolder\Test Folder:----|------------|------------|----|-------|---|-----------

errorlevel: 0

Which tells me that it's correctly finding that line, so how do I integrate that into a loop that looks through each file in "Test Folder" and deletes the line it finds?

[Squashman]

Something like this should work.

Code: Select all

@echo off

set "_outputfolder=D:\A Folder\A Subfolder\Test Folder"
echo %_outputfolder%
pushd "%_outputfolder%"
for %%G in ("*.CSV") do (
	findstr /v /r /c:"^-*|-*" "%%~G">temp.csv.txt
	move "temp.csv.txt" "%%~G"
)
delete temp.csv.txt
popd