Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: Accessing a data file on another server for LOAD DATA INFILE

    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.

  2. #2
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    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?

  3. #3
    Join Date
    Oct 2005
    Posts
    26
    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.

  4. #4
    Join Date
    Apr 2006
    Location
    Denver, Co. USA
    Posts
    242
    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...

  5. #5
    Join Date
    Oct 2005
    Posts
    26
    Quote Originally Posted by dbmab
    Your best bet would be to create a script to pull the remote file to your server, then execute the LOAD DATA INFILE
    That's what I thought but I had to ask the question anyway, thanks.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •