Repository: wireservice/csvkit

Issue:
Alpha2 ISO country code for Namibia (NA) is silently discarded
This is quite unexpected. Probably related to #49.

Minimal example:

### alpha2.csv

```
name,ISO3166-1-Alpha-2
Namibia,NA
```

### csvstat: `Most common values:    None`

```
$ csvstat alpha2.csv 
  1. "name"

	Type of data:          Text
	Contains null values:  False
	Unique values:         1
	Most common values:    Namibia (1x)

  2. "ISO3166-1-Alpha-2"

	Type of data:          Boolean
	Contains null values:  True (excluded from calculations)
	Unique values:         1
	Most common values:    None (1x)

Row count: 1
```

### csvjoin: `"NA"` is silently replaced with `""`

```
$ csvjoin -c1,1 alpha2.csv alpha2.csv 
name,ISO3166-1-Alpha-2,ISO3166-1-Alpha-22
Namibia,,
```

Discussion:

Patch:
diff --git a/CHANGELOG.rst b/CHANGELOG.rst
index a05c7b8..46b328c 100644
--- a/CHANGELOG.rst
+++ b/CHANGELOG.rst
@@ -8,6 +8,7 @@ Improvements:
 * 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.
+* Make :code:`--blanks` a common argument across all tools.
 * :code:`-I` is the short option for :code:`--no-inference`.
 * :doc:`/scripts/csvclean`, :doc:`/scripts/csvjson`, :doc:`/scripts/csvpy` support :code:`--no-header-row`.
 * :doc:`/scripts/csvclean` is faster and no longer requires exponential time in the worst case.
diff --git a/csvkit/cli.py b/csvkit/cli.py
index 5e5a092..23a8ce7 100644
--- a/csvkit/cli.py
+++ b/csvkit/cli.py
@@ -167,6 +167,9 @@ 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 'blanks' not in self.override_flags:
+            self.argparser.add_argument('--blanks', dest='blanks', action='store_true',
+                                        help='Do not convert "", "na", "n/a", "none", "null", "." to NULL.')
         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".')
