Solved extracting data from csv based using .bat file

November 9, 2016 at 20:48:46
Specs: Windows 7
RecordID,Name,ShortDescription,Description,ComponentAliases
SANDPFA01,SANDPFA01,SANDPFA01,Storage Subsystem,SANDPFA01
SANKNXFB0,SANKNXFB01,SANKNXFB01,Mainframe HMC,SANKNXFB01
DS8000-2107-75YY961,DS8000-2107-75YY961,DS8000-2107-75YY961-IBM,Sysplex,DS8000-2107-75YY961
DS8000-2107-75P2071,DS8000-2107-75P2071,DS8000-2107-75P2071-IBM,LPAR,DS8000-2107-75P2071
AUTE2OV077,AUTE2OV077NABOI,AUTE2OV077NABOI,Test Description,AUTE2OV077NABOI


My csv file InputCI.csv has the above listed data.My requirement is :

1.To filter out records based on 'Description' column.Like if Description=Storage Subsystem or Description=Sysplex or Description=LPAR copy these records in another file say InputCIModified.csv

Result should be:
RecordID,Name,ShortDescription,Description,ComponentAliases
SANDPFA01,SANDPFA01,SANDPFA01,Storage Subsystem,SANDPFA01
DS8000-2107-75YY961,DS8000-2107-75YY961,DS8000-2107-75YY961-IBM,Sysplex,DS8000-2107-75YY961
DS8000-2107-75P2071,DS8000-2107-75P2071,DS8000-2107-75P2071-IBM,LPAR,DS8000-2107-75P2071


2.Write the filtered records in another file say InputCIModified.csv

3.Rename first file InputCI.csv to InputCIError.csv.

4.Rename second file to InputCI.csv.


Basically needs skip the unwanted rows based on value in description field.


Is this doable?I am very new to the batch thing.Kindly help.


See More: extracting data from csv based using .bat file

Report •


#1
November 10, 2016 at 00:50:36
::====== script starts here ===============
::
:: ash.bat 2016-11-10 13:57:44.43
@echo off > NEWFILE & setLocal enableDELAYedeXpansioN

copy ash.csv myfile

:main
for /f "tokens=* delims=, " %%a in (myfile) do (
call :sub1 %%a
) >> NEWFILE
goto :eof

:sub1
if %4 equ Storage if %5 equ Subsystem echo.%*
if %4 equ Sysplex echo.%*
if %4 equ LPAR echo.%*
goto :eof
::====== script ends here =================

=====================

M2 Get custom script or take private lessons


Report •

#2
November 10, 2016 at 15:38:01
Thanks buddy for the help , I am trying the code now.Will get back to you with the results.

Report •

#3
November 10, 2016 at 20:29:12
✔ Best Answer
Alternative, using FINDSTR (Ha! not my favorite, only took half hour to sort it out):

@echo off & setlocal
set result=ciresult.csv
set feed=inputci.csv
set fstr=source
rem 'source' content:
Description
Storage Subsystem
Sysplex
LPAR

rem this following file feeds the target strings. you could substitute a "hardwired"
rem variable or use other source of inputs to get these strings...
rem 'source' contains list of target strings to look for in field 3 of feed, one per line
for /f "tokens=*" %%a in (%fstr%) do (
rem build the findstr pattern into file 'targets'
>>targets echo ^^[^^,]*,[^^,]*,[^^,]*,%%a,.*
)
rem execute the pattern out to the results file
findstr /r /g:targets %feed%>%result%
echo output went to: %result%

edit: short version, does not use other files, fixes a couple things:

findstr /r "^.*,.*,.*,Description,.* ^.*,.*,.*,Storage Subsystem,.* ^.*,.*,.*,Sysplex,.* ^.*,.*,.*,LPAR,.*" inputci >ciresult.csv

message edited by nbrane


Report •
Related Solutions


Ask Question