Text Files (txt or csv)
The text file examples assume a text file has been created at C:\Temp\test.csv. This file has one line of text: “name,object_id”.
32-bit, using the Microsoft.Jet.OLEDB.4.0 driver with OPENDATASOURCE:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- insert into text file - file must exist with proper column names INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source = C:\Temp;Extended Properties = "Text;HDR=YES;"')...[test#csv] (object_id, name) SELECT object_id, name FROM sys.tables; INSERT INTO OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\Temp\;HDR=Yes;')...[test#csv] (object_id, name) SELECT object_id, name FROM sys.tables; -- select from text/csv file SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Temp;Extended Properties="Text;HDR=YES;"')...[test#csv] SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\Temp\;HDR=Yes;')...[test#csv] |
32-bit, using the Microsoft.Jet.OLEDB.4.0 driver with OPENROWSET:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
-- insert into text file - file must exist with proper column names INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\Temp\;HDR=Yes;', 'SELECT * FROM test.csv') (object_id, name) SELECT object_id, name FROM sys.tables; -- select from text/csv file SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Data Source=C:\Temp;Extended Properties="Text;HDR=YES;"', 'select * from test.csv'); SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Text;Database=C:\Temp\;HDR=Yes;', 'select * from test.csv'); |
32-bit, using the MSDASQL driver with OPENROWSET:
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; Database=C:\Temp\test.csv;')x (object_id, name) SELECT object_id, name FROM sys.tables; -- select from text/csv file SELECT * FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=C:\Temp;', 'select * from test.csv'); |
32/64-bit, using the Microsoft.ACE.OLEDB.12.0 driver with OPENDATASOURCE:
|
1 2 3 4 5 6 7 8 |
INSERT INTO OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Text;Database=C:\Temp\')...[test#csv] (object_id, name) SELECT object_id, name FROM sys.tables; SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Text;Database=C:\Temp\;HDR=YES')...[test#csv] |
