TSQL: Query Pipe-Delimited text files with OPENROWSET

Sometimes, when working with extracts of data, it can be a pain to have to load these files into a database in order to work with them. It’s easy to use OPENROWSET to save yourself a little time. Here’s a basic example;

SELECT *
FROM OPENROWSET
('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)}; 
DefaultDir=C:\Users\Rhys\Desktop\csv;Extended properties=''ColNameHeader=True;Format=Delimited;''',
'SELECT * FROM file1.csv');

The DefaultDir property points to a directory containing standard comma separated files. Now the text files can be treated just like tables, note the SELECT query above in red text. There can be any number of files in the same directory and they can be queried simply by changing the appropriate filename, i.e. file1.csv to file2.txt.

csv openrowset sql server thumb TSQL: Query Pipe Delimited text files with OPENROWSET

If you experience the below error you need to enable the feature on your SQL Server instance.

Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online. 

Run the below T-SQL to enable.

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Now this is handy but some people, myself included, prefer to use Pipe-Delimited csv files. Microsoft seem insistent on calling pipes the vertical-bar. If you attempt the above, with pipe-delimited files, here’s what you get!

csv pipe openrowset sql server thumb TSQL: Query Pipe Delimited text files with OPENROWSET

Not something we can work with very easily! By modifying a registry entry we can quickly resolve this. Usual warnings about editing your registry apply, i.e. backup and know what you’re doing.

Using regedit, navigate to the following key;

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

regedit jet text driver thumb TSQL: Query Pipe Delimited text files with OPENROWSET

Edit the value for the Format key;

text driver format registry thumb TSQL: Query Pipe Delimited text files with OPENROWSET

Change the value from CSVDelimited to Delimited(|). To reverse this change just set the value back to CSVDelimited when you need to.

text driver format registry pipe thumb TSQL: Query Pipe Delimited text files with OPENROWSET

Run the same query again and you’ll see our data is how we need it to be!

csv pipe openrowset sql server fixed thumb TSQL: Query Pipe Delimited text files with OPENROWSET

Read more about the Text Data Source Driver if you need to query files with other types of delimiters.


Leave a Reply

Current day month ye@r *