Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Michael71
Posting Yak Master
126 Posts |
 Posted - 2007-01-16 : 14:20:33
|
| Any help or pointing in the right direction..would be thankful......also how much time will this takeThe flat file hit another server in my company....maybe once a day...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. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 14:31:36
|
| Can I get this to happen everytime a file hit the Server? |
 |
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 14:36:13
|
| Is the Format file difficult |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-01-16 : 14:51:47
|
quote:
Originally posted by Michael71
Is the Format file difficult
No, do you know what books online is?There's a sample thereYou will need to know the layout of the fixed width file, and the definition of the table thoughI might suggest using a staging table that matches the layout of the tableBUT, if it's coming from mySQL, why can't they just supply a tab delimited file?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
|
|
|
Michael71
Posting Yak Master
126 Posts |
Posted - 2007-01-16 : 15:01:52
|
| I just found out it wasn't coming from MySQL....but being sent from companies as a flat file.. |
|
|
|
|
|
|
|
|
Parsing a flat file to a SQl Server database?