Repository: wireservice/csvkit

Issue:
Support date format hints
Excel will output CSVs with dates in the user's locale, so a British user will see dates in the DD/MM/YYYY format, whilst an American user would see MM/DD/YYYY.

Currently it appears that csvkit (although I've only really been using csvsql) will always try MM/DD/YYYY first, and if that fails to parse, then it will fall back to DD/MM/YYYY.

The problem with this approach arises when you're using a DD/MM/YYYY formatted sheet and you have ambiguity in some dates. For example:

02/01/2014
31/12/2012

This will produce dates in the database of:

2014-01-02 (incorrectly parsed as MM/DD/YYYY)
2012-12-31

So you silently end up with a mixture of correct and incorrect dates, which is not ideal!

Ideally there'd be an option one could pass to csvkit programs to specify a preference list for parsing dates. It'd be overkill to specify every format (I think), so having some abbreviations would suffice. For example:

--date-formats "dmy,mdy"

The default date parsing schema would then follow after this if the formats in the list did not match successfully.

An extension to this may be to try to infer the date format by examining all rows globally and selecting the format that successfully parses them all (of course, this is still no guarantee of success as people may _only_ have ambiguous dates present).


Discussion:

Patch:
diff --git a/CHANGELOG.rst b/CHANGELOG.rst
index 9dc61c3..98ccd17 100644
--- a/CHANGELOG.rst
+++ b/CHANGELOG.rst
@@ -6,6 +6,8 @@ Improvements:
 * Add a :code:`--version` flag.
 * Add a :code:`--skip-lines` option to skip initial lines (e.g. comments, copyright notices, empty rows).
 * Add a :code:`--locale` option to set the locale of any formatted numbers.
+* Add a :code:`--date-format` option to set a strptime date format string.
+* Add a :code:`--datetime-format` option to set a strptime datetime format string.
 * :code:`-I` is the short option for :code:`--no-inference`.
 * :doc:`/scripts/csvjoin` supports :code:`--snifflimit` and :code:`--no-inference`.
 * :doc:`/scripts/csvstat` adds a :code:`--freq-count` option to set the maximum number of frequent values to display.
diff --git a/csvkit/cli.py b/csvkit/cli.py
index fdd77d4..57e7659 100644
--- a/csvkit/cli.py
+++ b/csvkit/cli.py
@@ -166,6 +166,12 @@ class CSVKitUtility(object):
         if 'S' not in self.override_flags:
             self.argparser.add_argument('-S', '--skipinitialspace', dest='skipinitialspace', action='store_true',
                                         help='Ignore whitespace immediately following the delimiter.')
+        if 'date-format' not in self.override_flags:
+            self.argparser.add_argument('--date-format', dest='date_format',
+                                        help='Specify a strptime date format string like "%%m/%%d/%%Y".')
+        if 'datetime-format' not in self.override_flags:
+            self.argparser.add_argument('--datetime-format', dest='datetime_format',
+                                        help='Specify a strptime datetime format string like "%%m/%%d/%%Y %%I:%%M %%p".')
         if 'H' not in self.override_flags:
             self.argparser.add_argument('-H', '--no-header-row', dest='no_header_row', action='store_true',
                                         help='Specify that the input CSV file has no header row. Will create default headers (a,b,c,...).')
@@ -288,8 +294,8 @@ class CSVKitUtility(object):
                 agate.Boolean(),
                 agate.Number(locale=self.args.locale),
                 agate.TimeDelta(),
-                agate.Date(),
-                agate.DateTime(),
+                agate.Date(date_format=self.args.date_format),
+                agate.DateTime(datetime_format=self.args.datetime_format),
                 text_type
             ])
 
diff --git a/csvkit/utilities/csvclean.py b/csvkit/utilities/csvclean.py
index 36b4aa5..e94e7ab 100644
--- a/csvkit/utilities/csvclean.py
+++ b/csvkit/utilities/csvclean.py
@@ -11,7 +11,7 @@ from csvkit.cleanup import RowChecker
 
 class CSVClean(CSVKitUtility):
     description = 'Fix common errors in a CSV file.'
