Exporting query data
Is there an easy way to export SQL query data into a tab delimited or CSV format? I know I can do this with a loop and variables, but it seems like there should be an easier way. (my query has 100+ column names!). Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341817 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Exporting query data
http://cflib.org/udf/QueryToCsv On Wed, Feb 2, 2011 at 1:31 PM, Richard Steele r...@photoeye.com wrote: Is there an easy way to export SQL query data into a tab delimited or CSV format? I know I can do this with a loop and variables, but it seems like there should be an easier way. (my query has 100+ column names!). Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341818 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Exporting query data
The easiest way is to do this within SQL Server Management Studio. There are a few methods of exporting data built into that product, all of them easy to use. -Mike Chabot On Wed, Feb 2, 2011 at 2:31 PM, Richard Steele r...@photoeye.com wrote: Is there an easy way to export SQL query data into a tab delimited or CSV format? I know I can do this with a loop and variables, but it seems like there should be an easier way. (my query has 100+ column names!). Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341819 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Exporting query data
John's answer is the correct one for doing it on the CF server side. Here's how I would do it on the SQL server side. For MySQL: SELECT * INTO OUTFILE '/location/of/file.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' FROM AnyTable For MSSQL, it can be simple or complicated: simple: 1. Run your query in SQL Server Management Studio 2. Highlight the resultset 3. Copy/paste into excel 4. Save as CSV complicated, run BCP command line utility on the SQL Server: c:\bcp DatabaseName.dbo.TableName out C:\fileLocation.csv -c -t , -S SERVERNAME -U username -P password Have fun! On 2/2/2011 11:43 AM, John M Bliss wrote: http://cflib.org/udf/QueryToCsv On Wed, Feb 2, 2011 at 1:31 PM, Richard Steeler...@photoeye.com wrote: Is there an easy way to export SQL query data into a tab delimited or CSV format? I know I can do this with a loop and variables, but it seems like there should be an easier way. (my query has 100+ column names!). Thanks in advance. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341820 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: Exporting query data
For MSSQL, it can be simple or complicated: simple: 1. Run your query in SQL Server Management Studio 2. Highlight the resultset 3. Copy/paste into excel 4. Save as CSV Even simpler for step 3 and 4, 3. right click and select save as CSV. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341822 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
