Monday, September 3, 2012

Query Excel and text files from SQL

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

'Data Source=G:\test.xls;Extended Properties=Excel 8.0')...[sheetname$]

'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$]')

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 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(;)"

No comments: