MySQL query to a text file
Posted by John_Moylan (jfp), 12 August 2003Well this rated high on my "Why didn't I think of that" factor, so I thought I'd share.
I wanted to run a query in MySQL and save the results in a text file, I could write a script in Perl, PHP, Java etc to do this but saw this and liked it.
The query is piped through to MySQL and redirected to a text file.
I liked it, hope it helps someone else too for a quick hack.
Note: I normally save text files from a query using 'dbish' but DBI.pm was not installed on the particular machine.
'dbish' is another goodie to look at.
When I get time I'll post a little discuss on it.
Posted by admin (Graham Ellis), 12 August 2003There's also an oprion on SELECT to redirect output to a file, but that will be a file on the machine on which the mysql daemon is running, and not the machine on which the client is running. Of course, sometimes it's the same machine ...
Posted by John_Moylan (jfp), 12 August 2003Hello Graham
You can't just tease us with the mere mention of simpler way of doing this.
Any chance of an example query?
Posted by admin (Graham Ellis), 13 August 2003Sure, how about
Which will give you a tab separated file, with \ protection on special charcters. There are FIELDS SEPARATED BY and other modifiers available too, and if you say "dumpfile" not "outfile", you'll get binary data in a single line.
Sorry to be a biot obtuse before, but I was away and didn't have all the books with me. Internet access has been from a cafe, with 6 PCs sharing a modem or ISDN line and most computers there in use by the younger generation playing network games - it's been like trying to make a phone call in a war zone
Posted by 4est (4est), 14 August 2003i am assuming that using the SELECT to output file method mentioned will create a file if not present and overwrite it if it is present. am i right? is there a way to append? is there a way to do the opposite? INSERT from a textfile?
thanks so much, 4est.
Posted by admin (Graham Ellis), 14 August 2003The output file CANNOT already exist. This is done to prevent users overwriting / modifying system files. You must also have file privilege on thge server.
PH: 01144 1225 708225 • FAX: 01144 1225 793803 • EMAIL: email@example.com • WEB: http://www.wellho.net • SKYPE: wellho