Goldmine conversion

[gdurham]

Hello all,
I am currently working on getting all of our data out from goldmine into sugarcrm. We do not need alot of data, the only data we need is the contacts. However, after looking through the data it is massively messed up. I think I have a way of fixing the issue however the biggest issue I am running into is massaging the data. It is 24000 yes 24 thousand contact records, and has a field for company and contact, where in a lot of cases the company is the customer name or vice versa. The issue I am running into is that the contact has the persons first and last name in it or even a company name. I thought about parsing the data and splitting it at the space but that wouldn't work if it is a company name. Does anyone have any ideas as to a decent way to get this data straight. I know it will be alot of time but any ideas would be very benificial.

Thanks,
Greg

[sugarcane]

Hi Greg,

in a lot of cases the company is the customer name or vice versa.

By the above, do you mean that the contact person's name was incorrectly entered into the company name and that the company name was incorrectly entered into the contact name field?

If this is the case, then there is no easy way to do this and get all the data in the correct fields.

You could try to parse out the Contact first and last name fields in your csv, and then based on the length (how many columns the data spreads into), try to determine that way if the record is really a contact or company (if it is more than 2 columns, taking into account 1 for first name and one for last name. Although if a person has a last name or a Jr, or the company is 2 words long, this will obviously not be the case). Any massaging within the spreadsheet will require manual work, since the contact and company name fields were used interchangeably. But considering the fact that you have 24,000 records, this might be too tedious.

If by the above quote you mean that the contact person's name is what you use for the company name (if this client is an individual and not a company), then i would propose importing all fields into the Accounts module in Sugar.

Without actually seeing the data, it is a bit difficult to really say. We have performed many data migrations which include cleanup, so if you decide you do not want to do this yourself, please feel free to contact Ray Stoeckicht at rays@intelestream.net or 888-435-2221 for more details.

Otherwise, good luck with the import!

[SergeiLo]

Hi Greg,

What database do you have at the backend of your GM? At Apatar open source data migration tool, our users and customers use our tool to migrate their GM data to SugarCRM (any edition).
What you can do with Apatar: If you have for example MSSQL db at the backend of your GM you may configure MSSQL connector to connect to your database, then a list of tables appear where you will find CONTACT1 table. Here you may write a very simple SQL query to choose only three fields you need to cleanse:

Select top 1000 company, contact, lastname From [CONTACT1]

or

Select company, contact, lastname From [CONTACT1]

(to choose all 24K records)

Apatar will choose only these three fields where:
Contact field associates with First and Last name of the contact or company like you mentioned.
Last name field associates with the last name (the last word in the Contact field after the last space for example Greg Ivory Smith where Smith will be treated as Last name)
Company field associates with the company name or contact name like in your case.

Click Finish and you may do Preview results on the connector.

Once you have this information on your screen you may see where you have your data messed up. Then you may for example import this data to MS Excel file (2003 Office required) to change everything manually (because I do not see any other way) using MS Excel connector.

Thus your first datamap will look like: MSSQL connector -> Transform -> MS Excel connector.

After you cleansed your data in MS Excel file you may use this new file and a connector for MS Excel to import all the data to SugarCRM directly using a connector for SugarCRM. Moreover you may assosiate Accounts (Company) with the Contacts which belong to this company.

Hope it will help you. If you have any questions feel free to ask.

Regards,
Sergei

[gdurham]

Sugarcane-Yes, to what you were asking however it is more like the contact was entered into both the company and the contact fields. Which makes it hard to create a table of companies so that I can then import it into the accounts section of sugarcrm. Then take people and enter them into contacts. One way which would alleviate this problem would be to take and insert everything into both accounts and contacts. However, I would love not to do this because it would be a complete duplication of all of my data.

SergeiLo-We have a dBase backend, thank you for the tip, I will look into that software.

Thanks!!
Greg

[sugarcane]

Hi Greg,

it is more like the contact was entered into both the company and the contact fields. Which makes it hard to create a table of companies so that I can then import it into the accounts section of sugarcrm. Then take people and enter them into contacts. One way which would alleviate this problem would be to take and insert everything into both accounts and contacts. However, I would love not to do this because it would be a complete duplication of all of my data.

I see now. Since there are cases where the contact name is the same as the company name, then it should be fairly easy to parse these out. It can even be done within excel with a formula. One option you have for these contacts is to import the account name for these as 'The XXX Household' or something to this degree. Then, there would be a valid account, and if there is more than one family member, or more than one contact person for that household, it does maintain the account-contact relationship.

If you have not already, check out the following links, which contain instructions and best practice for importing data into Sugar:
Import data
Import Related Records

If you would like assistance with your import, we are more than happy to help. You can contact Ray Stoeckicht at rays@intelestream.net or 888-435-2221.

Good Luck!

[gdurham]

Thank you so much, that is a very good idea. I will work with that.

Thanks!
Greg