Any help or pointing in the right direction..would be thankful......also how much time will this take
I have to parse a flat file to a SQl Server database. It is currently being parse to a MySql database. The file usually hits everyday. Sometimes twice a day. I need to parse that data to the SQL Server database everytime it hits. Also be able to check the length of the first line in the file to see if it is correct for the type of file. If the file is supposed to be a POheader file, it looks for a first line of 624 or 625 characters(it allows for an extra carraige return character under windows), otherwise it looks for a first line length of 680 or 681 characters.
Next it creates a temporary table to hold the data fields from the imported file.
Next it needs to load the file into the temporary table. It should remove blank lines from the end of the temp table.
If this is a PO(Product Order number)file, it deletes POs in the existing table which are abour to be replaced by new entries in the imported file with the same PO number, then it inserts the new rows into the permanent table, setting the status fields, "LoadedDate", "LoadedTimeStamp",
and "ModifyFlag".
If this is a PO Header file, it inserts the new entries into the permenant table using the SQL Replace command, which does an implicit delete of old items with a matching unique primary key.
Finanly it needs to copiles a report of how many lines have been changed and such and sends it vis email.
