automating idx updates

[carrie]

Hi all,

I'm in the process of adding idx functionality for our website. I have gotten all the way to having the data vendor 'push' the data to our website via ftp, but I cannot access the data via script or any other means, except by downloading it.
One of the files contain pictures, it's over 600MB and I'd have to download it, unzip it, and then upload it all to a folder in a www directory so it will be viewable on the web.
I'm trying to find a way to automate this, so it doesn't have to be manually updated nightly.
We're on a Windows server, the website was built with FP 2002. Has anybody set this up on a similar site that can guide me through automating this?

Carrie

[carrie]

Well, I figured out that the reason I can't access the ftp files via script or web browser in website is because those files are in the ftp directory, and they would need to be in the www directory.
I could have the vendor ftp the data right into a web folder (I spent the majority of the day figuring out how to do that- and I have to enable administrator permissions to the file level structure), or I could manually download and update this everyday. I'm assuming the vendor types my info into a program to push the data into the directory, so I'm not totally worried about security, but that's the only way I've found so far to do it.
Even if the vendor pushes the data to a folder in my www folder I still have to unzip the picture file daily. I only have 1050MB on the webserver now with the photo file at 690MB, so if I write a script to unzip it while it's still on the server, I will go over my disk space. Downloading, and unzipping and reuploading those files will take forever, so please tell me, is there anyway to automate this process?

[wotech]

hi carrie,

i went thru the same situation last year.
Our mls provider (rapattoni) provides a zip file that we download to our server, unzip it to the www folder on our server, then delete the file. After unzipping, we also take the .txt file that's in the zip file and import the contents of it into our SQL database.
We use a VPS (virtual private server) to host our MLS info. If you're not familiar with VPS's--it's a linux server that is shared among a couple people, but you have complete control over a segment of the server. You can install whatever software you want, run scripts, host websites, etc. without the hassle and cost of maintaing your own server and datacenter.
To automate the download process from Rapattoni, we made a linux shell script that we have set to run every night at 1:30am.
If you're using a Linux server and you think the script might help you out, let me know and I'll post it here.
As far as your disk space limitation, I think your only option there is to get more space. The hosting company we use gives us 80GB of bandwidth and 5GB of disk space; so it's not a problem for us.

-ross

[carrie]

Thank you so much for the reply. There doesn't seem to be much support for this issue on the web. I've posted messages at several forums with no response at all.
I wish we were on a linux server, but we're not, and all of our scripting right now is in .asp. Do you know of anybody who has accomplished this in a Windows Environment?
I had started to think that the only way to automate this process was to get space on a vps, because then we could install a script that worked with asp to unzip the file. But there is also the issue of moving the file from the ftp folder to a web folder so that a script will work on that.
Please, any help is appreciated.

Carrie

[wotech]

#!/bin/bash
ftpserver=ftp.server.com
userid='FTPuser'
password=FTPpass
ftp -n $ftpserver <<END_OF_FTP
user $userid $password
lcd /usr/DirectoryToDownloadFTPfile
bin
get RS.zip
bye
END_OF_FTP
echo Done with ftp

cd /usr/DirectoryToDownloadFTPfile
echo changed directory ok
unzip RS.zip -d /usr/DirectoryToUnzipFTPfile
echo Unzipped zip file ok
rm -f RS.zip
echo deleted old rs zip file
rm -rf /usr/DirectoryToUnzipFTPfile
echo deleted old mls pix and data
mkdir images
echo Recreated data directory
unzip -u -o *.zip -d /usr/DirectoryToUnzipFTPfile/images
echo unzipped pix and data ok


TEXTFILE=`unzip -l *.zip | egrep -o "\w+\.txt"`
mysql -h mysqlserver.domain.com --user=mysqluser --password=mysqlpass databasename <<**
DELETE FROM dataTableNameHere;
LOAD DATA LOCAL INFILE 
'/usr/DirectoryToUnzipFTPfile/images/$TEXTFILE' REPLACE 
INTO TABLE dataTableNameHere FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r';
exit
**
echo finished with mysql work

rm -f *.zip
echo Cleaned up zip files
echo Ready for use

[wotech]

the above is a bash script that
downloads a file from the MLS ftp server
unzips the contents of the downloaded file
unzips the contents of the unzipped file to the images directory
get the name of the text file with the listing data
login to the mysql server
import the tab delimited text file into the database table

[carrie]

It looks so easy. Now the cron job you were referring to is what runs this script every night, right?
And you called it a 'bash' script. Where on the webserver do you setup a 'bash' script? In a control panel somewhere?
I will keep researching this until I understand fully.
Thank you for your help so far, and anymore you can give.

Carrie

[wotech]

"bash" refers to the shell running on the VPS.
You can find out what shell your VPS is running by typing $ ps -p $ at the command line. Bash is a very common one.

So in order to make all that text I posted a script, you want to use a text editor on the the vps to make a file with a ".sh" extension and paste all that script text into that file.

cd /usr/files/blah/blah <---cd to the folder you want to make your script in

nano FTPscript.sh <---create the shell script file using the Nano text editor

then paste all that script text into the text editor. Then to run the script, you'll type:

sh FTPscript.sh

at the command line. To get this to run automatically, you'll just create a cron job that runs: sh FTPscript.sh at set intervals.

I'm not sure how familiar you are with linux. And instructions vary depending upon the linux version. Do you have a vps account anywhere yet?

[carrie]

VPS account? No, not yet. I was hoping that godaddy.com would have everything we need, because the cost is low. If not I will check out your host.
Is the only requirement to unzip files on a Linux server that it has the 'bash' shell running?
For Windows, you have to have an additional utility installed to be able to unzip files, so that's why I'm wondering. I want to make sure everything I need is there, before I set up the account this time.
Then, I see you have the script parse the text file into the db. Into a MYSQL db. Is it creating the table here? or do you already have to have the table setup in order to import all of the data?

Also, we receive the data as .txt files, and an accompanying .dic file with all the field names and description of datatypes. Is your format similar to that?

Thank you,
Carrie

[brandondove]

Wotech. Thanks! This was super helpful. I have a couple questions about the actual implementation of your script, because I think my MLS feed is a bit different. Is the file you download the same every time you update? And do you do an automatic update every night even if there is no update, or do you wait for notification from the data feed? In my case, we receive an email with a link (http download) every time the feed is updated, and I was hoping to have the script run every time we receive that email. Since we don't have FTP access to the server to download the file, I am going to have to cURL it and do all the post processing after that. I think we're using EXIM for the mail server, so I added an alias that runs the script in our aliases file. I'm thinking it should work after that, what do you think?