Import Question

[Deland01]

I have a problem with my install of sugar & I don't know if the problem lies with the PHP & web pages or the MySQL database so I set another version of sugar up & customised it in the same way.



Would I be right in thinking I can export all the data from my damaged sugar version & then import this into the new version?



I understand I will need to look up assigned_user_id & modified_user_id SQL keys in the new database & change them on the csv file I will import so they match up.



The only question I have really is: I can also see the following fields with data on my csv file:



id

contact_id

account_id

opportunity_id



If I import all the contacts, accounts, meetings, tesks etc... in the same way will everything link up in the end using the same SQL keys or will I encounter issues?



Also would it be better to change each user's SQL key in PHPMyAdmin to what they used to have in the old database instead of changing them on the CSV file's?



Or can anyone else suggest a better way of doing this whole import?

[mtfrog77]

You should take a look at Navicat, Navicat is a windows program that will give you a GUI to look at your database and you can copy your database from one server to another over your network. no playing with CVS files and no need to convert ID's

[sanver]

You have to import old database and export it to the new database.

Do let me know in case you need help.

Sanjay Verma
Sanver E-Solutions
www.sanver.com
+1 646 736 7546
+44 20 8133 6894

I have a problem with my install of sugar & I don't know if the problem lies with the PHP & web pages or the MySQL database so I set another version of sugar up & customised it in the same way.



Would I be right in thinking I can export all the data from my damaged sugar version & then import this into the new version?



I understand I will need to look up assigned_user_id & modified_user_id SQL keys in the new database & change them on the csv file I will import so they match up.



The only question I have really is: I can also see the following fields with data on my csv file:



id

contact_id

account_id

opportunity_id



If I import all the contacts, accounts, meetings, tesks etc... in the same way will everything link up in the end using the same SQL keys or will I encounter issues?



Also would it be better to change each user's SQL key in PHPMyAdmin to what they used to have in the old database instead of changing them on the CSV file's?



Or can anyone else suggest a better way of doing this whole import?

[Deland01]

I downloaded Navicat & it looks pretty simply from the tutorials they have on the site.




I'll give you a shout if I get stuck.

Thanks.

[Deland01]

Tried to import the data from the old DB to the new one & because I imported all the tables its messed things up again. The SQL keys for the config must be different.

All I want to import are the following:

Leads
Opportunities
Accounts
Calls
Meetings
Tasks
Contacts
Documents - not too bothered about this

Based an this do you know which tables I would need to import, or which not to? I have a rough idea about a few of the tables but I'm not 100% sure which ones I need to just give me the data, users & none of the custom settings & config as this has all been re-done.



The ones I have selected so fare are listed below in red:

accounts
accounts_audit
accounts_bugs
accounts_cases
accounts_contacts
accounts_cstm
accounts_opportunities
acl_actions
acl_roles
acl_roles_actions
acl_roles_users
bugs
bugs_audit
calls
calls_contacts
calls_cstm
calls_users
campaign_log
campaign_trkrs
campaigns
campaigns_audit
cases
cases_audit
cases_bugs
config
contacts
contacts_audit
contacts_bugs
contacts_cases
contacts_users
currencies
custom_fields
dashboards
document_revisions
documents
email_marketing
email_marketing_prospect_lists
email_templates
emailman
emails
emails_accounts
emails_bugs
emails_cases
emails_contacts
emails_leads
emails_opportunities
emails_project_tasks
emails_projects
emails_prospects
emails_tasks
emails_users
feeds
fields_meta_data
files
iframes
import_maps
inbound_email
inbound_email_autoreply
leads
leads_audit
leads_cstm
linked_documents
meetings
meetings_contacts
meetings_users
notes
opportunities
opportunities_audit
opportunities_contacts
opportunities_cstm
project
project_relation
project_task
project_task_audit
prospect_list_campaigns
prospect_lists
prospect_lists_prospects
prospects
relationships
releases
roles
roles_modules
roles_users
saved_search
schedulers
schedulers_times
tasks
tracker
upgrade_history
user_preferences
users
users_feeds
users_last_import
users_signatures
vcals



Can you think of any that might need to come off or be added?

.

[sanver]

