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

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s