I'm looking for structure information on complex .CSV files that have custom fields, relationships, groups. I'd like to get a better sense of best practices for organizing data ahead of time. If you can share a sample file that would be great. You can delete the names but the titles for the .CSV would be informative.
Add a comment
|
1 Answer
Probably below the level of your question, but maybe it'll point you in a good direction?
A sample CSV can be exported from existing CiviCRM data.
This can be done from the CiviCRM demo sites, or if there's a need to frame up columns with custom data, via a CRM with existing data. Export the resulting contacts via CiviCRM Search => Export Contacts action.
The advantage of this approach is that the exported column names automatically map back to what CiviCRM expects[1]. It should apply to both simple and more complex import formats.
CSV sample exported from http://dmaster.demo.civicrm.org/ today -
"Contact ID","Contact Type","Contact Subtype","Do Not Email","Do Not Phone","Do Not Mail","Do Not Sms","Do Not Trade","No Bulk Emails (User Opt Out)","Legal Identifier","External Identifier","Sort Name","Display Name","Nickname","Legal Name","Image Url","Preferred Communication Method","Preferred Language","Preferred Mail Format","Contact Hash","Contact Source","First Name","Middle Name","Last Name","Individual Prefix","Individual Suffix","Formal Title","Communication Style","Job Title","Gender","Birth Date","Deceased","Deceased Date","Household Name","Organization Name","Sic Code","Unique ID (OpenID)","Current Employer ID","Contact is in Trash","Created Date","Modified Date","Addressee","Email Greeting","Postal Greeting","Current Employer","Location Type","Street Address","Street Number","Street Number Suffix","Street Name","Street Unit","Supplemental Address 1","Supplemental Address 2","City","Postal Code Suffix","Postal Code","Latitude","Longitude","Address Name","Master Address Belongs To","County","State","Country","Phone","Phone Extension","Email","On Hold","Use for Bulk Mail","Signature Text","Signature Html","IM Provider","IM Screen Name","OpenID","World Region","Website","Group(s)","Tag(s)","Note(s)","Phone Type","IM Service Provider"
"2","Individual",,,,,,,,,,"Samuels, Jed","Mr. Jed Samuels",,,,"SMS",,"Both","3988235934","Sample Data","Jed",,"Samuels","Mr.",,,,,,,,,,,,,"52",,"2016-08-13 11:07:38","2016-08-13 11:07:42","Mr. Jed Samuels","Dear Jed","Dear Jed","Urban Empowerment Fund","Work","190Y Cadell Ave SE","190","Y","Cadell",,"Attn: Development",,"Bowmanstown",,"18030","40.80116","-75.66158",,"Urban Empowerment Fund","Alameda","PA","United States","265-5630",,"[email protected]",,,,,,,,"America South, Central, North and Caribbean",,"Newsletter Subscribers",,,"Phone",
"3","Individual",,,,,,,,,,"[email protected]","[email protected]",,,,"SMS",,"Both","990362788","Sample Data",,,,,,,,,,,,,,,,,,,"2016-08-13 11:07:38","2016-08-13 11:07:39","[email protected]","Dear [email protected]","Dear [email protected]",,,,,,,,,,,,,,,,,,,,,,"[email protected]",,,,,,,,,,"Newsletter Subscribers",,,,
"4","Individual",,,"1",,,,,,,"Grant, Lawerence","Mr. Lawerence Grant II",,,,,,"Both","2064444768","Sample Data","Lawerence",,"Grant","Mr.","II",,,,"Male",,,,,,,,,,"2016-08-13 11:07:38","2016-08-13 11:07:43","Mr. Lawerence Grant II","Dear Lawerence","Dear Lawerence",,"Home","176T Dowlen Blvd S","176","T","Dowlen",,,,"Boston",,"02266","42.338947","-70.919635",,,"Alameda","MA","United States",,,,,,,,,,,"America South, Central, North and Caribbean",,,"Volunteer",,,
"5","Individual",,,,,,,,,,"Terrell, Russell","Russell Terrell Jr.",,,,"Postal Mail",,"Both","127505299","Sample Data","Russell",,"Terrell",,"Jr.",,,,"Male","1996-05-04",,,,,,,,,"2016-08-13 11:07:38","2016-08-13 11:07:42","Russell Terrell Jr.","Dear Russell","Dear Russell",,"Home","235X States Path NE","235","X","States",,,,"Valrico",,"33595","27.871964","-82.438841",,"Terrell family","Alameda","FL","United States",,,,,,,,,,,"America South, Central, North and Caribbean",,,"Volunteer",,,
"6","Individual",,,"1",,,,,,,"Adams, Scarlet","Scarlet Adams",,,,"Phone",,"Both","2620107037","Sample Data","Scarlet","W","Adams",,,,,,"Female","1957-11-05",,,,,,,,,"2016-08-13 11:07:38","2016-08-13 11:07:40","Scarlet Adams","Dear Scarlet","Dear Scarlet",,,,,,,,,,,,,,,,,,,,"(449) 428-2300",,"[email protected]",,,,,,,,,,"Newsletter Subscribers","Major Donor,Volunteer",,"Phone",
[1] You do have to deselect some of the Addressee fields to get the exported CSV to import straight back in: CRM-19360: Exported CSV does not import back in without adjusting import columns.
