Importing CSV-file produces NULL values

I had an issue where importing data from a CSV file resulted in NULL’s in a column that should have contained bank sort-codes.

The CSV data in question was in the format ‘nn-nn-nn’ or ‘nn/nn/nn’.

Here’s the Select part of the import command …

SELECT sortcode
FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',
		 'Text;Database=\someserver\somefolder;HDR=Yes;',
		 'SELECT * FROM [somefile.csv]')

I found adding IMEX=1 didn’t help.

The answer was to change the registry value …

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office14.0\Access\Connectivity Engine\Engines\TextImportMixedTypes

from ‘Majority Type’ to ‘Text’.

(the majority of numbers in nn/nn/nn were setting this to numeric. Then on import the slashes made it look like dates lol)

** BEWARE – This is off-course a global server setting – be ready to undo – BEWARE **