WE can do it for you for a small fees. If level of customisation is Nil or negligiable, the cost will be very low. Do write back to me at crm.sales@sanver.com

Sanjay Verma
Sanver E-Solutions
www.sanver.com
+1 646 736 7546
+44 20 8133 6894

Tried to import the data from the old DB to the new one & because I imported all the tables its messed things up again. The SQL keys for the config must be different.

All I want to import are the following:

Leads
Opportunities
Accounts
Calls
Meetings
Tasks
Contacts
Documents - not too bothered about this

Based an this do you know which tables I would need to import, or which not to? I have a rough idea about a few of the tables but I'm not 100% sure which ones I need to just give me the data, users & none of the custom settings & config as this has all been re-done.



The ones I have selected so fare are listed below in red:

accounts
accounts_audit
accounts_bugs
accounts_cases
accounts_contacts
accounts_cstm
accounts_opportunities
acl_actions
acl_roles
acl_roles_actions
acl_roles_users
bugs
bugs_audit
calls
calls_contacts
calls_cstm
calls_users
campaign_log
campaign_trkrs
campaigns
campaigns_audit
cases
cases_audit
cases_bugs
config
contacts
contacts_audit
contacts_bugs
contacts_cases
contacts_users
currencies
custom_fields
dashboards
document_revisions
documents
email_marketing
email_marketing_prospect_lists
email_templates
emailman
emails
emails_accounts
emails_bugs
emails_cases
emails_contacts
emails_leads
emails_opportunities
emails_project_tasks
emails_projects
emails_prospects
emails_tasks
emails_users
feeds
fields_meta_data
files
iframes
import_maps
inbound_email
inbound_email_autoreply
leads
leads_audit
leads_cstm
linked_documents
meetings
meetings_contacts
meetings_users
notes
opportunities
opportunities_audit
opportunities_contacts
opportunities_cstm
project
project_relation
project_task
project_task_audit
prospect_list_campaigns
prospect_lists
prospect_lists_prospects
prospects
relationships
releases
roles
roles_modules
roles_users
saved_search
schedulers
schedulers_times
tasks
tracker
upgrade_history
user_preferences
users
users_feeds
users_last_import
users_signatures
vcals



Can you think of any that might need to come off or be added?

.

[Deland01]

Thanks for the offer but I've managed to sort it now. Just needed to create the users in the new DB & give them the same SQL key as in the old DB. Works a treat.

[mtfrog77]

Glad to hear you got it working, sorry I didn't replay sooner.

[Deland01]

mtfrog77,

Do you use Navicat at all or just heard about it? I could do with a hand with some more data imports.

I have all my data back in sugar but need to update the records in there with newer details.

My sales team use an application called ACT so I exported there ACT data to CSV format. I imported the CSV into the Leads module of a cloned sugar (which contains no previous data). I then exported the Leads table in Navicat to a txt file.

I then imported this Leads table (which contains only the new leads) into my original sugar database in Navicat using the import wizard. I ticked the "Primary Key" field next to "Account_name" so it would match all the accounts up with the "account name" and not the "id" as I noticed the "id" column for each lead is different from the ones in the original sugar database.

I then chose "Append/Update" and got the following error:



[2007-04-18 16:35:13] [Msg] Import start

[2007-04-18 16:35:13] [Msg] Import type - Text file

[2007-04-18 16:35:13] [Msg] Import from - C:\Documents and Settings\administrator.CCM-CA\Desktop\leads_hellenmilson.TXT

[2007-04-18 16:35:13] [Msg] Import table [leads]

[2007-04-18 16:35:13] [Err] 1406 - Data too long for column 'primary_address_postalcode' at row 1

[2007-04-18 16:35:13] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('10fd1444-e6d1-2418-f4b5-46262fc06ee6','0','0','0','0','2007-4-18 14:45:24','2007-4-18 14:45:24','1','1','ee85d1d4-87ed-15c8-dda2-456c4b2c4f3c','Lynn','Diplock','New','020 - 77158000','11 Westferry Circus',' Canary Wharf',' London',' E14 4HE Canary Wharf London',null,null,null)

[2007-04-18 16:35:13] [Err] 1292 - Incorrect datetime value: '0' for column 'date_entered' at row 1

