Easy way to check tokens 5-16 and delete if specified pattern?
Posted: 20 Feb 2020 03:22
Hi Folks -
I have the following data file (sample) that is created by another team and they are unwilling to work with me to clean up their process. Therefore, I need to handle cleaning up the file on my end:
Right now, I have this code running before the data upload and it's working fine:
I have the following data file (sample) that is created by another team and they are unwilling to work with me to clean up their process. Therefore, I need to handle cleaning up the file on my end:
What I need to do is remove the lines where tokens 5-16 are either NULL (""), 'MISSING' or a combination of "" & 'MISSING'.E01,S00000,1040-00,2019,"","","","","","","","","","","",""
E01,S00000,1100-00,2019,267171.46,273027.23,717379.93,195954.96,296135.03,813816.07,252308.17,304287.16,698450.70,335489.17,567923.76,728968.55
E01,S00000,1100-30,2019,1889066.33,2863499.20,682036.53,1985084.97,3921360.24,980411.11,2915929.92,4929743.93,1060628.90,2744523.44,3903142.91,595767.42
E01,S00000,1130-00,2019,"","","","","","","","","","","",""
E01,S00000,1140-00,2019,"","","","","","","","","","","",""
E01,S00000,1210-30,2019,163016436.29,162153109.71,161479341.90,161241199.50,160727337.53,159780948.62,159718783.88,159194871.14,159133778.25,158872207.43,158751022.73,158882082.53
E01,S00000,1210-40,2019,-218211945.61,-217738772.03,-221904629.75,-225170015.31,-224702941.25,-225234443.63,-227407317.13,-230219433.60,-229532466.89,-217082211.44,-204252043.55,-190508732.54
E01,S00000,1210-90,2019,"",'#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING'
E01,S00000,1210-50,2019,1578792.53,4648868.03,-263904.89,2608853.62,2688750.22,-937903.84,147911.79,-747464.56,765309.36,656466.40,-145911.30,501938.01
E01,S00000,1210-90,2019,'#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING'
Right now, I have this code running before the data upload and it's working fine:
But I'm unsure how to code for the 3rd exception I mentioned above when it would be a combination of 'MISSING' and "". Essentially, as long as there are NO numerical values in tokens 5-16, I can delete that line. Any ideas?FINDSTR /V /C:"\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\",\"\"" "%DATAFILENAME%" > "%DATAFILENAME%out"
FINDSTR /V /C:"'#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING','#MISSING'" "%DATAFILENAME%out" > "%DATAFILENAME%out1"