CODEWITHSUNDEEP
windowsbatch-file
oracle bharadwaj
oracle bharadwaj

Reputation: 3

Windows Batch Script: Grab the value under a particular column from a text file

I have a text file with multiple columns

Column1 Column2 Column3 AS_OF_DATE
1        2       3      07/12/2017
2        3       4      07/12/2017
.....

n number of rows AS_OF_DATE represents the date of the data in the text file.It is same for all the rows.The place of AS_OF_DATE column can be at any place. Now my requirement is to pick the date under the column AS_OF_DATE and store it in a separate file.

Any help in this appreciated.

Thanks KVB

Upvotes: 0

Views: 1323

Answers (1)

dbenham
dbenham

Reputation: 130849

This code makes many assumptions about your data:

  • There are no * or ? characters in your header line
  • There are no poison characters like &, | etc. in your header line
  • There are no spaces in your column names or column values
  • All columns are fully populated
  • Your input file uses standard Windows line endings or <CR><LF>, not unix ending of <LF>

There may be other constraints that I am not remembering

@echo off
setlocal

:: Define files
set "input=test.txt"
set "output=out.txt"

:: Read first (column header) line
<"%input%" set /p "ln="

:: Figure out which column contains AS_OF_DATE
for /f "delims=:" %%N in (
  'cmd /c "(for %%C in (%ln%) do @echo %%C)|findstr /n AS_OF_DATE"'
) do set "col=%%N"

:: Write all AS_OF_DATE values to a new file
>"%output%" (
  for /f "skip=1 usebackq eol= tokens=%col%" %%A in ("%input%") do echo %%A
)

UPDATE: Here is a modification that reads only the first 2 lines

@echo off
setlocal

:: Define files
set "input=test.txt"
set "output=out.txt"

:: Read the first two lines
<"%input%" (
  set /p "header="
  set /p "data="
)

:: Figure out which column contains AS_OF_DATE
for /f "delims=:" %%N in (
  'cmd /c "(for %%C in (%header%) do @echo %%C)|findstr /n AS_OF_DATE"'
) do set "col=%%N"

:: Extract the AS_OF_DATE value and write to a new file
for /f "skip=1 eol= tokens=%col%" %%A in ("%data%") do >"%output%" echo %%A

Upvotes: 2

Related Questions