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.

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!

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

Edit the value for the Format key;

Change the value from CSVDelimited to **Delimited(

). To reverse this change just set the value back to **CSVDelimited when you need to.

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

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