[2007-04-18 16:35:13] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('','0','0',null,null,'0','0',null,null,null,null, null,null,null,null,null,null,null,null,null,null)

[2007-04-18 16:35:13] [Err] 1292 - Incorrect datetime value: '0' for column 'date_entered' at row 1

[2007-04-18 16:35:13] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('Accounting Date = 19-04-2007','0','0',null,null,'0','0',null,null,'0141 - 2255900',null,null,'G2 2SD','Group engaged in the rental of temporary power',null,null,null,null,null,null,null)

[2007-04-18 16:35:13] [Err] 1406 - Data too long for column 'primary_address_postalcode' at row 1

[2007-04-18 16:35:13] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('19c989ee-6ffe-8dcf-c6ac-46262f490c98','0','0','0','0','2007-4-18 14:45:24','2007-4-18 14:45:24','1','1','ee85d1d4-87ed-15c8-dda2-456c4b2c4f3c','Phil','Carr','New','0870 - 608 2372','Sovereign House',' Rhosili Road',' Brackmills',' Northam Rhosili Road Brackmills',null,null,null)

[2007-04-18 16:35:14] [Err] 1406 - Data too long for column 'primary_address_postalcode' at row 1

[2007-04-18 16:35:14] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('37025c87-aa3c-37e0-8a53-46262f301029','0','0','0','0','2007-4-18 14:45:24','2007-4-18 14:45:24','1','1','ee85d1d4-87ed-15c8-dda2-456c4b2c4f3c','Catherine','Williams','New','020 - 78733000','Number One',' Southwark Bridge',' London',' SE1 9HL Southwark Bridge BERMONDSEY',null,null,null)

[2007-04-18 16:35:15] [Err] 1406 - Data too long for column 'primary_address_postalcode' at row 1

[2007-04-18 16:35:15] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('9973bbbc-90d5-4b3f-0412-46262fdbe16c','0','0','0','0','2007-4-18 14:45:24','2007-4-18 14:45:24','1','1','ee85d1d4-87ed-15c8-dda2-456c4b2c4f3c','Steven','Kidd','New','01923 - 233366','Axis 1 Rhodes Way',' Watford',' Herts',' WD24 4FD Watford Herts',null,null,null)

[2007-04-18 16:35:15] [Err] 1406 - Data too long for column 'primary_address_postalcode' at row 1

[2007-04-18 16:35:15] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('ee83cac2-c836-168d-3686-46262f6d0e2f','0','0','0','0','2007-4-18 14:45:23','2007-4-18 14:45:23','1','1','ee85d1d4-87ed-15c8-dda2-456c4b2c4f3c','Steve','Barrow','New','01935 - 414141','Mead Avenue',' Houndstone Business Park',' Yeovil',' Som Houndstone Business Park ',null,null,null)

[2007-04-18 16:35:15] [Err] 1406 - Data too long for column 'account_name' at row 1

[2007-04-18 16:35:15] [Err] insert into `leads` (`id`,`deleted`,`converted`,`do_not_call`,`email_o pt_out`,`date_entered`,`date_modified`,`modified_u ser_id`,`created_by`,`assigned_user_id`,`first_nam e`,`last_name`,`status`,`phone_work`,`primary_addr ess_street`,`primary_address_city`,`primary_addres s_state`,`primary_address_postalcode`,`description `,`account_name`,`account_description`) values ('ef37d35a-292d-a289-3b98-46262f0d54ae','0','0','0','0','2007-4-18 14:45:24','2007-4-18 14:45:24','1','1','ee85d1d4-87ed-15c8-dda2-456c4b2c4f3c','Anthony','Robin','New','020 - 72166800','Fourth Floor 130 Wilton Road London','London','England','SW1V 1LQ','Building',' building management and services; civil and specialist engineering and services; rail engineering and services and the management of parent company investments and developments.','BALFOUR BEATTY GROUP LIMITED')

[2007-04-18 16:35:16] [Msg] Processed:126, Added:118, Updated:0, Deleted:0, Errors:8

[2007-04-18 16:35:16] [Msg] Finished - Unsuccessfully





I've tried so many different ways of importing the data but it all seams to error in some way.



Any suggestions?