I am a novice in scripting, but would like help in creating a batch script that will help me clean some data.
Basically i have data in a flat file, where by the field are seperated by pipes. Each line in the dat file represent a specific client. In Each line there should be 50 fields delimited by a pipe.
e.g
001|DOG|Rascal|Brown|Whisky||2007|Y||||
002|CAT|Pat|White||2000|Y|Disk|SE5 6NZ||MALE|||I know there should be 50 fields, thus 49 Pipes. I have come accross instances where some lines have less fields or more that 50 fields.
I would like to carry out some data cleansing so I need a script that will count the pipes and tell me which line falls short, or over shoots the 49 Pipes. It would ideally output to a .txt file the findings, ie
Line 3, 47 pipes
Line 234, 56 pipes
etcSo far had use the script below but it gives me the total pipes in the file.
@echo off & setLocal EnableDELAYedExpansion
set /p str= < pam.dat
set /p C=char to count ? :
set N=:loop
if !str:~0^,1! equ !C! (
set /a N+=1
)
if "!str:~1!" neq "" (
set str=!str:~1!
goto :loop
)echo !N!> result.txt
Thanks
@echo off & setLocal EnableDELAYedeXpansion set L= for /f "tokens=* delims= " %%a in (my.psv) do ( set /a L+=1 set S=%%a call :sub1 echo.line !L! has !P! pipes ) goto :eof :sub1 set P= :loop if "!S:~0,1!" equ "|" set /a P+=1 if "!S:~1!" neq "" ( set S=!S:~1! goto :loop ) goto :eof
=====================================
Helping others achieve escape felicityM2