Importing Accounts to write over existing - how to do?

[clarkdsp]

Hello,

I have exported a list of about 200 accounts out of SugarCRM in CVS mode. I am editing them in Excel because it is faster to update their addresses, etc. there.

I also want to add info such as their Type -- Customer, Prospect, etc.

Now when I try to import those files back into Sugar, it skips them because the IDs match. Also, it does not allow me to map Type, it never shows up on the mapping list at all.

How can I get it to overwrite existing records with new, better info and map the Type?

TIA,

Lori Ann

[mtfrog77]

I use Navicat MySQL.

[clarkdsp]

So am I correct in saying it is not possible through the Admin system or ??

Now I also realize that when I deleted the 20 accounts to test the import, of course the Contacts attached to those Accouns were severed.

Here is my new question: if I want to download all Contacts and all Accounts and the update in Excel and repopulate Sugar, what is the best way to do this?

Do I import Contacts first and then Accounts to that the Contacts tie to the Accounts?

Or do I import the Accounts first and then import the Contacts and all of the matching names will appropriately match up?

And if this is not possible via Admin, is there someone out there who might be willing to help me with this (paid of course) to get this done so I can actually start using the program?

TIA,

Lori Ann

[mtfrog77]

Lori,
I have not found a good way to do it from Sugar it's self, but I was able to import and update about 40,000 contact and 30,000 account in a few mins with Navicat. I would say the best thing for you to do is get a copy of Navicat or even try the free version (I think it is a 30 day copy) and run it yourself. That way next time you want to do something like this you can save money. But if you would like I’ll help you.

If you are going to us Sugar's Import I would clear your database if there is nothing you need. Then update your date in excel and then import them as contacts. Because if you import them as contacts you can put the account name and Sugar will create the accounts for you. But you are now back to the same problem of you have all of your contact in sugar, but none of you accounts has anything more then the name and which contact belongs to them.

This is where Navicat MySQL comes in. You now take you account list that you run an update with Navicat on the accounts table and set back and wait a few mins and your done. Please note it toke me 4 or 5 tries to get if all to import the way I wanted and you can save your import with Navicat to save time. You will also need to set one or more fields as a key to run the update part of Navicat. I hope this helps. Please let me know if you need more help.

[clarkdsp]

OK I am going to try this, but this is the question.

My ACT db is on my Windows server at the office and my SugarCRM is on my web server, LAMP platform.

I do not actually know that much MySQL and I am not sure ... do I install here on my computer and then link to Sugar?

So I would want the Windows platform, yes?

TIA,

Lori Ann

[mtfrog77]

The version of Navicat is based on the OP on your PC.

http://www.navicat.com/download/navicat_trial.exe

I have installed Navicat on my Win XP desktop and I opened the port 3306 on my Linux server that is hosting Sugar CRM. If you are going to be doing testing I would export the files from your server and play with them. I would not play with your live server till you have every thing test and working. And even then I would export the info out and move it into Sugar. That way you have your raw data still on your server that hasn't been touched.

[clarkdsp]

OK then another question: am I exporting the files from Sugar using Navicat to my computer so I can then edit and re-load into Sugar?

I am a little confused where I am starting -- Sugar or Act and which one is exporting and which one is importing ...

I think you are saying export my Accounts list from Sugar into Navicat. Then edit the fields as I want ... then work on re-importing them back into Sugar using Navicat. Is this correct?

Also, what do you mean by export the info out and then move it into Sugar? I am not sure how I am supposed to export it out, verify it is correct and then get it to import into Sugar. Can you expand what you mean by that?

TIA,

Lori Ann

[mtfrog77]

I wouldn’t bother export out of Sugar. It’s more work that you have to do. It sound like ACT has all your info entered correctly. I would start there by exporting your data out of ACT into excel, and check that your information was exported correct. Once you have all the data export and corrected. There are 3 table to keep in mind when importing contact into sugar with no history.

Accounts
Contacts
Accounts_contacts

The reason you don’t want to remove the data out of Sugar is because Sugar had done most of the hard work by creating the relationships. That is what the accounts_contacts does, it maps your contacts to accounts. Now that you have that part down and out of the way lets talk about importing.

At this point backup you Sugar database, as if you don’t do this next part right you can trash your data. Once you have sugar backed up, open Navicat and connect to your sugar server. Now you use Navicat’s Import Wizard and map your exported file from ACT to your Sugar tables (accounts & contacts). Please note that you have to set a Key field for each table for Navicat to update the fields in your sugar database. I would use name and phone number. Take your time and write you field mappings before you run the upgrade.

[clarkdsp]

Thank you for those detailed instructions. I will give it a shot.

I think I read somewhere that they are planning to add search and update capabilities to Ver 4.5, I think that would be a very good thing. After all, if I export them out then I would also like an easy way to import them back it after updating just a bit.

Thanks again,

Lori Ann

[mtfrog77]

Your welcome, and if you need any help please let me know.