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.
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 only takes a minute to sign up.
Sign up to join this communityAre 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.
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.
Mar 23, 2013 at 18:34
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.);
Jul 25, 2015 at 20:06
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.
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.
Mar 23, 2013 at 19:12