Reputation: 45

Extracting specific row based on a specific criteria Windows batch file

I'm trying to write a simple batch file to do the following:

Read the lines of a text file; each line contains a team.
Find the data with that criteria from several CSV files. (Each team could have many rows)
Copy the result in one CSV file.
Loop till the end of the teams.

CSV files contains data like this:

"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,

"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,

"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,

"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,

"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,

"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,

......

The text file contains:

Team A

Team B

Team C

Team D

...

This is the code I tried:

FOR %%A IN (Team.txt) DO Find "%%A" *.csv > result.txt

Upvotes: 1

Answers (3)

DavidPostill

Reputation: 7921

How do I extract rows based on a specified criteria?

Use the following batch file (test.cmd):

@echo off
setlocal enabledelayedexpansion
for /f "delims=" %%t in (team.txt) do (
  for /f "usebackq tokens=*" %%i in (`findstr /c:"%%t" *.csv`) do (
    set _line=%%i
    rem strip filenames from findstr output
    set _line=!_line:*:=%!
    echo !_line! >> "%%t".csv
  )
)
endlocal

Notes:

team.txt contains the team names.
If there are matching lines for a particular team name then an output csv file is created with the name team name.csv.

Example usage and output:

F:\test>type team.txt
Team A
Team B
Team C
Team D
F:\test>type *.csv

1.csv


"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,
"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,
"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,
"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,
"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,
"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,

2.csv


"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,
"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,
"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,
"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,
"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,
"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,

F:\test>test
processing Team A
processing Team B
processing Team C
processing Team D

F:\test>type t*.csv

Team A.csv


"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,
"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,
"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,
"Team A",2013/06/15 02:14:58 -0400,"X","LQ3SUEEWPWKL6",005,

Team B.csv


"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,
"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,
"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,
"Team B",2014/06/20 03:54:12 -0321,"Y","LQ4STREWNBKM5",004,

Team C.csv


"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,
"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,
"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,
"Team C",2011/01/14 00:00:00 -0400,"Y","LQ4STREWNBKM5",004,

An A-Z Index of the Windows CMD command line - An excellent reference for all things Windows cmd line related.
enabledelayedexpansion - Delayed Expansion will cause variables to be expanded at execution time rather than at parse time.
findstr - Search for strings in files.
for /f - Loop command against the results of another command.
syntax-replace - Variable Edit/Replace

Upvotes: 1

TessellatingHeckler

Reputation: 29033

Assuming your text file does not have spaces every other line, because that's just weird:

for /f "delims=" %t in (teams-in.txt) do (grep "%t" *.csv > "%t.out")

Where grep is grep for Windows

[edit: and don't output to .csv in the same folder as searching *.csv cough ]

Upvotes: 0

Martin Brandl

Reputation: 58991

In PowerShell, you could use the Import-Csv cmdlet. Just specify the header for the first column to enable the access using the property name, select it using Select-Object and finally write it to a file using the Out-File cmdlet:

Import-Csv -Path 'your_source_path.csv' -Header "Team" | 
    select -ExpandProperty Team | 
    Out-File 'YourDestination.txt'

File content:

Team A
Team C
Team B
Team A
Team C
Team B

Upvotes: 2

Extracting specific row based on a specific criteria Windows batch file

Answers (3)

How do I extract rows based on a specified criteria?

Further Reading

Related Questions