A while ago, I posted a blog article about building CSV's from SQL tables. It was a very popular post, so I'm submitting it again. I decided to go research if there were any things to do differently with the new SQL Server 2008. Turns out what I wrote before still applies.
What drove me to write the original post was 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.
Another thing to do in Management Studio is the following:
- Open SQL Server Management Studio
- Go to Tools > Options > Query Results > Results To Text
- On the far right, click on the drop down box labeled Output Format
- Choose Comma Delimited and click OK
So, what if you don't have Management Studio? You can do it in a 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.