0

I'm doing some work with databases, and I want to play around with the ability to load file content into a table. Currently I'm using a host server for a website, and using phpmyadmin to interact with MySQL. My questions is where do you save the file you want to load if you aren't using a local host? Everything I've read deals with local hosting, but what if I have a file that I have saved on an external server? I thought maybe something like LOAD DATA INFILE 'url to file' INTO TABLE [table name] would work, but instead I get an error.

Do I need to save this locally on my computer and then have it somehow read the file from a local path? I'm not sure this is normally done.

Faris Zacina
  • 14,056
  • 7
  • 62
  • 75
Jcmoney1010
  • 912
  • 7
  • 18
  • 41
  • mysql can't handle urls. it's not a web client, it will not reach "outside" the server to download something. `load data` will accept a filesystem path only: a local path on the machine running the mysql client, or a server-side path when not using `load local` – Marc B Oct 10 '14 at 20:10
  • possible duplicate of [How to use LOAD DATA INFILE statement when file is another location?](http://stackoverflow.com/questions/7965043/how-to-use-load-data-infile-statement-when-file-is-another-location) – andy Oct 10 '14 at 20:37
  • Are you trying to do this through phpMyAdmin or with your own LOAD DATA INFILE statement? – Isaac Bennetch Oct 26 '14 at 00:49

1 Answers1

0

Option 1

If you have SSH access to the MySQL Server Machine, do a SSH login to the machine and

  • Start the mysql client with --local-infile=1

    mysql --local-infile=1 -u username -p yourdb

  • Load the file (assuming it is in your current directory) to the table

    LOAD DATA LOCAL INFILE 'namelist.csv' INTO TABLE customers COLUMNS TERMINATED BY ',';

Option 2

If you don't have SSH access to the remote database server

  • Create a database instance in your local machine MySQL server
  • Load Data as shown in option #1
  • use mysqldump -u username -p yourdb importedtbl > tbldump.sql
  • If your remote Webserver has phpMyAdmin to connect to your remote database server, use it import the table dump to your remote server.
kums
  • 2,661
  • 2
  • 13
  • 16