October 14, 2008 at 7:46 am
Hi Guys,
Iād like the script to read a textfile on the same server [path is D:\Program Files\comshare\deciweb\BuildersW\Import_SAP_ACT\trial_balance.txt] and replace anything with the the values "B100", "B101", "B102", "B103", "B104", "B105" with "B98" and then all "B10" with "B98". Thereafter the script should replace anything with "S106", "S107" to "S90" and then all "S10" to "S90". The order is important in that the 100's must be done before the 10's and the quotes are not part of the string to be searched for, I just put them in to show what I'm after!
I would like to add this into a DTS package so I'm thinking I add some sort of script just after the package picks up the txtfile. What is best? SQL, ActiveX etc?
Please help. Thanks in advance for your help.
Regards,
Brad
October 15, 2008 at 2:24 pm
Can you import the file into a table, then update it there?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
October 15, 2008 at 2:41 pm
I'll agree with Greg. Get that data into a working table and update the values before moving on in your work flow. Or, use a stored procedure to BULK INSERT the data into a temp table and perform your updates against it...
--SJT--
October 16, 2008 at 8:06 am
If this were a one time event, I would use the search and replace capabilities of Query Analyzer but it sounds like you want to do this on a regular basis. Importing into a table and using the Replace function in an Update statement makes sense. Update it as many times as needed and in the order desired.
Steve
October 16, 2008 at 3:40 pm
Thanks guys!
Will give it a shot.
October 16, 2008 at 9:15 pm
Are you prepared to have all your "B100"to be replaced with "B980"?
Because you want to replace "B10" with "B98".
So, B100 as B10+0 will be replaced as B98+0 = B980.
_____________
Code for TallyGenerator
October 17, 2008 at 6:39 am
Yes, but he also wants to replace B100 with B98, so as long as he does that replace first, he should be fine in that particular case. However, it is a very valid point for data being so similar, and if you have values like B106(which you don't list as wanting to replace), etc, you would need to take an additional step to ensure that those were not in your dataset. For example, instead of just using a replace across the whole table, specify something like the following:
UPDATE MyTable
SET Value = 'B98'
WHERE Value IN ('B101','B102','B103','B104','B105')
That way, you lower the risk of corrupting your data unintentionally.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply