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=CSVDelimitedColNameHeader=FalseMaxScanRows=0Col1=Color Text Width 20Col2=Weight long Col3=Length long Col4=Type Text Width 15CharacterSet=ANSI

3.1 Use OPENROWSETWe 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:

To list the tables in the linked server:EXEC sp_tables_ex CSVOleODBCIt 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:\testTABLE_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 1The 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].