-    override_flags = ['H', 'L']
+    override_flags = ['H', 'L', 'date-format', 'datetime-format']
 
     def add_arguments(self):
         self.argparser.add_argument('-n', '--dry-run', dest='dryrun', action='store_true',
diff --git a/csvkit/utilities/csvcut.py b/csvkit/utilities/csvcut.py
index a168c03..ed3dc84 100644
--- a/csvkit/utilities/csvcut.py
+++ b/csvkit/utilities/csvcut.py
@@ -16,7 +16,7 @@ from csvkit.cli import CSVKitUtility
 
 class CSVCut(CSVKitUtility):
     description = 'Filter and truncate CSV files. Like the Unix "cut" command, but for tabular data.'
-    override_flags = ['L']
+    override_flags = ['L', 'date-format', 'datetime-format']
 
     def add_arguments(self):
         self.argparser.add_argument('-n', '--names', dest='names_only', action='store_true',
diff --git a/csvkit/utilities/csvformat.py b/csvkit/utilities/csvformat.py
index 8228703..10c2242 100644
--- a/csvkit/utilities/csvformat.py
+++ b/csvkit/utilities/csvformat.py
@@ -7,7 +7,7 @@ from csvkit.cli import CSVKitUtility
 
 class CSVFormat(CSVKitUtility):
     description = 'Convert a CSV file to a custom output format.'
-    override_flags = ['l', 'zero', 'H', 'L']
+    override_flags = ['l', 'zero', 'H', 'L', 'date-format', 'datetime-format']
 
     def add_arguments(self):
         self.argparser.add_argument('-D', '--out-delimiter', dest='out_delimiter',
diff --git a/csvkit/utilities/csvgrep.py b/csvkit/utilities/csvgrep.py
index 02792ce..b2c013e 100644
--- a/csvkit/utilities/csvgrep.py
+++ b/csvkit/utilities/csvgrep.py
@@ -11,7 +11,7 @@ from csvkit.grep import FilteringCSVReader
 
 class CSVGrep(CSVKitUtility):
     description = 'Search CSV files. Like the Unix "grep" command, but for tabular data.'
-    override_flags = ['L']
+    override_flags = ['L', 'date-format', 'datetime-format']
 
     def add_arguments(self):
         self.argparser.add_argument('-n', '--names', dest='names_only', action='store_true',
diff --git a/csvkit/utilities/csvjson.py b/csvkit/utilities/csvjson.py
index 63d1689..9d50e83 100644
--- a/csvkit/utilities/csvjson.py
+++ b/csvkit/utilities/csvjson.py
@@ -37,7 +37,7 @@ class CSVJSON(CSVKitUtility):
         self.argparser.add_argument('-y', '--snifflimit', dest='sniff_limit', type=int,
                                     help='Limit CSV dialect sniffing to the specified number of bytes. Specify "0" to disable sniffing entirely.')
         self.argparser.add_argument('-I', '--no-inference', dest='no_inference', action='store_true',
-                                    help='Disable type inference when parsing CSV input.')
+                                    help='Disable type inference (and --locale, --date-format, --datetime-format) when parsing CSV input.')
 
     def main(self):
         # We need to do this dance here, because we aren't writing through agate.
diff --git a/csvkit/utilities/csvstack.py b/csvkit/utilities/csvstack.py
index a9a620d..bf1c00b 100644
--- a/csvkit/utilities/csvstack.py
+++ b/csvkit/utilities/csvstack.py
@@ -9,7 +9,7 @@ from csvkit.cli import CSVKitUtility, make_default_headers
 
 class CSVStack(CSVKitUtility):
     description = 'Stack up the rows from multiple CSV files, optionally adding a grouping value.'
-    override_flags = ['f', 'K', 'L']
+    override_flags = ['f', 'K', 'L', 'date-format', 'datetime-format']
 
     def add_arguments(self):
         self.argparser.add_argument(metavar="FILE", nargs='+', dest='input_paths', default=['-'],
diff --git a/csvkit/utilities/csvstat.py b/csvkit/utilities/csvstat.py
index 9bac46f..8a31720 100644
--- a/csvkit/utilities/csvstat.py
+++ b/csvkit/utilities/csvstat.py
@@ -63,7 +63,7 @@ OPERATIONS = OrderedDict([
 
 class CSVStat(CSVKitUtility):
     description = 'Print descriptive statistics for each column in a CSV file.'
-    override_flags = ['l', 'L']
+    override_flags = ['l', 'L', 'date-format', 'datetime-format']
 
     def add_arguments(self):
         self.argparser.add_argument('--csv', dest='csv_output', action='store_true',
diff --git a/csvkit/utilities/in2csv.py b/csvkit/utilities/in2csv.py
index 7a4bc3c..08e7787 100644
--- a/csvkit/utilities/in2csv.py
+++ b/csvkit/utilities/in2csv.py
@@ -35,7 +35,7 @@ class In2CSV(CSVKitUtility):
         self.argparser.add_argument('-y', '--snifflimit', dest='sniff_limit', type=int,
                                     help='Limit CSV dialect sniffing to the specified number of bytes. Specify "0" to disable sniffing entirely.')
         self.argparser.add_argument('-I', '--no-inference', dest='no_inference', action='store_true',
-                                    help='Disable type inference when parsing CSV input.')
+                                    help='Disable type inference (and --locale, --date-format, --datetime-format) when parsing CSV input.')
 
     def main(self):
         # Determine the file type.
diff --git a/csvkit/utilities/sql2csv.py b/csvkit/utilities/sql2csv.py
index 1b56f52..c16fed3 100644
--- a/csvkit/utilities/sql2csv.py
+++ b/csvkit/utilities/sql2csv.py
@@ -10,7 +10,7 @@ from csvkit.cli import CSVKitUtility
 
 class SQL2CSV(CSVKitUtility):
     description = 'Execute an SQL query on a database and output the result to a CSV file.'
-    override_flags = 'f,b,d,e,H,K,L,p,q,S,t,u,z,zero'.split(',')
+    override_flags = 'f,b,d,e,H,K,L,p,q,S,t,u,z,date-format,datetime-format,zero'.split(',')
 
     def add_arguments(self):
         self.argparser.add_argument('--db', dest='connection_string', default='sqlite://',
diff --git a/docs/common_arguments.rst b/docs/common_arguments.rst
index 337ac0a..79edf46 100644
--- a/docs/common_arguments.rst
+++ b/docs/common_arguments.rst
@@ -29,6 +29,11 @@ All tools which accept CSV as input share a set of common command-line arguments
                         Specify the locale (en_US) of any formatted numbers.
   -S, --skipinitialspace
                         Ignore whitespace immediately following the delimiter.
+  --date-format DATE_FORMAT
+                        Specify a strptime date format string like "%m/%d/%Y".
+  --datetime-format DATETIME_FORMAT
+                        Specify a strptime datetime format string like
+                        "%m/%d/%Y %I:%M %p".
   -H, --no-header-row   Specify that the input CSV file has no header row.
                         Will create default headers (A,B,C,...).
   -L SKIP_LINES, --skip-lines SKIP_LINES


Test patch:
diff --git a/examples/test_date_format.csv b/examples/test_date_format.csv
new file mode 100644
index 0000000..8472a28
--- /dev/null
+++ b/examples/test_date_format.csv
@@ -0,0 +1,3 @@
+a
+02/01/2014
+31/12/2012
diff --git a/examples/test_date_format_converted.csv b/examples/test_date_format_converted.csv
new file mode 100644
index 0000000..4e6418b
--- /dev/null
+++ b/examples/test_date_format_converted.csv
@@ -0,0 +1,3 @@
+a
+2014-01-02
+2012-12-31
diff --git a/tests/test_utilities/test_in2csv.py b/tests/test_utilities/test_in2csv.py
index 753bae4..bac977f 100644
--- a/tests/test_utilities/test_in2csv.py
+++ b/tests/test_utilities/test_in2csv.py
@@ -37,6 +37,9 @@ class TestIn2CSV(CSVKitTestCase, EmptyFileTests):
     def test_locale(self):
         self.assertConverted('csv', 'examples/test_locale.csv', 'examples/test_locale_converted.csv', ['--locale', 'de_DE'])
 
+    def test_date_format(self):
+        self.assertConverted('csv', 'examples/test_date_format.csv', 'examples/test_date_format_converted.csv', ['--date-format', '%d/%m/%Y'])
+
     def test_convert_csv(self):
         self.assertConverted('csv', 'examples/testfixed_converted.csv', 'examples/testfixed_converted.csv')