Mysql syntax to find the opposite to load data local infile

windows XP
Mysql V5.1

My query i type into
mysql Query browser

Load data LOACAL infile
'c:/sql/dumpme.csv'
into table testtable
fields terminated by "'"
lines terminated by '\r\n\';

This works fine (but not with out using 'LOCAL' on line 1)



Is there any way to do the opposite using LOCAL to send the data to OUTFILE on my LOCAL machine?

Maybe
write data LOCAL outfile ?

I have this so far

select * into outfile 'C:\\dumpme.txt'
fields terminated by ','
optionally enclosed by '"'
lines terminated by '/n'
from dumpme;

which returns

>60007 rows effected.

But when i search for the file in c:\ it does not exist.
I have tried '\\' '/' and '\' in the pathing.

I have asked this question on the following link but no go as yet.
I have asked this question, again as now i want to know maybe if its not working due to me not knowing the syntax for LOCAL
Thankyou


http://www.experts-exchange.com/Database/MySQL/Q_25870166.html?cid=1575#a30792328
LVL 8
kingjelyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rjdownCommented:
The query you posted is fine, however you must remember that it needs permissions to create the file in the location specified. Try simply using 'dumpme.txt', see where it ends up and work from there :D
0
SoLostCommented:
The problem you are having is that the command you are running is being executed on the server.  Your outfiles will be located on the server, not on your PC.

If you're just wanting a dump of the table then I would copy the mysqldump.exe executable from the server and dump it to a file with some formatting parameters.

Check out :

http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html

You can specify similar options such as --fields-terminated-by etc

Since you are running the command locally, the file will be created locally.

If you're after something simpler, running the query on the Query Browser and selecting "Export Resultset" from the file menu is probably the easiest way.  Not good if you have a lot of data though.  It all depends on the solution that you're after.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rjdownCommented:
I had thought of that, but he commented on his use of LOCAL INFILE so I assume he's running a local server.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SoLostCommented:
Hmmm just the way he said "send the data to OUTFILE on my LOCAL machine" I interpreted it to mean that he wasn't on the local server....  well, I guess we'll find out when he answers :)
0
kingjelyAuthor Commented:
Hi SoLost and Rj,

Correct, I'm not on the server, I'm on a remote machine.
I found the files, there were on the server which is linux, which makes things more confusing.
I tried dumpme.txt without a path, which also goes to the linux server.

So are you saying i need the mysqldump.exe on my local machine for this OUTfile query to go to my local machine.

If I wanted to learn how to do this so i could go into one of my clients shops
For example, and copy back 'the_customer_table' using select into OUTFILE,
I would need to also have to get a mysqldump.exe on the local C:\ on the computer in the shop?

Sorry im new to this, thanks for your help so far
0
SoLostCommented:
That is correct, you will need to have mysqldump on the machine that you are wanting to dump the file to.

As the database server is Linux and you are using Windows I suggest downloading the "mysql-noinstall" zip version of MySQL server from the MySQL web site :

http://dev.mysql.com/downloads/mysql/

Inside this zip file you will find a Windows version of mysqldump that you can use.  You will not need to install it, simply execute it from the command line.

e.g.  mysqldump -uUsername -pPassword -hServerName ......

A list of all the options can be found at the URL that I gave you earlier.  There are also some examples that may be of benefit to you.
0
kingjelyAuthor Commented:
Okay, not to challenge you but someone has just said the following to me;

"The outfile command you are using is 'server side'
you need to find how to issue 'client side'
mysqldump.exe is just a standalone utility."

This response is suggesting the OUTFILE command I posted above is only for the 'server side', not the client.

Does this ring true at all with you?

0
SoLostCommented:
That is correct.  If you read my original post that is what I said on the first line.

The Query browser is only a client that passes the SQL statements to the SQL server.  The SQL statements are being executed on the server and the OUTFILE paths are relative to the server, not the client.

This is why I suggested mysqldump.  It is a standalone utility, but it is a utility that is designed to dump data from the database to a file on your local PC.  It's main use is for database backup but by using the options you can get it to format and dump the contents of a table to .csv format.

There's a fairly good example of using mysqldump to output a .csv file here :

http://christianriesen.com/2009/06/creating-a-mysql-dump-in-csv-format/
0
kingjelyAuthor Commented:
Ahh okay i get it.

