convert AM/PM fields to 24hrs entries in a text file

[z9999]

Hi there,
I am new to this forum and new to batch files on windows7.

I have a .csv file that contains thousands lines similar to those:
CLB,,,18l,,20150922,,2015-09-23 11:26:00 AM,,,2015-09-23 11:26:01 AM,,,,trad,,1344,,cam549,,,PUR,,,X001,,,PCL

CPC,,,3rs,,20150922,,2015-09-23 11:26:01 AM,,,2015-09-23 11:26:05 AM,,,,trad,,44,,cam388,,,CPC,,,X001,,,CPC

CPC,,,3rs,,20150922,,2015-09-24 11:29:00 PM,,,2015-09-24 11:29:12 PM,,,,ola,,43,,cam389,,,BPK,,,X002,,,D42

I am trying to convert AM/PM field4 (20150922,,2015-09-23 11:26:00 AM) and field5 (2015-09-23 11:26:01 AM) to 24hrs entries. For some reason !HOURS2! is always empty. I believe the problem is somewhere in the IF statement...Any idea why? and how I could make it work? Thanks!


This is the code I am using in a .bat file:

Code: Select all

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION

for /F "eol=; tokens=1,2,3,4,5,6,7,8,9,10,11 delims=," %%a in ('type C:\testing5.csv') do (
set CTS=%%d & set CTE=%%e

@echo CTS=!CTS! ---- CTE=!CTE!
for /f "tokens=1,2,3 delims= " %%o in ('echo !CTS!') do set CTIME1=%%p & set AMPM1=%%q
for /f "tokens=1,2,3 delims=:" %%r in ('echo !CTIME1!') do set HOURS1=%%r & set MINS1=%%s & set SECS1=%%t
set DSTAMPO=!HOURS1!!MINS1!!SECS1!
echo !DSTAMPO!  AMPM=!AMPM1!

if !AMPM1!==PM (if !HOURS1! EQU 01 (set HOURS2=13)
    if !HOURS1! EQU 02 (set HOURS2=14)
    if !HOURS1! EQU 03 (set HOURS2=15)
    if !HOURS1! EQU 04 (set HOURS2=16)
    if !HOURS1! EQU 05 (set HOURS2=17)
    if !HOURS1! EQU 06 (set HOURS2=18)
    if !HOURS1! EQU 07 (set HOURS2=19)
    if !HOURS1! EQU 08 (set HOURS2=20)
    if !HOURS1! EQU 09 (set HOURS2=21)
    if !HOURS1! EQU 10 (set HOURS2=22)
    if !HOURS1! EQU 11 (set HOURS2=23)) ELSE (if !HOURS1! EQU 12 (set HOURS2=00))

echo hrs1!HOURS1!---hrs2!HOURS2!---
set DSTAMP=!HOURS1!!MINS1!!SECS1!
echo !DSTAMP!
set DSTAMP2=!HOURS2!!MINS1!!SECS1!
echo !DSTAMP2!

@echo -------------------------------------------------)
ENDLOCAL

Thanks much for your help!!!

[foxidrive]

I have a .csv file that contains thousands lines similar to those:
CLB,,,18l,,20150922,,2015-09-23 11:26:00 AM,,,2015-09-23 11:26:01 AM,,,,trad,,1344,,cam549,,,PUR,,,X001,,,PCL

I am trying to convert AM/PM field4 and field5 to 24hrs entries.

This is the first five fields. Three of them are empty.

CLB,,,18l,,

See here: viewtopic.php?f=3&t=6108

[z9999]

foxidrive, I meant field4(2015-09-23 11:26:01 AM) and field5(2015-09-23 11:26:05 AM).
!HOURS1! is being assigned the correct data. !HOURS2! is always EMPTY.

[Squashman]

foxidrive, I meant field4(2015-09-23 11:26:01 AM) and field5(2015-09-23 11:26:05 AM).
!HOURS1! is being assigned the correct data. !HOURS2! is always EMPTY.

Looks like Field 8 and 11 to me.

Yes it is tokens 4 and 5 because the FOR /F command see consecutive delimiters as one. If you want to reassemble those fields into the proper order with the correct amount of delimiters you will need to make sure that all the fields are accounted for.

[Squashman]

You should be able to see the problem plain as day because you are echoing it to the screen.

Code: Select all

set DSTAMPO=!HOURS1!!MINS1!!SECS1!
echo !DSTAMPO!  AMPM=!AMPM1!

Output

Code: Select all

11 29 00   AMPM=PM

Where do you think those spaces are coming from.

[Squashman]

Shortened version with fixes and enhancements and indentation for readability!

Code: Select all

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION

