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?
Take a look at the second link in my answer on this question about registry keys that control how JET infers types.
You might have to substitute in something else for
Excel
, however.