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 …
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 **