for /F "eol=; tokens=1-11 delims=," %%a in (testing5.csv) do (
   set "CTS=%%d" & set "CTE=%%e"
   @echo CTS=!CTS! ---- CTE=!CTE!

   for /f "tokens=1,2,3 delims= " %%o in ('echo !CTS!') do set "CTIME1=%%p" & set "AMPM1=%%q"
   for /f "tokens=1,2,3 delims=:" %%r in ('echo !CTIME1!') do set "MINS1=%%s" & set "SECS1=%%t" & set "HOURS1=%%r"
   set DSTAMPO=!HOURS1!!MINS1!!SECS1!
   echo !DSTAMPO!  AMPM=!AMPM1!

   if !AMPM1!==PM (
      IF !HOURS1! LSS 12 SET /A HOURS2=1!HOURS1! + 12
      SET HOURS2=!HOURS2:~-2!
   ) ELSE (
      if !HOURS1! EQU 12 (set HOURS2=00)
   )

   echo hrs1!HOURS1!---hrs2!HOURS2!---
   set DSTAMP=!HOURS1!!MINS1!!SECS1!
   echo !DSTAMP!
   set DSTAMP2=!HOURS2!!MINS1!!SECS1!
   echo !DSTAMP2!

   @echo -------------------------------------------------
)
pause
ENDLOCAL

[z9999]

I am trying to understand why !HOURS2! is EMPTY, when !HOURS1! has a value for example 11.
I think that !HOURS1! is not 11, but is 11 and some special character.

In other words, IF I echo !HOURS1!, I get 11. (!HOURS! is assigned a value in the FOR loop)
IF I echo !HOURS2!, I get NOTHING (!HOURS2! is assigned a value in the IF statement)

which means that there is something in the IF statement that is not working. Probably it is !HOURS1!
which is EQUAL to "11 " and not "11" (11 and a special character)

[Squashman]

which means that there is something in the IF statement that is not working. Probably it is !HOURS1!
which is EQUAL to "11 " and not "11" (11 and a special character)

The IF statement is working just fine. The problem was your SET statements were adding a space to the end of each variable.
Hence "11 " DOES NOT EQUAL "11"

[Squashman]

I really dislike using the ampersand with multiple SET statements. Some people like to code that way but I like readability.
Change the formatting and added a check for HOURS2 being empty and setting it to HOURS1

Code: Select all

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION

for /F "eol=; tokens=1-11 delims=," %%a in (testing5.csv) do (
   set "CTS=%%d"
   set "CTE=%%e"
   @echo CTS=!CTS! ---- CTE=!CTE!

   for /f "tokens=1,2,3 delims= " %%o in ('echo !CTS!') do (
      set "CTIME1=%%p"
      set "AMPM1=%%q"
   )
   for /f "tokens=1,2,3 delims=:" %%r in ('echo !CTIME1!') do (
      set "MINS1=%%s"
      set "SECS1=%%t"
      set "HOURS1=%%r"
   )
   set DSTAMPO=!HOURS1!!MINS1!!SECS1!
   echo !DSTAMPO!  AMPM=!AMPM1!

   if !AMPM1!==PM (
      IF !HOURS1! LSS 12 SET /A HOURS2=1!HOURS1! + 12
      SET HOURS2=!HOURS2:~-2!
   ) ELSE (
      if !HOURS1! EQU 12 (set HOURS2=00)
   )

   IF "!HOURS2!"=="" set HOURS2=!HOURS1!
   echo hrs1!HOURS1!---hrs2!HOURS2!---
   set DSTAMP=!HOURS1!!MINS1!!SECS1!
   echo !DSTAMP!
   set DSTAMP2=!HOURS2!!MINS1!!SECS1!
   echo !DSTAMP2!

   @echo -------------------------------------------------
)
pause
ENDLOCAL

Output

Code: Select all

CTS=2015-09-23 11:26:00 AM ---- CTE=2015-09-23 11:26:01 AM
112600  AMPM=AM
hrs111---hrs211---
112600
112600
-------------------------------------------------
CTS=2015-09-23 11:26:01 AM ---- CTE=2015-09-23 11:26:05 AM
112601  AMPM=AM
hrs111---hrs211---
112601
112601
-------------------------------------------------
CTS=2015-09-24 11:29:00 PM ---- CTE=2015-09-24 11:29:12 PM
112900  AMPM=PM
hrs111---hrs223---
112900
232900
-------------------------------------------------

[TheHunterManX]

Are you trying to make it 24 hours/12 hours? if so try this code. This is a example.

Code: Select all

@echo off
Set time=0
set sec=0
set secnds=0
set min=0
set hour=0
set day=0
set year=0
set Type=0
set am=0
echo Set time(a for 12 hour, b for 24 hour). If it is wrong, the default is 24.
set /p Input=Type here: 
set Type=%Input%
if %Type% EQU a set am=1
goto Test
:Test
set /a time=%time%+1
if %time% EQU 3 goto Second
if %sec% EQU 60 goto Min
if %min% EQU 60 goto Hour
goto Test
:Second
cls
set /a sec=%sec%+15
set /a time=0
echo %day%:%hour%:%min%:%sec%:%time%
goto Test
:Min
set /a min=%min%+10
set /a sec=0
goto Test
:Hour
set /a hour=%hour%+1
set /a min=0
if %hour% EQU 13 if %am% EQU 1 goto Hour1
if %hour% EQU 12 if %Type% EQU a if %am% EQU 0 goto Day
if %hour% EQU 24 if %am% EQU 0 goto Day
goto Test
:Day
set /a day=%day%+1
set /a hour=0
if %Type% EQU a set am=1
goto Test
:Hour1
set /a hour=0
set /a min=0
set /a am=0
goto Test

[Squashman]

Are you trying to make it 24 hours/12 hours? if so try this code. This is a example.

No.