3

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.

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
4
  • Not local...i need to load directly on server are you talking about this ? mysqlimport -h hostname -u username -p password
    – farhan
    Mar 23, 2013 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, 2013 at 18:34
  • 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
    Jul 25, 2015 at 20:06
  • This is surprisingly fast.
    – Dan Sandland
    Jul 25, 2015 at 20:20
0

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.

Improve this answer
4
  • "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, 2013 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, 2013 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, 2013 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, 2013 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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