@@ -194,7 +197,7 @@ class CSVKitUtility(object):
                                         help='When interpreting or displaying column numbers, use zero-based numbering instead of the default 1-based numbering.')
 
         self.argparser.add_argument('-V', '--version', action='version', version='%(prog)s 1.0.2',
-                                        help='Display version information and exit.')
+                                    help='Display version information and exit.')
 
     def _open_input_file(self, path):
         """
@@ -284,21 +287,22 @@ class CSVKitUtility(object):
 
     def get_column_types(self):
         if getattr(self.args, 'blanks', None):
-            text_type = agate.Text(cast_nulls=False)
+            type_kwargs = {'null_values': ()}
         else:
-            text_type = agate.Text()
+            type_kwargs = {}
 
-        if self.args.no_inference:
-            return agate.TypeTester(types=[text_type])
-        else:
-            return agate.TypeTester(types=[
-                agate.Boolean(),
-                agate.Number(locale=self.args.locale),
-                agate.TimeDelta(),
-                agate.Date(date_format=self.args.date_format),
-                agate.DateTime(datetime_format=self.args.datetime_format),
-                text_type
-            ])
+        types = [agate.Text(**type_kwargs)]
+
+        if not self.args.no_inference:
+            types = [
+                agate.Boolean(**type_kwargs),
+                agate.Number(locale=self.args.locale, **type_kwargs),
+                agate.TimeDelta(**type_kwargs),
+                agate.Date(date_format=self.args.date_format, **type_kwargs),
+                agate.DateTime(datetime_format=self.args.datetime_format, **type_kwargs),
+            ] + types
+
+        return agate.TypeTester(types=types)
 
     def get_column_offset(self):
         if self.args.zero_based:
diff --git a/csvkit/utilities/csvcut.py b/csvkit/utilities/csvcut.py
index ed3dc84..f4460d2 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', 'date-format', 'datetime-format']
+    override_flags = ['L', 'blanks', '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/csvgrep.py b/csvkit/utilities/csvgrep.py
index b2c013e..bec40b1 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', 'date-format', 'datetime-format']
+    override_flags = ['L', 'blanks', '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/csvsql.py b/csvkit/utilities/csvsql.py
index 77e1757..5f86a02 100644
--- a/csvkit/utilities/csvsql.py
+++ b/csvkit/utilities/csvsql.py
@@ -40,8 +40,6 @@ class CSVSQL(CSVKitUtility):
                                     help='Skip creating a table. Only valid when --insert is specified.')
         self.argparser.add_argument('--overwrite', dest='overwrite', action='store_true',
                                     help='Drop the table before creating.')
-        self.argparser.add_argument('--blanks', dest='blanks', action='store_true',
-                                    help='Do not coerce empty strings to NULL values.')
         self.argparser.add_argument('--db-schema', dest='db_schema',
                                     help='Optional name of database schema to create table(s) in.')
         self.argparser.add_argument('-y', '--snifflimit', dest='sniff_limit', type=int,
diff --git a/csvkit/utilities/csvstack.py b/csvkit/utilities/csvstack.py
index f32f514..56e2051 100644
--- a/csvkit/utilities/csvstack.py
+++ b/csvkit/utilities/csvstack.py
@@ -9,7 +9,8 @@ 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', 'L', 'date-format', 'datetime-format']
+    # Override 'f' because the utility accepts multiple files.
+    override_flags = ['f', 'L', 'blanks', 'date-format', 'datetime-format']
 
     def add_arguments(self):
         self.argparser.add_argument(metavar="FILE", nargs='+', dest='input_paths', default=['-'],
diff --git a/csvkit/utilities/sql2csv.py b/csvkit/utilities/sql2csv.py
index 4e3d24e..9533bb2 100644
--- a/csvkit/utilities/sql2csv.py
+++ b/csvkit/utilities/sql2csv.py
@@ -8,7 +8,8 @@ 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,date-format,datetime-format,zero'.split(',')
+    # Overrides all flags except --linenumbers, --verbose, --version.
+    override_flags = 'f,b,d,e,H,K,L,p,q,S,t,u,z,blanks,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 79edf46..1290980 100644
--- a/docs/common_arguments.rst
+++ b/docs/common_arguments.rst
@@ -29,6 +29,8 @@ 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.
+  --blanks              Do not coerce empty, "na", "n/a", "none", "null", "."
+                        strings to NULL values.
   --date-format DATE_FORMAT
                         Specify a strptime date format string like "%m/%d/%Y".
   --datetime-format DATETIME_FORMAT
diff --git a/docs/scripts/csvsql.rst b/docs/scripts/csvsql.rst
index ed62c05..b7154f6 100644
--- a/docs/scripts/csvsql.rst
+++ b/docs/scripts/csvsql.rst
@@ -47,7 +47,6 @@ Generate SQL statements for a CSV file or execute those statements directly on a
       --no-create           Skip creating a table. Only valid when --insert is
                             specified.
       --overwrite           Drop the table before creating.
-      --blanks              Do not coerce empty strings to NULL values.
       --db-schema DB_SCHEMA
                             Optional name of database schema to create table(s)
                             in.
diff --git a/examples/blanks.csv b/examples/blanks.csv
new file mode 100644
index 0000000..e801118
--- /dev/null
+++ b/examples/blanks.csv
@@ -0,0 +1,2 @@
+a,b,c,d,e,f
+,NA,N/A,NONE,NULL,.
diff --git a/examples/blanks_converted.csv b/examples/blanks_converted.csv
new file mode 100644
index 0000000..4c70fa1
--- /dev/null
+++ b/examples/blanks_converted.csv
@@ -0,0 +1,2 @@
+a,b,c,d,e,f
+,,,,,


Test patch:
diff --git a/tests/test_utilities/test_csvjoin.py b/tests/test_utilities/test_csvjoin.py
index 6d287b1..b0f29e7 100644
--- a/tests/test_utilities/test_csvjoin.py
+++ b/tests/test_utilities/test_csvjoin.py
@@ -44,6 +44,18 @@ class TestCSVJoin(CSVKitTestCase, EmptyFileTests):
         with open('examples/join_short.csv') as f:
             self.assertEqual(output.readlines(), f.readlines())
 
+    def test_no_blanks(self):
+        self.assertRows(['examples/blanks.csv', 'examples/blanks.csv'], [
+            ['a', 'b', 'c', 'd', 'e', 'f', 'a2', 'b2', 'c2', 'd2', 'e2', 'f2'],
+            ['', '', '', '', '', '', '', '', '', '', '', ''],
+        ])
+
+    def test_blanks(self):
+        self.assertRows(['--blanks', 'examples/blanks.csv', 'examples/blanks.csv'], [
+            ['a', 'b', 'c', 'd', 'e', 'f', 'a2', 'b2', 'c2', 'd2', 'e2', 'f2'],
+            ['', 'NA', 'N/A', 'NONE', 'NULL', '.', '', 'NA', 'N/A', 'NONE', 'NULL', '.'],
+        ])
+
     def test_no_header_row(self):
         output = self.get_output_as_io(['-c', '1', '--no-header-row', 'examples/join_a.csv', 'examples/join_no_header_row.csv'])
         self.assertEqual(len(output.readlines()), 3)
diff --git a/tests/test_utilities/test_csvjson.py b/tests/test_utilities/test_csvjson.py
index 54299c5..25f37e4 100644
--- a/tests/test_utilities/test_csvjson.py
+++ b/tests/test_utilities/test_csvjson.py
@@ -30,6 +30,14 @@ class TestCSVJSON(CSVKitTestCase, EmptyFileTests):
         js = json.loads(self.get_output(['examples/sniff_limit.csv']))
         self.assertDictEqual(js[0], {'a': True, 'c': 3.0, 'b': 2.0})
 
+    def test_no_blanks(self):
+        js = json.loads(self.get_output(['examples/blanks.csv']))
+        self.assertDictEqual(js[0], {'a': None, 'b': None, 'c': None, 'd': None, 'e': None, 'f': None})
+
+    def test_blanks(self):
+        js = json.loads(self.get_output(['--blanks', 'examples/blanks.csv']))
+        self.assertDictEqual(js[0], {'a': '', 'b': 'NA', 'c': 'N/A', 'd': 'NONE', 'e': 'NULL', 'f': '.'})
+
     def test_no_header_row(self):
         js = json.loads(self.get_output(['--no-header-row', 'examples/no_header_row.csv']))
         self.assertDictEqual(js[0], {'a': True, 'c': 3.0, 'b': 2.0})
diff --git a/tests/test_utilities/test_csvlook.py b/tests/test_utilities/test_csvlook.py
index e2b22fe..f7392f9 100644
--- a/tests/test_utilities/test_csvlook.py
+++ b/tests/test_utilities/test_csvlook.py
@@ -35,6 +35,20 @@ class TestCSVLook(CSVKitTestCase, EmptyFileTests):
             '| True | 4 | 5 |',
         ])
 
+    def test_no_blanks(self):
+        self.assertLines(['examples/blanks.csv'], [
+            '| a | b | c | d | e | f |',
+            '| - | - | - | - | - | - |',
+            '|   |   |   |   |   |   |',
+        ])
+
+    def test_blanks(self):
+        self.assertLines(['--blanks', 'examples/blanks.csv'], [
+            '| a | b  | c   | d    | e    | f |',
+            '| - | -- | --- | ---- | ---- | - |',
+            '|   | NA | N/A | NONE | NULL | . |',
+        ])
+
     def test_no_header_row(self):
         self.assertLines(['--no-header-row', 'examples/no_header_row3.csv'], [
             '| a | b | c |',
diff --git a/tests/test_utilities/test_csvsort.py b/tests/test_utilities/test_csvsort.py
index 5e3a5b1..4b67a1a 100644
--- a/tests/test_utilities/test_csvsort.py
+++ b/tests/test_utilities/test_csvsort.py
@@ -39,10 +39,25 @@ class TestCSVSort(CSVKitTestCase, ColumnsTests, EmptyFileTests, NamesTests):
         new_order = [six.text_type(r[0]) for r in reader]
         self.assertEqual(test_order, new_order)
 
+    def test_no_blanks(self):
+        pass
+
+    def test_blanks(self):
+        reader = self.get_output_as_reader(['examples/blanks.csv'])
+        test_order = [
+            ['a', 'b', 'c', 'd', 'e', 'f'],
+            ['', '', '', '', '', ''],
+        ]
+        new_order = [r for r in reader]
+        self.assertEqual(test_order, new_order)
+
     def test_no_header_row(self):
-        reader = self.get_output_as_reader(['--no-header-row', '-c', '1', '-r', 'examples/no_header_row3.csv'])
-        test_order = ['a', '4', '1']
-        new_order = [six.text_type(r[0]) for r in reader]
+        reader = self.get_output_as_reader(['--blanks', 'examples/blanks.csv'])
+        test_order = [
+            ['a', 'b', 'c', 'd', 'e', 'f'],
+            ['', 'NA', 'N/A', 'NONE', 'NULL', '.'],
+        ]
+        new_order = [r for r in reader]
         self.assertEqual(test_order, new_order)
 
     def test_no_inference(self):
diff --git a/tests/test_utilities/test_csvsql.py b/tests/test_utilities/test_csvsql.py
index 4959a4b..aa7d7ad 100644
--- a/tests/test_utilities/test_csvsql.py
+++ b/tests/test_utilities/test_csvsql.py
@@ -44,6 +44,28 @@ class TestCSVSQL(CSVKitTestCase, EmptyFileTests):
         self.assertTrue('time TIMESTAMP' in sql)
         self.assertTrue('datetime TIMESTAMP' in sql)
 
+    def test_no_blanks(self):
+        sql = self.get_output(['--tables', 'foo', 'examples/blanks.csv'])
+
+        self.assertTrue('CREATE TABLE foo' in sql)
+        self.assertTrue('a BOOLEAN' in sql)
+        self.assertTrue('b BOOLEAN' in sql)
+        self.assertTrue('c BOOLEAN' in sql)
+        self.assertTrue('d BOOLEAN' in sql)
+        self.assertTrue('e BOOLEAN' in sql)
+        self.assertTrue('f BOOLEAN' in sql)
+
+    def test_blanks(self):
+        sql = self.get_output(['--tables', 'foo', '--blanks', 'examples/blanks.csv'])
+
+        self.assertTrue('CREATE TABLE foo' in sql)
+        self.assertTrue('a VARCHAR NOT NULL' in sql)
+        self.assertTrue('b VARCHAR(2) NOT NULL' in sql)
+        self.assertTrue('c VARCHAR(3) NOT NULL' in sql)
+        self.assertTrue('d VARCHAR(4) NOT NULL' in sql)
+        self.assertTrue('e VARCHAR(4) NOT NULL' in sql)
+        self.assertTrue('f VARCHAR(1) NOT NULL' in sql)
+
     def test_no_inference(self):
         sql = self.get_output(['--tables', 'foo', '--no-inference', 'examples/testfixed_converted.csv'])
 
diff --git a/tests/test_utilities/test_in2csv.py b/tests/test_utilities/test_in2csv.py
index 943d269..f9f454f 100644
--- a/tests/test_utilities/test_in2csv.py
+++ b/tests/test_utilities/test_in2csv.py
@@ -38,6 +38,12 @@ 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_no_blanks(self):
+        self.assertConverted('csv', 'examples/blanks.csv', 'examples/blanks_converted.csv')
+
+    def test_blanks(self):
+        self.assertConverted('csv', 'examples/blanks.csv', 'examples/blanks.csv', ['--blanks'])
+
     def test_date_format(self):
         self.assertConverted('csv', 'examples/test_date_format.csv', 'examples/test_date_format_converted.csv', ['--date-format', '%d/%m/%Y'])