OPENROWSET – how to read everything as text?

I am using the following command to load data into SQL Server:

INSERT INTO [NewTable]

SELECT * FROM OPENROWSET 
(
'MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:SomeFolder;'
, 'SELECT * from [SomeFile.csv]'
);

The problem is that apparently the driver tries to guess the datatype for each field, and where the cast fails it simply reads in a null. For example, lets say I have the following

SomeCode   SomeName
100        A
299        B
22         C
123        D
ABC        E
900        F

It seems to figure that “SomeCode” is an integer, and it will read “ABC” as NULL. Is there any way I can stop this from happening. All I want is for the data to be handled as varchars all the way through.

Any ideas?

Answer

Take a look at the second link in my answer on this question about registry keys that control how JET infers types.

You may also want to make sure the ImportMixedTypes key is set to Text.

HKLMSoftwareMicrosoftJet4.0EnginesExcelImportMixedTypes

You might have to substitute in something else for Excel, however.

Advertisement

Answer