Query Excel and text files from SQL

Here is some different ways to query an excel sheet or textfile.

Excel..
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=G:\test.xls;Extended Properties=Excel 8.0')...[sheetname$]

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=G:\test.xls', [sheetname$])

SELECT * into import FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=G:\test.xls', 'SELECT * FROM [sheetname$]')

Texfile..
select * from OpenRowset('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Temp;Extended properties=''ColNameHeader=True;Format=CSVDelimited;''','select * from test.csv')

SELECT a.* FROM OPENROWSET( BULK 'c:\test\test.txt',
FORMATFILE = 'c:\test\formatfil.fmt') AS a;

Most of the time I have test to query textfiles from OPENROWSET it is difficult to delimiter the columns in the text file. I have never seen the Format=CSVDelimited settings having any affect. But the grate site connectionstring.com pointed out that you must set this in the registry on the server. And this works well for me. See example below. I wonder why the setting is available in the Extended Properties then?? At least I have never got it to work. But that says nothing :-)


The delimiter can be specified in the registry at the following location:

HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text 
"Format" = "TabDelimited" or "Format" = "Delimited(;)"