I need to find a way to inse开发者_开发知识库rt a file header (not column header) and dump my data after that.
How would you do that ?
Regards,
Not quite a "clean" solution, but this works.
Linux or Unix terminal:
prompt$ echo YourFileHeader > aFile.txt ; mysql -u YOUR_USER -p YOUR_DATABSE --execute="SELECT ..." >> aFile.txt
Windows command window (you have to type the commands one by one):
c:\> echo YourFileHeader > aFile.txt
c:\> mysql -u YOUR_USER -p YOUR_DATABSE --execute="SELECT ..." >> aFile.txt
MySQL will prompt for your password.
I do believe select ... into outfile...
is a bit slow, I've had always better results using this solutions. Downside: The query output will have the field and line default terminators (\t
and \n
respectively)... I don't know if there's a way to tweak this from shell.
Hope this works for you.
We can achieve this using UNION function as below.
SELECT * INTO OUTFILE "/tmp/COD-Shipment-Report.csv" FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM
( SELECT 'Shipment Id' , 'Invoice No' , 'POD Number' , 'Order Id' , 'Shipment Created' , 'Shipment Created-TS' , 'Weight' , 'Qty' , 'Total Amount' , 'Courier Agency' , 'Payment Method' , 'Billing State' FROM DUAL
UNION
SELECT shipment.increment_id 'Shipment Id', IFNULL(invoice.increment_id,'') 'Invoice No', shipment.track_no 'POD Number', shipment.order_increment_id 'Order Id', DATE_FORMAT(ADDTIME((shipment.created_at),'05:30:00'),'%d-%m-%Y') 'Shipment Created', ADDTIME(shipment.created_at,'05:30:00') 'Shipment Created-TS', shipment.shipping_weight 'Weight', shipment.total_qty 'Qty', sum(shpitm.qty*shpitm.price) 'Total Amount', shipment.track_title 'Courier Agency', payment.method 'Payment Method', IFNULL(shipping.region,'') 'Billing State' FROM sales_flat_shipment_grid shipment JOIN sales_flat_shipment ship ON shipment.entity_id=ship.entity_id JOIN sales_flat_invoice invoice ON invoice.order_id=ship.order_id JOIN sales_flat_shipment_item shpitm ON ship.entity_id= shpitm.parent_id JOIN sales_flat_order_payment payment ON shipment.order_id=payment.parent_id JOIN sales_flat_order_address shipping ON ship.shipping_address_id=shipping.entity_id WHERE payment.method='cashondelivery' AND DATE(ship.created_at) BETWEEN SUBTIME('2011-12-01 00:00:00', '05:30:00') and SUBTIME('2011-12-03 00:00:00', '05:30:00') GROUP BY shipment.entity_id ) A
Thanks !!! Srinivas Mutyala
Here's a different solution that may be better or worse, depending on your situation:
http://jasonswett.net/how-to-get-headers-when-using-mysqls-select-into-outfile/
I came up with this solution. This way you don't have to know the field names. Very useful if you just want to make quick dumps of tables.
SET group_concat_max_len = 65533;
select @target:='sourcetablename'; -- the source table you're going to be dumping
select @ts:=replace(replace(now(), ':', ' '),' ',' '); -- a time stamp for the file name
select @csvoutfile:=concat('d:\\\\', @target, @ts, '.csv');
--
-- Pull the field names from the schema
--
select
@field_headers:=GROUP_CONCAT(CONCAT('\'', COLUMN_NAME, '\''))
from
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = @target
order BY ORDINAL_POSITION ;
--
-- build a select statement to include the field names and "union all" it with the table
-- and provide the outfile parameters
--
select
@betterexport:=concat('select ',
@field_headers,
' union all ',
' select * from ',
@target,
' ',
'into outfile \'',
@csvoutfile,
'\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\\n\';');
--
-- prepare and execute the query.
--
prepare qry1 from @betterexport;
execute qry1;
deallocate prepare qry1;
--
--
--
Or better still, here's a procedure version so you can just call it at will (ie. "call dump_csv("tablename");"):
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `dump_csv`(in target varchar(255))
BEGIN
declare ts varchar(50);
declare csvoutfile varchar(255);
declare field_headers varchar(2048);
declare betterexport varchar(2048);
DECLARE curs CURSOR FOR select GROUP_CONCAT(CONCAT('\'', COLUMN_NAME, '\'')) from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = target order BY ORDINAL_POSITION ;
SET group_concat_max_len = 65533; -- max is normally 1024 and will cause problems on tables with a lot of fields
--
-- Pull the field names from the schema
--
OPEN curs;
fetch curs into field_headers;
CLOSE curs;
set ts=replace(replace(now(), ':', ' '),' ',' '); -- a time stamp for the file name
set csvoutfile=concat('d:\\\\', target, ts, '.csv'); -- build filename
--
-- build a select statement to include the field names and "union all" it with the target table
-- and provide the outfile parameters
--
set @betterexport=concat('select ',
field_headers,
' union all ',
' select * from ',
target,
' ',
'into outfile \'',
csvoutfile,
'\' FIELDS TERMINATED BY \',\' OPTIONALLY ENCLOSED BY \'"\' LINES TERMINATED BY \'\\n\';');
--
-- prepare and execute the query.
--
prepare qry1 from @betterexport;
execute qry1;
deallocate prepare qry1;
--
-- beer.
--
END
Enjoy! :)
-- Chris
精彩评论