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:

FROM 'MyFile.txt'

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 MyFile.csv')

-- Query Excel
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 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

  • Is it acceptable to use a dynamically created temp table that is flushed after use?
    – jcolebrand
    Commented Nov 26, 2012 at 17:20
  • Second question: have you tried using the Excel drivers to just open the TDF? Sometimes Excel is magical.
    – jcolebrand
    Commented Nov 26, 2012 at 17:21
  • @jcolebrand Yes I can use temp tables, however the column list is quite long and may change, so I'd much rather be able to specify only the columns I'm interested in instead of having to write out the definition for all of them, and remembering to update them anytime the unneeded columns change.
    – Rachel
    Commented Nov 26, 2012 at 17:23
  • @jcolebrand I had not thought to try the excel drivers, although it's not looking promising right now. The file extension is actually .bak which isn't a normal delimited file extension, and the error it gave me on my first try is Could not find installable ISAM, so I don't think this will work.
    – Rachel
    Commented Nov 26, 2012 at 17:26
  • 2
    I would personally parse the column list on the fly from the file each time (just do a countof tabs in the first line of the file) instead of trying to keep up with the number of columns needed for import, thus the dynamically created temp table. Never hard-code what you can reverse engineer from the data, in my opinion.
    – jcolebrand
    Commented Nov 26, 2012 at 17:30

2 Answers 2


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:

    'Text; HDR=YES; Database=C:\Text', 
    'SELECT * FROM testupload2.txt')

In C:\Text\schema.ini:


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.

  • 1
    Thank you very much! I had seen hints of using a schema.ini file, however I was having trouble figuring out how to implement one on my own
    – Rachel
    Commented Nov 30, 2012 at 19:06
  • 1
    I know this is an old post but I just had to say "Nice Job" @dartonw .
    – Jeff Moden
    Commented Jun 25, 2014 at 1:50

May be SSMS: How to import (Copy/Paste) data from excel can help (If you don't want to use OPENROWSET or don't have permissions for it).

  • 1
    Hello, Denis. The questios says "query a tab-delimited file ... without saving it anywhere", so I believe the method you suggested wouldn't apply properly.
    – Ronaldo
    Commented Apr 9, 2020 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.