Sql-server – query a tab-delimited file from SSMS

csvsql-server-2005

Is it possible to query a tab-delimited file from Sql Server Management Studio to view its data without saving it anywhere?

I know you can BULK INSERT from a tab-delimited file using something like:

BULK INSERT SomeTable
FROM 'MyFile.txt'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n');

however that requires you know the columns in advance and create a table to hold the data.

I also know you can query some other file types such as CSV or Excel without defining the columns in advance using OPENROWSET and the Excel drivers, such as:

-- Query CSV
SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=\\Server\Folder\;HDR=Yes;', 
    'SELECT * FROM MyFile.csv')

-- Query Excel
SELECT *
FROM OPENROWSET('Microsoft.Ace.OLEDB.12.0', 
    'Excel 8.0;Database=MyFile.xls', 
    'SELECT * FROM [Sheet1$]')

Also, if I change the registry key Format under HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Text from CSVDelimited to TabDelimited on the SQL Server, the CSV query above will correctly read a tab-delimited text file, however it will no longer read a comma-delimited text file so I don't think I want to leave it like that.

Attempting to use Format=TabDelimited in the OPENROWSET does not work either

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
    'Text;Database=\\Server\Folder\;HDR=Yes;Format=TabDelimited', 
    'SELECT * FROM MyFile.txt')

I have made some attempts to copy the Text registry keys from both the Engines and ISAM Formats keys to something custom that defaults to TabDelimited, however it is still reading files with CSVFormat instead of TabDelimited format so I must be missing something here.

Is there a way to query a tab-delimited file to view its contents without having to create a table and BULK INSERT it?

I am using SQL Server 2005

Best Answer

You must create a schema.ini file containing the delimiter in the same directory as the text file you are opening. This is the only way to override the registry values on a per-file basis. See the file format documentation on MSDN. Example:

SELECT * 
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0', 
    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:

[testupload2.txt]
ColNameHeader=True
Format=TabDelimited
MaxScanRows=0

If this activity needs to be repeated frequently, I would suggest a script to create schema.ini. Multiple files can be referenced in the same schema.ini or a separate schema.ini can be included with each text file in its own directory.

Related Question