Changing the format of a date in a column of a comma delimited file .csv in unix
OfficialBenWhite
I need a unix command that would convert the following .csv table using("," rather than ,) from:
.. to
I think an awk or sed command will be able to do it but I can't figure it out.
For those who want to see the raw csv:
"Area","Locale","Date","Prefix","INT","Cap","CF","loss","Fall","WR"
"base","NewYork","20150331","100","0","3.225","3.225","0","0","0"
"base","NewYork","20150930","100","0","3.225","3.225","0","0","0"
"base","NewYork","20160331","100","0","3.225","3.225","0","0","0"
"base","NewYork","20160930","100","0","3.225","3.225","0","0","0"
"base","NewYork","20170331","100","0","3.475","3.475","0","0","0"
"base","NewYork","20170929","100","0","3.475","3.475","0","0","0"
"base","NewYork","20180329","100","0","3.475","3.475","0","0","0"
"base","NewYork","20180928","100","0","3.475","3.475","0","0","0"
"base","NewYork","20190329","100","0","3.475","3.475","0","0","0"
to
"Area","Locale","Date","Prefix","INT","Cap","CF","loss","Fall","WR"
"base","NewYork","3/31/2015","100","0","3.225","3.225","0","0","0"
"base","NewYork","9/30/2015","100","0","3.225","3.225","0","0","0"
"base","NewYork","3/31/2016","100","0","3.225","3.225","0","0","0"
"base","NewYork","9/30/2016","100","0","3.225","3.225","0","0","0"
"base","NewYork","3/31/2017","100","0","3.475","3.475","0","0","0"
"base","NewYork","9/29/2017","100","0","3.475","3.475","0","0","0"
"base","NewYork","3/29/2018","100","0","3.475","3.475","0","0","0"
"base","NewYork","9/28/2018","100","0","3.475","3.475","0","0","0"
"base","NewYork","3/29/2019","100","0","3.475","3.475","0","0","0"
Avinash Raj
Through Perl.
$ perl -pe 's~^([^,]*,[^,]*,")(\d{4})(0([1-9])|(1[012]))(\d{2})"~\1\4\5/\6/\2"~' file
"Area","Locale","Date","Prefix","INT","Cap","CF","loss","Fall","WR"
"base","NewYork","3/31/2015","100","0","3.225","3.225","0","0","0"
"base","NewYork","9/30/2015","100","0","3.225","3.225","0","0","0"
"base","NewYork","3/31/2016","100","0","3.225","3.225","0","0","0"
"base","NewYork","9/30/2016","100","0","3.225","3.225","0","0","0"
"base","NewYork","3/31/2017","100","0","3.475","3.475","0","0","0"
"base","NewYork","9/29/2017","100","0","3.475","3.475","0","0","0"
"base","NewYork","3/29/2018","100","0","3.475","3.475","0","0","0"
"base","NewYork","9/28/2018","100","0","3.475","3.475","0","0","0"
"base","NewYork","3/29/2019","100","0","3.475","3.475","0","0","0"
"base","NewYork","12/29/2019","100","0","3.475","3.475","0","0","0"
Through sed,
I just replaced \d in the above Perl regex to [0-9] because sed won't support \d notation.
$ sed -r 's~^([^,]*,[^,]*,")([0-9]{4})(0([1-9])|(1[012]))([0-9]{2})"~\1\4\5/\6/\2"~' file
"Area","Locale","Date","Prefix","INT","Cap","CF","loss","Fall","WR"
"base","NewYork","3/31/2015","100","0","3.225","3.225","0","0","0"
"base","NewYork","9/30/2015","100","0","3.225","3.225","0","0","0"
"base","NewYork","3/31/2016","100","0","3.225","3.225","0","0","0"
"base","NewYork","9/30/2016","100","0","3.225","3.225","0","0","0"
"base","NewYork","3/31/2017","100","0","3.475","3.475","0","0","0"
"base","NewYork","9/29/2017","100","0","3.475","3.475","0","0","0"
"base","NewYork","3/29/2018","100","0","3.475","3.475","0","0","0"
"base","NewYork","9/28/2018","100","0","3.475","3.475","0","0","0"
"base","NewYork","3/29/2019","100","0","3.475","3.475","0","0","0"
"base","NewYork","12/29/2019","100","0","3.475","3.475","0","0","0"
Collected from the Internet
Please contact [email protected] to delete if infringement.
edited at
- Prev: Formatting JSON object and adding new keys to the structure
- Next: adding and removing a stylesheet dynamcally
Related
Related Related
- 1
Sorting numerically in a comma-delimited file with Unix
- 2
Changing the date format within a CSV file
- 3
remove the last column from a comma delimited file
- 4
Changing the date format of the file
- 5
R read comma delimited txt file with comma inside one column
- 6
Need to convert Fixed Width File to 'Comma' delimited in unix
- 7
Need to convert Fixed Width File to 'Comma' delimited in unix
- 8
Format Date fields in Pipe Delimited File
- 9
replace first column of space delimited file with nth column of comma delimited file
- 10
reading a comma-delimited file with a date object and a float with Python
- 11
reading a comma-delimited file with a date object and a float with Python
- 12
c++ csv comma delimited file entries, trouble with mixed types
- 13
Process comma delimited CSV file into multiple files with ASP.NET
- 14
Add double quotes in .CSV comma delimited file using awk
- 15
Changing date format in file name
- 16
How to store nth column of comma delimited file in an array?
- 17
How to store nth column of comma delimited file in an array?
- 18
Format a csv date column using mysql load data in file
- 19
Changing column value in CSV file
- 20
Parsing a csv comma delimited with quotes
- 21
MySQL: Changing column type and date time format
- 22
Changing last entries in a comma delimited list
- 23
How to convert pipe-delimited file to CSV format in bash?
- 24
Date and time of a file in Unix in a desired format
- 25
Changing Date format and appending to original file
- 26
Changing the Date Format Displayed from a Text File
- 27
Handling errors in AHK and changing format of csv file
- 28
Handling errors in AHK and changing format of csv file
- 29
How to format a file in csv format after the first comma in a line?
Comments