Text File To SQL Database Import / Sync System

Develop a one-way sync & import routine capable of moving data between a flat file format and a Microsoft SQL Database. We currently have a running prototype written in Coldfusion. The programmer will have the choice of completing the current Coldfusion project or a complete rewrite in PHP / PERL. However Coldfusion is preferred. This routine will regularly be executed on a folder full of flat files. When a new file is recognized it will be imported into the SQL database. When a previously imported file is updated the script will update the SQL Database with the updated values. ## Deliverables We currently have a running prototype written in Coldfusion. The programmer will have the choice of completing the current Coldfusion project or a complete rewrite in PHP / PERL. However Coldfusion is preferred. The system itself will need to be dynamic in that the flat file format and the SQL Schema change constantly. The script will read the data transformation schema from a SQL Table and migrate the data appropriately. _About The Flat File Source:_ The flat file database is created by Calyx Point a common mortgage processing application. The file is a simple ASCII representation of the data. We have already built a processor for the file and created a mapping of the 12,000 possible fields to the SQL database schema. _About The SQL Destination:_ The SQL database is a relational structure containing a subset of the fields stored in the Flat File Source. The script must have enough intelligence to keep from duplicating data in the SQL Destination. _What The Script Must Do:_ 1. Read a directory of flat files and check them against a table full of previously known files 2. Read a transformation schema from a table depicting the relationships between the source and destination tables/fields 3. Break the flat file down into the necessary schema to be imported into several relational tables 4. Choose whether to create new records in the SQL Database or update existing records 5. Transform between different data types such as 1. A checkbox stored as a bit in the text file and a combobox stored as a numeric value in the SQL Database 2. Transform phone numbers from string data to numeric data 3. Break out addresses from street number & street name into separate fields 6. Store a list of conflicts in a SQL Table to be later executed or rejected by the end user. Upon successful completion of this project there will be several other data transformation related projects related to the same SQL Database available for bid. **Requirements For Payment: ** 1) NDA Agreement for access to Database Schema & Transformation Details 2) Complete and fully-functional working program(s) in executable form. 3) Well commented source code 4) Visio diagram or similar of any database tables created unless Coder uses existing structure 5) All deliverables will be considered "work made for hire" under U.S. Copyright law. Buyer will receive exclusive and complete copyrights to all work purchased. (No GPL, GNU, 3rd party components, etc. unless all copyright ramifications are explained AND AGREED TO by the buyer on the site per the coder's Seller Legal Agreement). ## Platform This script will be run in a hosted enviornment. And will execute on several different directories full of flat files and move them into potentially hundreds of different SQL Databases, each with a potentially different schema. The script will be written in eitheir Coldfusion (to maintain consisentcy with other current scripts). Or PHP / Perl for speed. The database is Microsoft SQL 2000, with a potential to move to SQL 2005 in the near future.