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.

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!