Remote csv to MySql

wdglide

I need to get a csv file that is stored remotely - on another site -- into my DB. Its about 3000-4000 lines.

I had a similar project before where I deleted the DB table and used Load Data Infile to repopulate it. I am planning to do roughly the same here.

To do this I need to read this remote file, then write it locally. I'm looking around at fopen and fwrite, but I cannot see how to use them with a remote file.

This obviously won't work get the remote file, but I thought it would help to show where my thoughts are starting from.

$filename = 'my.csv';
$somecontent = "http://www.domain.com/their.csv";

// Let's make sure the file exists and is writable first.
if (is_writable($filename)) {

// Open $filename in write mode.
if (!$fp = fopen($filename, 'w')) {
     print "Cannot open file ($filename)";
     exit;
}

// Write $somecontent to our opened file.
if (!fwrite($fp, $somecontent)) {
    print "Cannot write to file ($filename)";
    exit;
}

print "Success, wrote ($somecontent) to file ($filename)";

fclose($fp);

} else {
    print "The file $filename is not writable";
}

TIA

weekender

fopen can only read local files (ie stored on the same server as the php script) or remote (stored on another machine)

i think the issue is that remote files have to be on the web somewhere - on a webhost, your machine (running a web server and accessible over ip or domain name) etc - php cannot just read files from a client machine.

To get around this, you have to upload the file to the server - start reading here - it's fairly easy

or just install phpmyadmin and use that 🙂

adam

wdglide

Sorry I missed your reply yesterday. Thanks!

However, I've been researching this for the last day or so. I believe my best option would be to use file_get_contents to read the file into a string. Then write it with fwrite.

But for some strange reason, it worked a few times then quit. I can't, for the life of me, figure out why? It works for any remote site I try, except the one I need :mad:.

weekender

even better would be [man]fgetcsv[/man], which parses the csv as it reads the file.

what are you trying to fwrite to? post the code (only relevant bits, mind!)

adam

wdglide

The data on the remote site is in csv format (but its rendered with .php). I need to take the rendered csv content and stored it locally on my server. I do not have control over the remote site.

// local file name 
$filename = "my.csv";

if (!$content = file_get_contents("http://www.remotesite.com/test.php?key1=value1&key2=value2")) {
	print "Cannot get remote Template Data";
	exit;
}

if (is_writable($filename)) {

if (!$fp = fopen($filename, 'w')) {
	 print "Cannot open file ($filename)";
	 exit;
}

if (!fwrite($fp, $content)) {
	print "Cannot write to file ($filename)";
	exit;
}

print "Success, wrote templates to file ($filename)";

fclose($fp);

} else {
	print "The file $filename is not writable";
	exit;
}

I then use code (that works 🙂 ) to import it into MySql. (Similar to phpMyAdmins load data from csv.)

It will be run via Cron daily.

weekender

the filename has to be an absolute path, i think

on apache:

fopen("/home/site/my.csv");

on win (remember to escape slashes)

fopen("c:\\\home\\\site\\\my.csv");

you can test to see if the filename exists using [man]file_exists[/man]

oh, and a quicker way of error checking - rather than doing

if (!$fp = fopen($filename, 'w')) {
            print "Cannot open file ($filename)";
            exit; 
}

i find this easier:

$fp = fopen($filename, 'w')
    or die("Cannot open file for writing");

note: die stops execution

adam

weekender

ok where i said on win, you have to escape slashes, you have to put two slashes in between each directory in the path. This forum won't show it properly

wdglide

Originally posted by weekender
the filename has to be an absolute path, i think

I believe relative is ok. Wouldn't my error check catch that? I tried it anyway and still -- no go.

I'm 90% sure the problem is the site I'm trying to reach -- is unreachable from my server. There have been DNS problems on their end (templatemonster.com).

I isolated just the file_get_contents part and it failed with this site but worked with yahoo.

Thanks for your assist!

wdglide

You have a beautiful site BTW!!!

weekender

haha thanks 😃

i have a completely new copy on my laptop, just not quite got round to finishing it. Glad you like it though - that's all php too.

what's the url of your site?

adam

wdglide

Kind of ashamed to say... Its a template:o

weekender

no shame in that, it's very impressive.

i think choosing a good template is as much a skill as designing yourself in some respects.

i like the subtle things, like the pics that come into focus, the squares that appear and dissappear and the white noise on the monitor in the photo.

very nice

wdglide

Thanks. I really like your style -- however, it does not do well for a commercial site. I love designing (though I'm more carpenter than scupltor). The templates are just too good to pass up -- from a business sense.

Your site reminds me of an old favorite of mine: http://www.pakobrats.com/

I love large, high quality photos.