Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is it acceptable to use a dynamically created temp table that is flushed after use? –  jcolebrand Nov 26 '12 at 17:20
    
Second question: have you tried using the Excel drivers to just open the TDF? Sometimes Excel is magical. –  jcolebrand Nov 26 '12 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 Nov 26 '12 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 Nov 26 '12 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 Nov 26 '12 at 17:30
add comment

1 Answer

up vote 8 down vote accepted

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.

share|improve this answer
    
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 Nov 30 '12 at 19:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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