There's got to be a simple way to do this; please help me.

[spectheintro]

Ok, I know I'm missing something very simple here. About a month ago I imported all of our old ACT! records into SugarCRM. (It took quite a bit of editing in Excel and custom mapping, but it worked.) However, a few of the fields weren't mapped correctly, so they didn't transfer over. At first I did not think it would be a big deal (they did not strike me as important data) but a few of my employees have since told me that if I could transfer it over, it would make their lives easier.

How in the world do I do this without deleting all of my clients and re-importing them using a new spreadsheet with the correct mappings? I created all of the IDs so I'm not worried about losing any related data in contacts, notes, or history, but it just strikes me as so odd that I can't use SugarCRM or MySQL to take a CSV, match up the ID field, and then update the employees field and the MISC field based on what's in the CSV. It seems to trivially easy that I know I'm missing something. Can anyone help? It's driving me insane.

[kuske]

Why don't you use MS-Access to update the missing fields from an imported csv-file to the fields in Sugar?
You only must know that ID, rsep. ID_C is the unique key of each record and that, if you have custom fields defined, a record consists always out of two records, one from <obect>-table and one from <object>_cstm-table havinf ID = ID_C.

To connect access to the mySQL database I recommend the latest ODBC-Connector of mysql.com.

And ONE VERY VERY IMPORTANT remark:
Always test such things in a copy of your production environement, NEVER in the production itself !!!

hk

[spectheintro]

I just want to make sure I understand--Access can connect to a MySQL table? And if so, it can then match IDs and fill in the appropriate other values? I've tried using two front-ends for MySql--HeidiSQL and Navicat--and neither has been able to do exactly what I'm looking for. They always create entirely new entries, as opposed to updating existing records.

[kuske]

MS Access can.
I imported our whole SalesForce Database with all values by access macros from cav into separate mySQL tables.
From there I imported all data by litte mySQL scripts to the Sugar tables.
Works fine, we have about 50 MByte data and 100 Users working with their old SalesForce data now.

good luck
hk

[labanjohnson]

The old adage applies, JUNK IN, JUNK OUT. Importing is an excellent chance to clean up your data with a pair of human eyes (four eyes, in my case).

Generally speaking, the hardest part of transferring data from one type of database to another is making sure the CSV is clean, and this is best done manually. It's amazing the damage a comma can do in a "COMMA SEPERATED" file. That's the first place to look if your transfer isn't working perfectly.

I'm VERY VERY VERY HAPPY with Sugar's import system, I especially appreciate how imports are not committed to the database until you're happy with them, because sometimes it takes a few tries to get it right (because of out-of-place data.

I had to import what to some is a small database of about 1500 records from a much less developed PHP system, (was using PHP Groupware.. not recommended - if only for the snotty attitude I was hit with when asking one of the main developers for help. Going to send that guy some St. John's Wort or something.)

There was no simple way to do it, I had to do a Mysql JOIN from two or 3 seperate tables to get all the fields lined up in a row. Then of course I had to make sure that at minimum each row contained a last name. I also came across duplicates, etc that had been imported to PHPGW by different sources and PHPGW and I myself, were too dumb to catch them.

[cerhorn]

Ok, I know I'm missing something very simple here. About a month ago I imported all of our old ACT! records into SugarCRM. (It took quite a bit of editing in Excel and custom mapping, but it worked.) However, a few of the fields weren't mapped correctly, so they didn't transfer over. At first I did not think it would be a big deal (they did not strike me as important data) but a few of my employees have since told me that if I could transfer it over, it would make their lives easier.

How in the world do I do this without deleting all of my clients and re-importing them using a new spreadsheet with the correct mappings? I created all of the IDs so I'm not worried about losing any related data in contacts, notes, or history, but it just strikes me as so odd that I can't use SugarCRM or MySQL to take a CSV, match up the ID field, and then update the employees field and the MISC field based on what's in the CSV. It seems to trivially easy that I know I'm missing something. Can anyone help? It's driving me insane.

It is usually very simple to use SQL to update your tables with new data, assuming you know how to perform an Update. You can embed the SQL statements in your data file, although it may require a text editor (such as TextPad) to easily insert all the correct syntax. I have also had great success using open source ETL tools such as Pentaho and Talend. They can be used to read flat files or any other data source, and to update existing Sugar records.