Exporting mysql queries in .csv format
Sometimes, in order for example to populate an excel file or equivalent e-sheets, we need to export data resulting from a mysql query in csv ( comma separated values) format.
We can act as above.
Let be:
select field1, field2,...,fieldN from TABLE1 inner join TABLE2 on TABLE1.fieldM=TABLE2.fieldR where field1='value1' and TABLE2.field2='value2' order by field3,field4, field5;
our original query.
All we must do is to modify that query adding the string [OPTIONS] like this:
select field1, field2,...,fieldN [OPTIONS] from TABLE1 inner join TABLE2 on TABLE1.fieldM=TABLE2.fieldR where field1='value1' and TABLE2.field2='value2' order by field3,field4, field5;
(OPTIONS string must be exactly before FROM)
where [OPTIONS] is:
INTO outfile '[file_name.csv]' FIELDS TERMINATED BY '[separator]' ENCLOSED BY ''
We substitute to file_name.csv the absolute path of .csv file we want to create, as an example:
/home/user/dump_table.csv
[separator] is the field separator choosed.
It can be a single character, but it's better to choose a string that almost surely is not contained in any field of data. As an example:
###, @@@, #@#, #£@
Hereafter we choose string ### as separator.
Let we execute the query as user dumper with home /home/dumper
Let our file name be dump_table.csv.
The complete query is:
select field1, field2,...,fieldN INTO outfile '/home/dumpolo/dump_table.csv' FIELDS TERMINATED BY '###' ENCLOSED BY '' from TABLE1 inner join TABLE2 on TABLE1.fieldM=TABLE2.fieldR where field1='value1' and TABLE2.field2='value2' order by field3,field4, field5;
In this way we obtain file dump_table.csv that we can use to import in Excel et similia.
As an example, in Linux, we can open this file using Libre Office Calc: the application recognize file extension .csv and open the window Text Import.
Whe check the Other option in Separator Options field, assigning the value ###.
Now spreadsheet opens and we can save the result in any format permitted (.ods, .xls, etc.).
0 commenti:
Iscriviti a:
Commenti sul post (Atom)
Post