So the browser queries, get stored on the server.
Thanks for that clarification.

The example helps me to understand mysqldump,
But still, i would like to still find, as suggested the 'client side' syntax
to send the data to my local machine.

(ill give you points for all your help so far, but im going to leave this question open till i get the answer!)

THanks heaps so far, you will be an guru in no time =D
0
kingjelyAuthor Commented:
'So the browser queries, get stored on the server.
Thanks for that clarification.'
This is not quite right, but i do understand what you mean... =D
0
SoLostCommented:
I think you seem to be missing the point.  There is no 'client side' syntax that can be used from within a client such as Query Browser that will get you what you want.

As taken from the MySQL documentation :

The SELECT ... INTO OUTFILE statement is intended primarily to let you very quickly dump a table to a text file on the server machine. If you want to create the resulting file on some client host other than the server host, you cannot use SELECT ... INTO OUTFILE. In that case, you should instead use a command such as mysql -e "SELECT ..." > file_name to generate the file on the client host.

In the above statement they are using the mysql client as opposed to mysqldump which I suggested.  The command that they suggest merely outputs the data to a file, it does not format the data as .csv.
0
SoLostCommented:
It's not that the queries are "stored" on the server... it's that they are executed on the server.  The Query Browser is merely your interface into the server.

It's like two people on the telephone in different houses.  You're the client, your friend is the MySQL server and the telephone is the Query browser letting the two of you communicate.

You can say to the other person over the phone "get the orange juice from the fridge, pour it into a glass and leave it on the bench in the kitchen"

The glass of orange juice will not be on your kitchen bench... it will be on theirs.
0
kingjelyAuthor Commented:
Okay,

so can i do the example you gave me in the browser?
0
kingjelyAuthor Commented:
Ill try to do this example in the browser, and get back to you.
http://christianriesen.com/2009/06/creating-a-mysql-dump-in-csv-format/
0
SoLostCommented:
That example isn't an SQL statement that is executed in the browser.  That is running the mysqldump.exe utility on the command line.
0
kingjelyAuthor Commented:
Ahh yep i got it,

so
1)install mysqldump.exe,
2)Do the example
and
3) there is no 'client side' syntax for this so DO; 1 ) and 2)  
hehe
0
kingjelyAuthor Commented:
I think I should inherit your name.

hehe..
Thanks for your patience
0
SoLostCommented:
That's correct.

The good thing is that mysqldump doesn't have to be installed.  You just need the mysqldump.exe file.  You can even run it from a USB key if you don't want the customer to have access to it.
0
kingjelyAuthor Commented:

This is much more of what I need! Cool.
So I guess my next and final dumb question (for now.)

Would I find the mysqldump.exe, on a link on mysql.com. or do i have to dl the entire server onto my local machine to get it
0
SoLostCommented:
Unfortunately they don't have the individual files available for download so you'll have to download a copy of all the server files from them.

Follow the link that I posted earlier :

http://dev.mysql.com/downloads/mysql/

Download the "mysql-noinstall" zip version of MySQL server from the MySQL web site.

Inside this zip file you will find a Windows version of mysqldump that you can use.
0
rjdownCommented:
I don't understand why you would need to install the server on your local machine. You'd still need to run all the commands on the remote machine!

Assuming the remote server has PHP installed, and is web-accessible, a solution may be to install something like phpMyAdmin on it which will allow you to manage the database remotely and download a dump of the database directly to your local machine in any format you wish. You can also re-import the data to your remote database from your local machine with the same tool.

http://www.phpmyadmin.net/
0
SoLostCommented:
I didn't say to install the server on his local machine.  I merely asked him to get a copy of the mysqldump executable.
0
rjdownCommented:
I see, fair enough.

In which case, kingjely, don't forget to add the host attribute to the beginning. Copying the example SoLost linked:

mysqldump --host=YOURREMOTEDATABASEIP -p -u USER -T DIRECTORY --fields-enclosed-by=\"  --fields-terminated-by=, DATABASE

Though I still think if the remote server is accessible to the outside world, phpMyAdmin or similar tools are much simpler and nicer to use! Please do check it out.
0
kingjelyAuthor Commented:
Hey thanks guys,
I got a copy of the

mysqldump.exe and i ran it and it actually worked for ME.. which doesnt happen to often.

It all makes alot more sense to me now so thanks heaps!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.