Moving Contact Database

**SomeoneNamedMichael** · 11-11-2011, 03:24 PM

Hi, I suck with this stuff, and have worked my way into a corner I'm afraid.

I am moving our old contact database to a new program. In the old database, the company ID that contacts reference to see what company they belong to are unique nvchar50 type characters that look like GKDF-22DK-....

For the new program, the company ID's are INT type, so when I exported everything from my old database into excel files, and tried to import that into the new database, the types are off, so it breaks.

I have 1 workbook, 2 excel sheets. The first excel sheet is the company export, listing their ID, address, etc. The second excel sheet is the contact export, listing the companyID it belongs to, the contacts name etc.

What I realize I need is some type of mass find and replace script, that will look at each company record in the company sheet, search matching company ID's on the contact sheet, and replace all those matching values with an integer type of 1. then on the next record, do the same thing, and increase the integer to 2, and so on for each record. So that all the contacts now refer to the same company, only with a different ID type, that I can then import to this new program.

Any help would be so awesome.

I've already tried doing the above on the SQL side, but that mucks everything up when exporting the data to excel, which I need to do for import.

**jaslake** · 11-11-2011, 04:25 PM

Hi Michael

A sample of your data would be helpful. Include sheets 1 and 2 as you described and add a third sheet that demonstrates what you wish the results of your sample data to be. Perhaps one of us can help.

**SomeoneNamedMichael** · 11-11-2011, 05:14 PM

Okay here is a upload of some sample data. The company and contact exports before, and then what they are suppose to look like after. Only the company ID needs to change on both sheets, however on the contacts sheet, many contacts have the same company ID, so they need to change respectively.

I just cant wrap my brain around how to do. If i had <1000 records i would just change the values manually, but there are 11,000 contacts with like 600 companies.

ANY help would be so great.

Thanks.

**jaslake** · 11-12-2011, 02:59 PM

Hi Michael
This code is in the attached. See if it does as you require. Run the code from the Button on Sheet "New_Company". Let me know of issues.

Please Login or Register  to view this content.

**SomeoneNamedMichael** · 11-14-2011, 05:03 PM

John,

From what I can tell this solution works great!

I'm still sorting and cleaning up the data and have yet to apply the script to the entire database, but I'm betting this solves my problem.

Thanks again! You are a wonderful person!