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

4 posts by 2 authors in: Forums > CMS Builder
Last Post: May 22, 2012   (RSS)

By sidcam - May 17, 2012

I need to populate several tables in my CMS Builder database from a 3rd party source on a daily basis. The 3rd party offers a Windows program that can automate the data downloads to a text or XML file.

I know there are import tools for CMS Builder (and MySQL in general), but I need the process fully automated since it has to be done daily.

Does anyone have any suggestions as to how I get Text/XML data on a Windows machine into CMS Builder on a Linux webserver without human involvement (past setup of course)?

Thanks in advance to all who reply.

Sid

Re: [sidcam] Automating a daily data dump into CMS Builder from a Windows PC

By Dave - May 18, 2012

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.
Dave Edis - Senior Developer
interactivetools.com

Re: [Dave] Automating a daily data dump into CMS Builder from a Windows PC

By sidcam - May 21, 2012

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