If you are running MySQL on RDS or don't have access to the file system on the remote server you cannot use the recommend approach to create a CSV file. This would be the recommend way...
SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
I like all the creative ways people on this post came up with to get over this:
http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
If you know your data doesn't have commas or tabs then many of these solutions will work just fine. Such as this Example 1. Example 2 seems to account for even having commas or tabs in your data. But extra quotations in the data might be problematic.
Example 1:
mysql --user=wibble --password wobble -B -e "select * from vehicle_categories;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > vehicle_categories.csv
Regex Explanation:
- s/// means substitute what's between the first // with what's between the second //
- the "g" at the end is a modifier that means "all instance, not just first"
- ^ (in this context) means beginning of line
- $ (in this context) means end of line
So, putting it all together:
s/'/\'/ replace ' with \'
s/\t/\",\"/g replace all \t (tab) with ","
s/^/\"/ at the beginning of the line place a "
s/$/\"/ at the end of the line place a "
s/\n//g replace all \n (newline) with nothing
Example 2:
select concat_ws(',',
concat('"', replace(field1, '"', '""'), '"'),
concat('"', replace(field2, '"', '""'), '"'),
concat('"', replace(field3, '"', '""'), '"'))
from your_table where etc;
Explanation:
- Replace " with "" in each field --> replace(field1, '"', '""')
- Surround each result in quotation marks --> concat('"', result1, '"')
- Place a comma between each quoted result --> concat_ws(',', quoted1, quoted2, ...)
I liked the Example 2 which I have listed above. I've created a script that dynamically generates that type of SQL and then I execute that dynamically generated SQL.
My script runs three commands like this:
# Generate the header
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -s -e "(SELECT GROUP_CONCAT(CONCAT('\"',COLUMN_NAME,'\"'),'' '' SEPARATOR ',')
FROM INFORMATION_SCHEMA.COLUMNS c WHERE TABLE_NAME = 'MY_TABLE_NAME' AND TABLE_SCHEMA = 'MY_DATABASE_NAME');" > my_report.csv
# Create SQL that will be used to get the data
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -s -e "SET SESSION group_concat_max_len = 1000000;
(SELECT CONCAT('SELECT CONCAT_WS(\',\', ', GROUP_CONCAT('CONCAT(\'\"\', REPLACE(',column_name, ',\'\"\', \'\"\"\'), \'\"\')'), ') FROM MY_TABLE_NAME')
FROM information_schema.columns c
WHERE c.table_schema = MY_DATABASE_NAME
AND c.TABLE_NAME = 'MY_TABLE_NAME');" > dynamically_generated_code.sql
# Run the above SQL to get the data
mysql -uUSERNAME -pPASSWORD -hHOSTNAME -s MY_DATABASE_NAME < dynamically_generated_code.sql >> my_report.csv
Explanation:
- First query puts the header values for the CSV file and writes them to the CSV file
- Second query creates the SQL to pull the data so that it will be in comma separated value format.
- The third query executes the SQL that was generated from step 2 and pulls the data for the CSV file and appends it to the file created in step one.
No comments:
Post a Comment