Add Data to a File by Matching a String

[shaheer]

I have a file, let's call it file A, which has the following 5 fields:

ID	Name	City	Funded?	Cost ($)
1	Car theft prevention	Kansas	Yes	100000
2	Weapon Upgrades	Toronto	Yes	50000
...
50	Fortifications	Charlottetown	No	250000

Let's say I have another file, File B, with the same 5 fields, but with over 5000 sets of data already in it. All of the ID's in file A are already in file B. I just need to make updates to the fields Funded? and Cost ($).

I've tried using a very simple approach using find and replace:

However, this only appends them to file B (creates a whole new column). I want to replace/add the data from file A into the file B fields that already exist. Also, I do not know how to then overwrite file B after having used find and replace.

[binuacs · Accepted solution]

@shaheer If both the files are having the same ID then use the join tool to connect both files and make the changes. Attaching a sample workflow

[collin_pace]

Use a Join tool, join on ID.  Deselect Funded and Cost from the File B stream and keep them selected from File A stream - make sure no renames for the File A fields.  Then use an output tool to output to a new file.  I recommend writing out to a new file rather than writing over your existing File B, unless you're 100% your flow is working and you will never need to go back and reference original File B.  If possible, make a copy of File B, before you overwrite it if you really insist on the Output tool overwriting the existing file.

Oh, if you do set it up to overwrite the existing File B that you're reading in, you'll want to include a Block Until Done tool between Join tool and Output tool.

[shaheer]

Can you explain why we uncheck the Funded and Cost columns in the join tool?

[binuacs]

@shaheer Since you wanted to update the left table fields (Funded?, Cost) with table from the right, I deselected the values from and included only the fields from the right table, If you dont unselect them , you will values from both the tables