Return to Answer

You did mention:

I'm trying to change the date format in the second field. I should also add the second field is sometimes not populated at all.

The following awk script will satisfy the requirement. Save it as date.awk (thanks to @EdMorton for the nitpicks) :

BEGIN {
  FS = OFS =","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

{
  if ($2 =! "") {
    split($2, date, / /)
 
    month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
    $2 = sprintf("%04d-%02d-%02d %s", date[6], month, date[3], date[4])
  }

  print
}

then execute awk using the script:

awk -f date.awk input.csv

Original Answer

You can easily change date formats using date command. For instance:

$ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

Then, you can use awk to convert only a specific column (in this example, $1):

awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

The result will be in your local time, so if you want to convert the timezone, then simply add TZ=EDT (or any timezone) before date.

However, as per @StéphaneChazelas mentioned in the comments, this is vulnerable to command injection if somehow the field in a line contains malicious command, and this will run slowly because it needs to execute sh and date for each line.

You did mention:

I'm trying to change the date format in the second field. I should also add the second field is sometimes not populated at all.

The following awk script will satisfy the requirement. Save it as date.awk (thanks to @EdMorton for the nitpicks) :

BEGIN {
  FS = OFS = ","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

$2 != "" {
  split($2, date, / /)
  month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
  $2 = sprintf("%04d-%02d-%02d %s", date[6], month, date[3], date[4])
}

1

then execute awk using the script:

awk -f date.awk input.csv

Original Answer

You can easily change date formats using date command. For instance:

$ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

Then, you can use awk to convert only a specific column (in this example, $1):

awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

The result will be in your local time, so if you want to convert the timezone, then simply add TZ=EDT (or any timezone) before date.

However, as per @StéphaneChazelas mentioned in the comments, this is vulnerable to command injection if somehow the field in a line contains malicious command, and this will run slowly because it needs to execute sh and date for each line.

You did mention:

I'm trying to change the date format in the second field. I should also add the second field is sometimes not populated at all.

The following awk script will satisfy the requirement. Save it as date.awk (thanks to @EdMorton for the nitpicks) :

BEGIN {
  FS = OFS =","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

$2 != "" {
  split($2, date, / /)

  month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
  $2 = sprintf("%04d-%02d-%02d %s", date[6], month, date[3], date[4])

  print
}

then execute awk using the script:

awk -f date.awk input.csv

Original Answer

You can easily change date formats using date command. For instance:

$ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

Then, you can use awk to convert only a specific column (in this example, $1):

awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

The result will be in your local time, so if you want to convert the timezone, then simply add TZ=EDT (or any timezone) before date.

However, as per @StéphaneChazelas mentioned in the comments, this is vulnerable to command injection if somehow the field in a line contains malicious command, and this will run slowly because it needs to execute sh and date for each line.

You did mention:

I'm trying to change the date format in the second field. I should also add the second field is sometimes not populated at all.

The following awk script will satisfy the requirement. Save it as date.awk (thanks to @EdMorton for the nitpicks) :

BEGIN {
  FS = OFS =","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

{
  if ($2 =! "") {
    split($2, date, / /)

    month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
    $2 = sprintf("%04d-%02d-%02d %s", date[6], month, date[3], date[4])
  }

  print
}

then execute awk using the script:

awk -f date.awk input.csv

Original Answer

You can easily change date formats using date command. For instance:

$ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

Then, you can use awk to convert only a specific column (in this example, $1):

awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

The result will be in your local time, so if you want to convert the timezone, then simply add TZ=EDT (or any timezone) before date.

However, as per @StéphaneChazelas mentioned in the comments, this is vulnerable to command injection if somehow the field in a line contains malicious command, and this will run slowly because it needs to execute sh and date for each line.

You can use awk to do the reformatting. Save the following awk script as date.awk :

BEGIN {
  FS = OFS =","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

$2 != "" {
  split($2, date, / /)

  month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
  day = sprintf("%02d", date[3])
  time = date[4]

  $2 = date[6] "-" month "-" day " " time
  print
}

then execute awk using the script:

awk -f date.awk input.csv

Original Answer

You can easily change date formats using date command. For instance:

$ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

Then, you can use awk to convert only a specific column (in this example, $1):

awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

The result will be in your local time, so if you want to convert the timezone, then simply add TZ=EDT (or any timezone) before date.

However, as per @StéphaneChazelas mentioned in the comments, this is vulnerable to command injection if somehow the field in a line contains malicious command, and this will run slowly because it needs to execute sh and date for each line.

You did mention:

I'm trying to change the date format in the second field. I should also add the second field is sometimes not populated at all.

The following awk script will satisfy the requirement. Save it as date.awk (thanks to @EdMorton for the nitpicks) :

BEGIN {
  FS = OFS =","
  months = "JanFebMarAprMayJunJulAugSepOctNovDec" 
}

$2 != "" {
  split($2, date, / /)

  month = sprintf("%02d", (index(months, date[2]) + 2) / 3)
  $2 = sprintf("%04d-%02d-%02d %s", date[6], month, date[3], date[4])

  print
}

then execute awk using the script:

awk -f date.awk input.csv

Original Answer

You can easily change date formats using date command. For instance:

$ date -d "Mon Jul 03 14:48:54 EDT 2023" +"%Y-%m-%d %H:%M:%S"
2023-07-03 14:48:54

Then, you can use awk to convert only a specific column (in this example, $1):

awk 'BEGIN {FS=OFS=","} {"date -d \"" $1 "\" +\"%Y-%m-%d %H:%M:%S\"" | getline res; $1=res; print}' file.csv

The result will be in your local time, so if you want to convert the timezone, then simply add TZ=EDT (or any timezone) before date.

However, as per @StéphaneChazelas mentioned in the comments, this is vulnerable to command injection if somehow the field in a line contains malicious command, and this will run slowly because it needs to execute sh and date for each line.