Output SQL queries to CSV
Yesterday, I got an email from another consultant who wanted to know a way to export SQL queries to CSV files. After I formulated my response to him, I decided it would make a good and hopefully helpful blog article as well.
There is actually a couple of ways to do this. First off, and by far the easiest, is to use SQL Management Studio. Open Management Studio and create a query by clicking New Query and then type in your SELECT statement. Recently I had to do this to find some bad phone fields. I created my query, and sent the output to a CSV file to send to the client. The database had over 23000 contacts and looking for bad phone numbers couldn’t be done via the normal lookup. The fields were “funky” (technical term) because of a failed import and ACT wasn’t finding the bad fields via the normal mechanism.
Once you have created your query, do the following:
1. Right-click down in the result records section
2. Select “Save Results As…” and choose either CSV or Text
It’s that simple. However, there is a caveat. If you are using SQL Management Studio 2005, you need to do the following to ensure that quotes are added to your output (to handle commas in the middle of fields). It isn’t set that way by default. Also, depending on which version of SQL Management Studio you are running, you may not have the option to save the results to text.
* Tools – Options – Query Results – SQL Server – Results to Grid.
* Check the “Quote strings containing list separators when saving .csv results” option.
This is especially important if you have fields that have commas like a Company. No quotes means your imports go wonky (as my Australian friends say). In other words, the commas throw the fields over by one putting things in wrong places – i.e. nastiness.
The second way to output is great to know if you are not running Management Studio. You can do it in your SQL Select statement. Here’s an example.
* SELECT * FROM CONTACTS
* WHERE email = NULL
* INTO OUTFILE ‘missingemails.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘n’;
That last line is the trick. Also, the FIELDS TERMINATED BY is what adds commas between columns. The LINES TERMINATED BY ends each line and makes them separate rows of data.
As usual, it’s helping others that gives me inspiration for my blog posts – which I keep vowing to do more often. Thanks out to David for providing today’s inspiration.
Ciao for now.