Remove First and last row of csv file with batch script

Question

I am trying to remove the first and the last row of CSV files in a folder and after that merge them to make one csv file.

I am trying to remove the first and last row with the batch script below but its not working:

@echo on 
setlocal enabledelayedexpansion

for %%a in (*.csv) do (
find /v "RemoveThis" < %%a >> %%~Na.csv
)

Anyone has done similar batch script before. Can someone help me with this one.

The CSV will look like this.

0   RemoveThis                                  
1   6105829 4   541746546   46542114546 56541651651 132 132 1   31/12/2017 20:43    31/12/2017 20:45
1   6105837 4   4545    6546    4465465 396 396 1   31/12/2017 18:20    31/12/2017 18:26
1   6105844 4   21656565    46546546    4654466546  36  36  1   31/12/2017 19:27    31/12/2017 19:27
1   6105845 4   45454   45454   465465  2   2   1   31/12/2017 19:26    31/12/2017 19:26
99  RemoveThis      188

Martin Brandl · Accepted Answer · 2018-02-27 09:00:52Z

2

Using PowerShell you could do it using the Get-Content cmdlet to retrieve the text, skip the first and last line using the Select-Object cmdlet and finally append it to the merge file using the Add-Content cmdlet.

To iterate over each csv use the Get-ChildItem cmdlet:

Get-ChildItem -Path 'D:\' -Filter '*.csv' | ForEach-Object {
    Get-Content $_ | Select -Skip 1 | Select -SkipLast 1 | Add-Content d:\merge.csv
}

answered Feb 27, 2018 at 9:00

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Martin Brandl Over a year ago

Im sure it can but I am not familiar with it. You added the PowerShell tag so I provided you a solution with PowerShell..

Dave Over a year ago

That answer will remove the header from a csv file rather than the first row.

Martin Brandl Over a year ago

@Dave Thats true but based on his example this is what he needs.

Dave Over a year ago

Absolutely! I didn't mean it as a critic of your answer, rather an additional note. I have added a solution that keeps the header.

Dave · Accepted Answer · 2018-02-27 12:08:04Z

1

Alternative answer which keeps the column headers.

#Set source Directory
$SourcePath = "D:\CsvFiles"

#Create new subdirectory 
$SubDir = New-Item -Path $SourcePath -Name NewCsv -ItemType Directory

# Get all files with csv extension
$CsvFiles = Get-ChildItem -Path $SourcePath -filter "*.csv"

#Loop through files
foreach ($File in $CsvFiles)
{
   #Import the csv file
   $Csv = Import-Csv -Path $File.FullName

   #Create Destination Path for new csv file based on file name
   $Des = Join-Path -Path $SubDir.FullName -ChildPath $File.Name

   #remove first and last row from csv but keep column header
   $NewCsv = $Csv | where {$_ -ne $Csv[-1] -and $_ -ne $Csv[0]}

   #Save to new file
   $NewCsv | Export-Csv -Path $Des
}

edited Feb 27, 2018 at 12:08

answered Feb 27, 2018 at 11:58

Dave

3641 silver badge8 bronze badges

Comments

lit · Accepted Answer · 2018-02-27 16:51:04Z

0

Assuming that there is not a column header record, the following will do what you are asking.

Get-ChildItem -File -Filter '*.csv' |
    ForEach-Object {
        Get-Content -LiteralPath $_.FullName -ReadCount 1
            Select-Object -Skip 1 |
            Select-Object -SkipLast 1
    }

answered Feb 27, 2018 at 16:51

lit

16.5k11 gold badges80 silver badges146 bronze badges

Collectives™ on Stack Overflow

Remove First and last row of csv file with batch script

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related