A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, June 17, 2010

MySQL query to CSV results

This post is prompted by my now rare dabblings in the Unix(tm) world.  

User request ~ we need some values that are stored in MySQL reported to us on a daily basis.

MySQL dumps data out in tab delimited format but the user needed it as a CSV and wasn't savvy enough to do the conversion themselves.  Below is a simple bash script that runs the query contained in the daily numbers SQL file and pipes the results through sed and converts tabs to commas.  Finally, it writes the results to a new file with the format of MarketingDailyNumbers.YYYY-mm-dd.csv  

As with all the code you find here, trust but verify.  In particular, this script will not produced the expected CSV results if there are embedded commas or tabs in the data.  It would need to wrap fields with the delimiter and escape unescaped values.  I didn't have to spend the time working on those problems as my extract is purely numeric but this should be enough code for me to remember the syntax of various things.

# The sed replacement is the tab character
# Generated with ctrl-V tab
mysql -D MutualFundDB -uwebUser -pwebPwd < ./MarketingDailyNumbers.sql | sed 's/     /,/g' > MarketingDailyNumbers.`date "+%F"`


jonmcrawford said...

Curious why you decided to go that route rather than modify the MySQL query to just output as a CSV?



Bill said...

Ummm because I'm ignorant of how to do it within the query itself? Is that a parameter/switch when using MySQL? Or did you mean something like SELECT CONCAT(col1, ',', col2) AS output FROM MyTable