3

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Illuminati
  • 555
  • 2
  • 7
  • 34

2 Answers2

6

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 = '\n' 
  )

Other Details in these articles:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • I want to automate the process every time opening writing and saving is not a feasible option. – Illuminati Jan 30 '17 at 21:56
  • @zack read the article that i provided i think it is what ur looking for – Hadi Jan 30 '17 at 21:58
  • No then I wont have column names I will have F1,F2 and etc which isnt acceptable. – Illuminati Jan 30 '17 at 22:54
  • 2
    @zack why down voting? Read more about oledb mixed data types and you will see that there is only few ways to work with. – Hadi Jan 31 '17 at 06:41
  • `OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "(null)".` getting this error when I use above process – Illuminati Feb 01 '17 at 20:41
  • 1
    Please follow these steps: Go to Control Panel -- Administrative Tools -- Data Sources(ODBC) --Drivers check if there is a driver named "Microsoft Text Driver". – Hadi Feb 01 '17 at 21:02
  • 1
    @Zack you can also follow this link for more details https://www.toadworld.com/platforms/sql-server/b/weblog/archive/2015/02/09/t-sql-read-csv-files-using-openrowset – Hadi Feb 01 '17 at 21:13
4

The below example should work. Note that On my system I couldn't use the provider that you use (because I haven't installed this in a while and figuring out what to install is driving me crazy.) The key piece: You need a format file to tell SQL server the expected values in each column. Here's a good blog post using csv in your queries I can't paste the XMl as the version may vary depending on your database:

The easiest way to create the xml file is to:

1) Create a table in the database. You won't be inserting to this. It will just allow you to have the next command create the table:

CREATE TABLE dbo.TestInsert 
(ID INTEGER,
 A VARCHAR(10),
 B VARCHAR(10),
 C VARCHAR(10)
)

2) Next, run the following command line bcp program to generate the appropriate file:

bcp YourDatabase.dbo.TestInsert format nul -c -x -fimport.xml -t, -T

3) Finally, use import.xml as your format file.

SELECT 
  * 
FROM 
   OPENROWSET
    (
        BULK 'c:\test.csv', 
        FORMATFILE='c:\import.xml',
        FIRSTROW=2
    ) t
Adam Jacobson
  • 564
  • 4
  • 9