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
HKLM\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Text from
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
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