3.19 mdformat Extract Date Time

CSV data that was exported from other systems often contains forward slash and colon symbol in date columns, in addition, date and time are stored in single digit (Example: 2014/7 / 18 1:57). Sorting and range specification processing on these kind of items in MCMD is not possible.

For ease of processing data and time formatted data Date Time Format, the mdformat command extracts the date, hour, minute, and second on fields specified in f= parameter according to the format specified in the c= parameter.

Format

mdformat c= f= [-A] [i=] [o=] [-nfn] [-nfno] [-x] [--help] [--version]

Parameters

`f=`	Specify the field name for extraction (multiple items can be specified).
`c=`	Specify string format according to the specified format.
`-A`	Specify the new column name and save output results in new column.

Conversion Specification of Character Format

Table 3.2 shows the possible character conversion formats that can be defined in c= parameter.

Table 3.2: Conversion characters

Conversion Characters	Description
%Y	Number representing year (4 digits)
%y	Number representing year (2 digits)
%m	Number representing month (2 digits)
%d	Number representing day (2 digits)
%H	Time (2 digits)
%M	Minute (2 digits)
%S	Second (2 digits)

Examples

Example 1: Basic Example

Extract and convert time and date information from fld field. Save the converted format as "a:yearmonthday:b:timeminutesecond", by specifying "a:%Y%m%d:b:%H%M%S" in the c= parameter.

$ more dat1.csv
fld
a:20120304:b:121212
a:20101204:b:011309
$ mdformat f=fld c=a:%Y%m%d:b:%H%M%S i=dat1.csv o=rsl1.csv
#END# kgdformat c=a:%Y%m%d:b:%H%M%S f=fld i=dat1.csv o=rsl1.csv
$ more rsl1.csv
fld
20120304121212
20101204011309

Example 2: Add Results to New Column

Store results in fld2 from format conversion in fld1 field, specify the format by "%Y/%m/%d" in c= parameter. Use -A option to save results in f2 field.

$ more dat2.csv
fld,fld2
2010/11/20,2010/11/21
2010/1/1,2010/1/2
2011/01/01,2010/01/02
2010/1/01,2010/1/02
$ mdformat f=fld:f1,fld2:f2 c=%Y/%m/%d i=dat2.csv -A o=rsl2.csv
#END# kgdformat -A c=%Y/%m/%d f=fld:f1,fld2:f2 i=dat2.csv o=rsl2.csv
$ more rsl2.csv
fld,fld2,f1,f2
2010/11/20,2010/11/21,20101120,20101121
2010/1/1,2010/1/2,20100101,20100102
2011/01/01,2010/01/02,20110101,20100102
2010/1/01,2010/1/02,20100101,20100102

Example 3: Case of failed extraction

The date format in fld field is saved as "Year Month Day Time:Minute:Second", "%Y %m %d %H:%M:%S" is specified in c= parameter. However, it failed since the format is different in different rows.

$ more dat3.csv
fld
2010 11 20 12:34:56

2011 01 01 23:34:56
2010  1 01 123455
$ mdformat f=fld:f1 c='%Y %m %d %H:%M:%S' i=dat3.csv -A o=rsl3.csv
#END# kgdformat -A c=%Y %m %d %H:%M:%S f=fld:f1 i=dat3.csv o=rsl3.csv
$ more rsl3.csv
fld,f1
2010 11 20 12:34:56,20101120123456
,
2011 01 01 23:34:56,20110101233456
2010  1 01 123455,