-1

I have a text file (.txt) that contains multiple rows of text in the following format:

ABCD|TEST|123456|12/10/2017 5:41|Sample|CODENOTE123|Comment text|11/27/2017|12383697

As it can be seen, there are 9 columns separated by the pipe | operator. I need to edit the time, i.e. 5:14, and convert it to 05:14. If the hour has already two digits, no changes need to be made to the row. The date and time will always be the 4th column element. The line after editing should look like as follows:

ABCD|TEST|123456|12/10/2017 05:41|Sample|CODENOTE123|Comment text|11/27/2017|12383697

This editing has to be done for each row in the text file and a new text file has to be created with all the rows containing the correct time format.

PS: The number of columns (separated by the pipe operator) can be between 9 to 14, but the date and time will always appear in the 4th column.

Mofi
  • 46,139
  • 17
  • 80
  • 143
sinhar303
  • 11
  • 6
  • 1
    So what's your *question*? SO isn't here to write this for you. – jonrsharpe Jan 01 '18 at 13:07
  • Please note that https://stackoverflow.com is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. You should also read [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask). – DavidPostill Jan 01 '18 at 14:30

1 Answers1

0

The Windows command interpreter executing a batch file is designed for running commands and executables, but not for editing text or binary files. Therefore nearly all other scripting or programming languages are better for this CSV file editing task.

How can you find and replace text in a file using the Windows command-line environment? contains lots of solutions for searching and replacing strings in a file mainly using applications or other scripting languages.

One of the provided solutions is JREPL.BAT written by Dave Benham which is a batch file / JScript hybrid. Microsoft's JScript supports regular expression replaces. So whenever a simple regular expression search and replace in Perl like regex syntax can be performed on a text file with a text editor, it can be done usually also with jrepl.bat from within a batch file.

The batch code below expects jrepl.bat in same directory as the batch file containing the posted command lines. The file to modify is specified twice in this batch file with name DataFile.csv.

@echo off
if not exist "DataFile.csv" goto :EOF
if not exist "%~dp0jrepl.bat" goto :EOF

call "%~dp0jrepl.bat" "(\|[01][0-9]/[0-3][0-9]/(?:19|20)[0-9][0-9]) (?=[0-9]:[0-5][0-9]\|)" "$1 0" /F "DataFile.csv" /O -

The search expression is written to find a date with time between two | in format MM/DD/YYYY h:mm and inserts 0 after the space character before single digit hour. Century of year must be 19 or 20.

The search string finds the date/time in this format anywhere in line because I think this is better for the future in case of date/time string ever changes its field position in the line. It would be also possible to limit this search/replace to fourth | delimited field value with the search string:

^((?:[^|]*\|){3}[01][0-9]/[0-3][0-9]/(?:19|20)[0-9][0-9]) (?=[0-9]:[0-5][0-9]\|)

DataFile.csv is modified directly by this batch script. Replacing - at end of jrepl.bat command line by a file name produces a new file being a copy of DataFile.csv with all hour values converted to two digit values.

Mofi
  • 46,139
  • 17
  • 80
  • 143