Hi netcmh
OK, so what the annotations in your attached csv file SEEM to imply is that you have 5 fields:
PC-Num, YY-MM-YY HH:MM:SS.000, YY-MM-YY HH:MM:SS, 0.0, 0.0
The first HH:MM:SS are all set to Zero, but the SS in the 2nd HH:MM:SS is always rounded to Zero seconds.
You are saying that it is OK to have some extra numbers following the PC-Num but that they must be in the same field?
You are also saying that the two fields that follow the 2nd instance of HH:MM:SS ie. 0.0,0.0 must be consistently 0.0,0.0 and that if any other values are added in there, they should be corrected?
I suppose the easiest way to deal with this is to use the FOR command to grab each comma-separated "token" and verify that the important ones match your rule. You tell the FOR command to use the comma as the field delimeter and you can break up each line into as many tokens as it finds like this:
for /f "tokens=1-6 delims=," %%a in (4.csv) do (
set FIELD1=%%a
set FIELD2=%%b
set FIELD3=%%c
set FIELD4=%%d
set FIELD5=%%e
)
So, for each line parsed in the file "4.csv" you have 6 variables that you can test against a specific rule with each pass. If the values held in the variables being tested don't match what you want them to match, then you reset the variable to whatever value you do want and then echo all the variables to another file.
Take the first line in your example file.
PC-20,2009-10-19 00:00:00.000,2009-08-20 11:30:00,0.0,0.0
When parsed and split into tokens, the following variables should hold the data shown:
FIELD1 = PC-20
FIELD2 = 2009-10-19 00:00:00.000
FIELD3 = 2009-08-20 11:30:00
FIELD4 = 0.0
FIELD5 = 0.0
Taking your last "wrong" line as the next example
PC-8,2009-10-18 00:00:00.000,2009-08-20 11:30:00,0.0,0.0PC-9,2009-
the following would be the case when parsed by the same FOR command:
FIELD1 = PC-8
FIELD2 = 2009-10-19 00:00:00.000
FIELD3 = 2009-08-20 11:30:00
FIELD4 = 0.0
FIELD5 = 0.0PC-9
Because we haven't stored the 6th token (2009-10-19 00:00:00.000), and because the 7th field of your csv file would just be a blank cell in an excel spreadsheet because there is nothing after that terminating comma, we could just output the "corrected" line with 5 fields:
PC-8,2009-10-18 00:00:00.000,2009-08-20 11:30:00,0.0,0.0
using the command:
echo %FIELD1%,%FIELD2%,%FIELD3%
that %FIELD5:~0,3% variable modifier just starts at the first character and includes only 3 characters, so 0.0PC-9 would be modified to 0.0
There are just too many unknowns in your question to know what you would want corrected.
I mean, if you consistently want to have the HH:MM:SS part of your 2nd comma-delimeted field set to 00:00:00.000, and have the 4th and 5th fields consistently set to 0.0, then you could tell the FOR command to use spaces and commas as delimeters (using 7 tokens now) and only get tokens 1, 2, and 4 and 5 into variables. You could SET tokens 3, 6, and 7 to the static values you always want to have in your file. The following example would do this:
--------------------------
@echo off
set TOKEN3=00:00:00.000
set TOKEN6=0.0
set TOKEN7=0.0
for /f "tokens=1-7 delims=, " %%a in (4.csv) do (
set TOKEN1=%%a
set TOKEN2=%%b
set TOKEN4=%%d
set FIELD5=%%e
)
echo %TOKEN1%,%TOKEN1% %TOKEN3%,%TOKEN4% %TOKEN5%,%TOKEN6%,%TOKEN7%
del 4.csv
ren NewFile.csv 4.csv
EXIT
--------------------------
So, there are methods that can be used, but we would have to know EXACTLY what your rules actually are. As peetjh has asked, "What is wrong with the line that starts with PC-2-City?". In fact, could you please explain why all the lines you marked as "wrong" don't fit your rules.
Bill
Main Topics
Browse All Topics
by: peetjhPosted on 2009-10-19 at 17:17:03ID: 25610046
What is wrong with the line that starts with PC-2-City?