Accessing a data file on another server for LOAD DATA INFILE

db__novice · #1 (**permalink**) 04-05-06, 06:21

Hope you guys can answer this. The SQL statement below works perfectly well if the data.txt file is on the same server as the MySQL database, what I would like to know is can the data.txt be on another server, so that the path to access it would be like 'http://www.somewebsite.com/data.txt', I've tried putting in an url path but it always fails, it seems to want only a directory path and one that is on the same server. Any tricks to overcome this?

"LOAD DATA INFILE 'data.txt'
REPLACE INTO TABLE table_name
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
IGNORE 0 LINES"

Many thanks.

jfulton · #2 (**permalink**) 04-05-06, 17:37

Quote:

I would like to know is can the data.txt be on another server, so that the path to access it would be like 'http://www.somewebsite.com/data.txt'

Never done this before, but here is a possible workaround:

If I connect to a your host db from my computer and use LOAD DATA INFILE with the LOCAL option, then your server will load the data from my machine. I'm not sure off the top of my head how to (or if you can) get mysql to pull a file from another computer though...

What type of data are you trying to send over? Can you just dump and then import?

db__novice · #3 (**permalink**) 04-05-06, 20:03

The type of data is that it's a merchants product list, i.e. "Sony|25.00|CD Player" etc, but without any database structure information.

Each night the merchants create a product list (data.txt) which is then stored on their own server, what I want to do is to input these product lists into my database on my own server with the LOAD DATA INFILE statement. Is this possible?

P.S. I don't want to connect to their databases as this means that they will have to give me a username and password which I don't think they will do.

dbmab · #4 (**permalink**) 04-05-06, 21:36

I tried this with a .txt file on a remote server and get an error message like you would if you were trying to use the FTP protocol (I did not attempt to provide the FTP user and password, as I doubt this will actually work, and you would not have an FTP user and password for the remote system you are dealing with anyway.) The mysql manual states that the file must be either on the server or the client (using the LOCAL option.) I would say that the short answer to this is - you cannot use LOAD DATA INFILE to do this.

Your best bet would be to create a script to pull the remote file to your server, then execute the LOAD DATA INFILE...