Automating a daily data dump into CMS Builder from a Windows PC

Hi Sid,

We do custom jobs like this fairly often that involve syncing data from one system to another (often a legacy backend system to a web front-end).

Here's the steps:
- Get a sample of the export data
- Have the 3rd party source either make the export data available via a web url, ftp, a direct upload to the website, or my some other means (we need to copy the data from the 3rd party source to the web server)
- Find or write some code to parse the data and insert/update the mysql
- setup a cronjob script (scheduled process) to run daily and perform the import/update

Also, you need to deal with the issue of whether you are just replacing the data, or syncing (where changes or extra data on the web copy need to be maintained or copied back to the source).

As a first step, I'd ask them for a sample output file, and ask them the methods available for you to be able to access the data (http, ftp, can they ftp upload it to another server, etc).

Hope that helps.

Thanks Dave.

This is real estate MLS data, so I access it using an industry protocol called RETS. I had custom scripts on my webserver for this, but my MLS changed database vendors so they're now worthless.

The easiest/cheapest way for me to download the data is with a Windows RETS client my MLS' database vendor wrote. So my original question was looking for advice on how I automate the transfer of data files to my webserver from a Windows box (so you guys can work with it).

Questions for you:

The data files will be delimited but can be tab, CSV or pipe. Which works best for you (or does it matter)?

Do you want the data files to have a header? (it can be either way)

The guy who wrote my original RETS scripts told me after the fact that the "cms_" created by CMS Builder added a lot of work to his mapping (it was 2,000+ fields in 14 tables). Will that be a problem for you? If so is there a way to turn the "cms_" off without disrupting the existing tables?

I can email/upload a sample once you tell me the format you want it in. Just let me know how to send it to you. My MLS will require a confidentiality agreement signed by you but I might already have that on file (I've hired you before).

Thanks, Sid

Hi Sid,

>how I automate the transfer of data files to my webserver from a Windows box (so you guys can work with it).

I'd either setup a web server on your windows box so the web server can request the data file via a secret URL, or setup a automated FTP process to FTP the file from the windows box to the web server.

Here's some articles on creating windows batch files to automatically upload files:
http://www.howtogeek.com/howto/windows/how-to-automate-ftp-uploads-from-the-windows-command-line/
http://support.microsoft.com/kb/96269

>The data files will be delimited but can be tab, CSV or pipe. Which works best for you (or does it matter)?

We can work with anything but I'd say CSV first, followed by tab and then pipe. However, If there's a default format that's easiest just send us that.

>Do you want the data files to have a header? (it can be either way)

If it's easy I'd say yes, as we can then add a line of error checking to not import if the header is different (good for detecting future changes that would otherwise break things)

>If so is there a way to turn the "cms_" off without disrupting the existing tables?

If we're writing a script on the web server to import from a database then we can do the field mappings there, and the table prefix doesn't need to match or be there. So not a problem if it's there or it's not.

>I can email/upload a sample once you tell me the format you want it in.

Sure, just send any details to me direct at dave@interactivetools.com and we can figure out the next steps. Thanks!