Hi, all. I've been working on setting up sites with direct data feeds from data vendors for Real Estate (MLS).
I've worked out how to tap into their FTP sites with CFFTP and download files. I've also worked out how to then unzip those file with CFZIP. After working with the tab-delimited files that the data comes in, it's become apparent that getting that data into MySQL is going to be a chore. I spent about 5 hours this afternoon just getting data for one table, the "Acreage" table into MySQL. I had to use MSSQL 2005 Server Express and Management Studio to read the scripts and create the tables. But even with a data import wizard from Microsoft, the data that the vendor sent wouldn't load into the tables cleanly, without error. I finally decided to create the tables in MySQL manually, then import the data from the vendor-provided tab-delimited text files. However, errors were returned. It was too difficult to check the data for problems in the text files, so I imported the data into Excel and then imported the data into MySQL from there. However, as I was attempting to run the import into MySQL, I was frequently getting "corrupt data errors". I looked at the data and found what looked like Chinese characters in some of the fields that caused errors. After eventually working my way through about 1800 rows of data and cleaning it up, I managed to get the entire table into MySQL where I can use it. Now that was today's work on "one" table. There are about 8 tables that have to be updated *daily*, so I've got to automate this entire process. If anyone is working with a setup like this, I'd sure like to hear what procedures you're using to get from zipped up data from a vendor into your database, especially if you're using MySQL. I do have a specific question concerning "illegal" characters in the vendor's data. Is there some way to "clean up" the data in an automated manner before trying to import it? Are there CF tags or functions that are made for something like this or third-part software that can accomplish this? Thanks for taking the time to read this. Any advice or guidance will certainly be appreciated. This has turned out to be *much* more complicated a process than I thought it would be and, like I mentioned, the data has to be updated daily. Part of the problem is that the guy with the data vendor that I speak to doesn't know anything about databases; the "data-dump" that's performed on their end is automated and the people that work there didn't set it up. So when I talk to them, they don't know much about what I'm talking about, even though my knowledge is certainly limited. I know one thing that would probably make this an easy situation, it seems. If I could get them to provide complete sql scripts that create tables *and* populate them with data, that would be great. However, they just provide scripts to build tables and tab-delimited text data files. Thanks again for any help! Rick ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;160198600;22374440;w Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:298410 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
