Send csv to server and start processing?

[Wysocki]

I need to be able to send a file from a Windows computer to my webserver and then have php process it into a MySql database. I've already worked out the php code to process the csv file into the database but it assumes the file is on the server already. I can have my pc application ftp the file to the server but I don't know how I can automatically have php start processing it once it gets there. Right now I have the php code in a webpage and I have to point my browser to that page to get the code to execute.

[sde]

Hi Wysocki, welcome to the site. Since there may be a couple ways to do this, let me ask a couple questions.

1. Is this a regular event? For example: once per day, once per hour?
2. Do you have shell access to your web server? Can you setup cron jobs?
3. Do you have php installed on your windows computer?
4. How big is the csv file? ( in k or mb )
5. Does this process need to be automated? -or- Will you manually execute the transfer?

[Wysocki]

1. Is this a regular event? For example: once per day, once per hour?
Will probably be once a day but I'd like to set it up so that if someone is in the app (Visual Foxpro) and they want to update the website, they can initiate the job from that windows machine.

2. Do you have shell access to your web server? Can you setup cron jobs?
I have shell access although the person sending the data up probably would not. I'm REALLY new to the Linux server so I believe I DO have cron setup capability but I have no idea what that is.

3. Do you have php installed on your windows computer?
No. It could be done if it's not a big effort since I'd have to do this on a couple dozen machines potentially.

4. How big is the csv file? ( in k or mb )
Usually pretty small (<10k) but the initial data load could be a couple meg.

5. Does this process need to be automated? -or- Will you manually execute the transfer?
I would like to initiate the process manually at the windows pc (probably by having the app fire off a batch file for processing). The bat file could do the ftp and whatever but I'd like to have the php code pick up the process automatically once the data gets up to the server.

Thanks for the welcome, kinda strange to be a forum virgin.
I REAALLLY appreciate your quick response!

[sde]

ok, for manual execution would be very easy to just make a web page that uploads the csv file which would then execute the logic to read it into your datbase. it would just be a web form with a browse button and submit. if you needed help with the upload script, let me know.

however, i don't think that is what you're looking for. you probably are better off calling a php web page remotely from the command line.

i dont' know how to do this in windows other than calling internet explorer to load the php page that has the logic to parse the csv, i don't know. in the command line, it would look something like this:

"C:\Program Files\Internet Explorer\iexplore.exe" http://mydomain.com/csv_parser.php

the only problem with this is that it will launch your browser. there may be another way to read a web page from a windows shell, but i don't know.

I asked about the shell access only because you could setup a cron job to run once a day to parse the csv file. this won't work for you if you want the website updated as soon as the csv is uploaded though.

[sdeming]

I suggest installing either Cygwin for the entire GNU toolset, or find a windows built version of either curl or wget. With curl or wget you set the batch file to call the URL as SDE mentions above automatically after the FTP completes. With Cygwin you can script it all in bash which makes life that much easier.

[Wysocki]

Originally Posted by sde

the only problem with this is that it will launch your browser. there may be another way to read a web page from a windows shell, but i don't know.

The trick would be to set up a bat file that would first do the ftp, then AFTER the ftp was successful, to have the browser "execute" the code. I suppose the code could also have something in it to close the browser window but the sequencing of these events is the issue. Is there any way of having some sort of script that would automatically upload a specific file from the local pc?

Originally Posted by sdeming

I suggest installing either Cygwin for the entire GNU toolset, or find a windows built version of either curl or wget. With curl or wget you set the batch file to call the URL as SDE mentions above automatically after the FTP completes. With Cygwin you can script it all in bash which makes life that much easier.

Whoa, slow down! Is curl and wget some kind of utility I can install on the local pc? "Scripting in bash" sounds NOT llike life being easy!

[sde]

well if you have to install something, and you already know a bit of php, then maybe php would be the choice. you can run php scripts in the window shell.

php usually installs in: C:\php

let's say we wrote a script to upload the csv file, and then call the php script on the remote server that will parse and write data to the db. let's say we named this script: update.php and for this example it is located in C:\scripts\update.php

once php is installed on your windows box, you can call the script from a windows shell like this:

c:\php\php.exe c:\scripts\update.php

now, a quick search on php.net turned up the ftp logic, .. i just added one extra function at the bottom to call the csv_parser script on the remote server. this will probably work as is if you go this way. you may just need to make a bat script with the code to call the php program.

PHP Code:

<?
$source_file = "C:\\directory\\to\\file.csv";
$destination_file = "path/to/file.csv";
$ftp_server = "mydomain.com";
$ftp_user_name = "me";
$ftp_user_pass = "pass";


// set up basic connection
$conn_id = ftp_connect($ftp_server);

// login with username and password
$login_result = ftp_login($conn_id, $ftp_user_name, $ftp_user_pass);

// check connection
if ((!$conn_id) || (!$login_result)) {
       echo "FTP connection has failed!";
       echo "Attempted to connect to $ftp_server for user $ftp_user_name";
       exit;
   } else {
       echo "Connected to $ftp_server, for user $ftp_user_name";
   }

// upload the file
$upload = ftp_put($conn_id, $destination_file, $source_file, FTP_BINARY);

// check upload status
if (!$upload) {
       echo "FTP upload has failed!";
   } else {
       echo "Uploaded $source_file to $ftp_server as $destination_file";
   }

// close the FTP stream
ftp_close($conn_id);

// call the php csv parser file
file("http://mydomain.com/parse_csv.php");
?>

[Wysocki]

YESSSS!!!! It Works!

I installed php to the local pc and created the php as you described and it worked the first time! I'm a little confused though as to why the last line causes the parse_csv.php to execute on the server and not just on my local machine.

Thanx!

[sde]

i think telnet might have been an option too, .. but i'm not sure how to call a page with telnet. if you were able to, then you wouldn't need php to call the script.

the file() function reads a file ( local or remote ) into an array. each line of the file is a seperate element of the arrray.

we don't really care about the array, just that it calls the file to try to read it. that just tells the php script to execute.

on the telnet subject though, i could connect to a server with telnet mydomain.com 80 .. then i tried to get a page with: GET index.php , but that didn't work.

so if you don't mind installing php on all the clients, then that may be the best solution.

[idx]

telnet foo.com 80
GET /

[Wysocki]

Thanks for all the help!