Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can we directly load a CSV file ( which is on the local system) on MYSQL DB ( which is installed on the Remote server ) ?

'load data infile into table name' command can only be used for loading in local system only.

share|improve this question
add comment

2 Answers

Are you looking for LOAD DATA LOCAL INFILE?

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html


Update: The original question exhibits a significant misunderstanding about LOAD DATA INFILE that originally escaped my attention:

'load data infile into table name' command can only be used for loading in local system only.

The client was referred to as being "local" and the server was referred to as being "remote," which makes that statement 100% incorrect.

LOAD DATA INFILE requires that the file already be on the server's filesystem, and adding LOCAL means it must be on the client's filesystem.

From the documentation:

If LOCAL is specified, the file is read by the client program on the client host and sent to the server.

If LOCAL is not specified, the file must be located on the server host and is read directly by the server.

share|improve this answer
    
Not local...i need to load directly on server are you talking about this ? mysqlimport -h hostname -u username -p password –  farhan Mar 23 '13 at 16:34
1  
Please read the documentation at the link provided. LOCAL does not mean local to the server, it means local to where you're typing this command -- at the client, which is what you are asking for. It's the same as LOAD DATA INFILE but it uses a file on your computer, not on the server. –  Michael - sqlbot Mar 23 '13 at 18:34
add comment

If you have phpMyAdmin installed on the remote server then you could use that, although it might be a bit awkward if your CSV file is larger than ~2MB.

If your remote MySQL instance accepts connections from the outside world (or can be made to do so, at least temporarily) then you could run mysqlimport on your local machine with the --host option (amongst others, like --user and --password) to push the data up to the remote server. The mysqlimport command would look something like this;

mysqlimport --local --host=192.168.1.3 --user=root --password --fields-terminated-by=',' --fields-optionally-enclosed-by='"' cms contacts.csv

Failing that, if you can get ssh access to the remote server you could upload the CSV file via SFTP (or similar) and then run mysqlimport on the remote machine.

share|improve this answer
    
"Failing that, if you can get ssh access to the remote server you could upload the CSV file via SFTP (or similar) and then run mysqlimport on the remote machine." i tried this method , in this method i exactly do what you have mentioned above....i was thinking if any command was available so that we can directly run a command from local system for uploading data on remote server.... –  farhan Mar 23 '13 at 12:20
    
Fair enough. That last option does not really address your request for a way to "directly load" the file onto the remote server from the local machine. What about the other two options? –  Gord Thompson Mar 23 '13 at 12:38
    
1 option have its limitations. 2 option seems to be useful..can u please give me more information on the second one in an easy way ? –  farhan Mar 23 '13 at 16:13
    
My backup server is 192.168.1.3 with a database named cms containing a table called contacts. On my dev server (192.168.1.2) I have a local CSV file named contacts.csv and the command mysqlimport --local --host=192.168.1.3 --user=root --password --fields-terminated-by=',' --fields-optionally-enclosed-by='"' cms contacts.csv pushed the data up to my backup server and inserted the rows into the contacts table. –  Gord Thompson Mar 23 '13 at 19:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.