I am trying to import a column (from .csv file) with numeric and alphanumeric values but when I run the openrowset procedure it imports the numeric rows properly but for alphanumeric values it defaults to null.
Table A
ID,A,B,C
1,12,hh,i
2,ab12,tt,b
3,2,aa,o
4,bc12,ee,l
Code used
SELECT
*
FROM
OPENROWSET
(
'Microsoft.ACE.OLEDB.12.0','Text;Database=C:;IMEX=1;','SELECT * FROM abc.csv'
) t
I used IMEX =1 and no change at all.
The problem cause is the Oledb provider
When importing csv file or excel files with mixed data types column it will replace non dominant types by null. (Using Oledb or Ace.Oledb)
Workarounds
You can do some workaround by adding a first row that contain string values then removing it after impirting is finish
ID,A,B,C
0,a,a,a
1,12,hh,i
2,ab12,tt,b
3,2,aa,o
4,bc12,ee,l
And it will fix the issue if using IMEX=1
This will read columns as string and ID column as number. (0 is used)
Or add HDR=NO property to connection string so the header is the first line imported (all its values are strings)
Read more about mixed data types in this article
Other methods
Or try to achieve this without aceoledb provider just import csv file in other way like the following:
Using Microsoft Text Driver
SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:;',
'SELECT * FROM abc.csv')
Using BULK INSERT
CREATE TABLE dbo.MyTable
(ID INTEGER,
A VARCHAR(50),
B VARCHAR(50),
C VARCHAR(50)
)
BULK INSERT dbo.MyTable
FROM 'C:abc.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '
'
)
Other Details in these articles:
