Import external data into SQL server: from CSV or TEXT file

3. csv file
Suppose we have a csv file Animal.csv under c:\test. It contains the following records:
Yellow    12       345     Dog
Brown      3       222     Dog
Black      4        55     Cat
For csv or text, a schema.ini file is required to define the schema for each file under the folder.
The schema file is used explicitly if it’s in the folder. Otherwise OLEDB provider use default settings.
ODBC driver will create a schema.ini file with default values in the folder if the file does not exist.
The schema file defines the name and data type for each column in each csv/text file.
The schema file for this test is (suppose two files Animal.csv and Test1.sql in the target folder ):
[Animal.csv]
Format=CSVDelimited
ColNameHeader=False
MaxScanRows=0
Col1=Color Text Width 20
Col2=Weight long
Col3=Length long
Col4=Type Text Width 15
CharacterSet=ANSI
[Test1.sql]
Format=Format=Delimited()
ColNameHeader=False
MaxScanRows=0
Col1=SQLCmd Text Width 250
3.1 Use OPENROWSET
We need to use MS OLEDB provider for ODBC and ODBC text/csv driver.
select * from OpenRowset(‘MSDASQL’, ‘Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\Test;’,’select top 3 * from Animal.csv’)
Since OPENROWSET does not support variable in parameters, if the file name is dynamically determined in run time, use dynamic query like this:
DECLARE @FileName nvarchar(255)
DECLARE @Table nvarchar(50)
SET @FileName=’C:\Test’
SET @Table=’Animal.csv’
DECLARE @SQL nvarchar(2000)
SET @SQL=’SELECT * FROM OPENROWSET(
”MSDASQL”,
”Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=’+@FileName+”’,”SELECT * FROM ‘+@Table+”’)’
PRINT @SQL
EXEC(@SQL)
 
 
3.2 Use linked server:
There two ways to define linked servers to csv files.
One is to use Jet OLEDB provider:
EXEC sp_addlinkedserver @server = ‘CSVOleText’, @srvproduct =’Jet’, @provider = ‘Microsoft.Jet.OLEDB.4.0′, @datasrc = ‘C:\Test’, @provstr = ‘Text’
GO
– no security mapping
EXEC sp_addlinkedsrvlogin CSVOleText, FALSE, NULL, NULL
GO
To list the tables in the linked server:
EXEC sp_tables_ex CSVOleText
GO
It gives the following results. The table name (files in the folder c:\Test) is changed to Animal#csv.
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
——— ———– ———- ———- ——-
NULL      NULL        Animal#csv TABLE      NULL
To query the data in [Animal#csv], use 4-part name:
SELECT * FROM CSVOleText…[Animal#csv]
Or we can use OPENQUERY:
SELECT * FROM OPENQUERY(CSVOleText, ‘SELECT * FROM [Animal#csv]‘)
To insert a record to the file:
INSERT CSVOleText…[Animal#csv] (Color,Length,Type,Weight) VALUES (‘Yellow’,345,’bird’,15)
Another way is to use OLEDB provider for ODBC:
EXEC sp_addlinkedserver @server = ‘CSVOleODBC’, @srvproduct =’ODBC’, @provider =’MSDASQL’, @provstr=’Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Test’, @datasrc = NULL
EXEC sp_addlinkedsrvlogin CSVOleODBC, FALSE, NULL, NULL
GO
To list the tables in the linked server:
EXEC sp_tables_ex CSVOleODBC
It gives the following results. Unlike Jet OLEDB provider, the table name (files in the folder c:\Test) is unchanged, but the catalogue name is the folder name c:\test
TABLE_CAT TABLE_SCHEM TABLE_NAME TABLE_TYPE REMARKS
——— ———– ———- ———- ——-
C:\Test   NULL        Animal#csv TABLE      NULL
To query the data in [Animal.csv], use 4-part name:
SELECT * FROM CSVOleODBC.[C:\Test]..[Animal.csv]
Or we can use OPENQUERY:
SELECT * FROM OPENQUERY(CSVOleODBC, ‘SELECT * FROM [Animal.csv]‘)
The csv/text ODBC driver does not support DML. If we try to insert data to the file, we will get the following error:
Server: Msg 7390, Level 16, State 1, Line 1
The requested operation could not be performed because the OLE DB provider ‘MSDASQL’ does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].
 
3.3 Use OPENDATASOURCE:
SELECT * FROM OpenDataSource( ‘MSDASQL’,
  ‘Driver={Microsoft Text Driver (*.txt; *.csv)};
  DefaultDir=C:\Test;’).[C:\test]..[Animal.csv]
We must use the folder name as catalog name.
About these ads
This entry was posted in SQL server. Bookmark the permalink.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s