loading a csv file which is on local system in to Mysql DB which is on remote server

Question

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.

Answer 1

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.

• farhan: Not local...i need to load directly on server are you talking about this ? mysqlimport -h hostname -u username -p password
• Michael - sqlbot: 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.
• Dan Sandland: When connected to your remote mysql shell from your local server: LOAD DATA LOCAL INFILE '/local/path/to/import_file/import.csv' INTO TABLE your_database.your_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (column_1, column_2, column_3, etc.);
• Dan Sandland: This is surprisingly fast.

Answer 2

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.

• farhan: "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....
• Gord Thompson: 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?
• farhan: 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 ?
• Gord Thompson: 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.