Importing with a partial matching record
In our database, we have a merchant number field that is 12 digits long. I need to import a spreadsheet that has new information tied to each of these merchant numbers. The problem is that in the spreadsheet, the merchant number field only has the first 9 digits of the number.
These are bank-assigned numbers and the 9-digit numbers are unique so there are no doubles. I do not know why the bank provides some reports w/ only 9 digits and some with the full twelve. Anyway, I tried to import but it seems that the fields have to match exactly, and there is not any way to match by the first nine digits only. There are too many records to consider doing it manually.
I'm wondering if 1, there is some way to match partially and import based on that match that I haven't found. If not, I've thought another option could be 2, making a script that would create a second, 9-digit merchant number field in each record based on the existing 12-digit number field so I could import based on an exact match. I have no clue how to create a script that would do this. If that's not possible, I also thought I could, 3, export the entire table into Excel and see if Excel will let me merge the two spreadsheets some how based on a partial match and then reimport the entire thing again.
Any guidance is greatly appreciated!