Sync Database From Flat Files
$30-5000 USD
Paid on delivery
I have clients that are using an old legacy system that stores information in flat files. This system stores data in a separate windows folder for each company. I need a program that will traverse the folder tree, extract the data from the flat files and insert records into a SQL database. This will enable the client to easily generate reports since the information will be in a single database instead of multiple flat files/folders.
The information in the flat files is static for the most part but new records will be added daily. This process should keep the SQL database in sync with the flat files so only new/changed records should be added. The program to update the SQL database will be run nightly.
The SQL Server will be on the same network, not necessarily the same computer. So the connection string should be specified in the web.config. Same for the data files. They will be on the same network, not the same computer.
There are two flat files that will need to be synced:
[login to view URL] - this file contains owner [login to view URL] - this file contains financial transactions for an owner.
[login to view URL] will update tbl_Owners in the SQL database.
[login to view URL] will update tbl_Transactions in the SQL database
From here, I plan to work with reports generated by the legacy app to assist the developer to determine the layouts.
The application should be a desktop app running on Windows XP/Vista/7. The database should be a free/low cost version of SQL Server.
## Deliverables
The information needed from the Owners file is Name, Address, City, State, PostalCode, AccountNumber
The information needed from the Fintrx file is TranDate, Description, TranCode, Amount. I will get more sample files from my client for you to develop against.
Unfortunately, I do not have data layouts for the flat files so we'll need to figure this out. I'll assist with this. I did a little analysis on the two files:
Fintran - record length is 94 bytes
Owners - record length is 837 bytes
-- Layouts for the data files should be stored in a config file. This will enable the app to work with different versions of the same legacy app. This config file should relate the file layout to the SQL table name. This would enable additional flat files to be supported in the future.
-- When adding records to the SQL database, each company folder should be indexed with a unique ID. A separate SQL table will store the ID and folder name
-- A log should be captured for errors that occur
-- Add a flag to each record in to the SQL database that indicates whether the particular record is an Add, Update, or Delete.
Project ID: #2699535