Kavi Help CenterKavi® Members Help
| Appendix E. Sample Company Data Files for the Upload Data Tool | ||
|---|---|---|
 |
Part X. Appendix | 
|
Table of Contents
The first sample data file is presented as it would appear in a text editor. Other samples are presented in tables to reflect the way the data file would appear in a spreadsheet.
A CSV data file such as 'company.csv' (which can be downloaded through the Upload Data tool) will look something like this when viewed in a text editor. The first line contains the column headings and the second and subsequent lines contain data records. The first column heading is 'upload_action'. The first field in every subsequent row corresponding to the 'upload_action' heading is prefilled for the 'edit' action.
"upload_action","item_key","name"
edit,"uv1618033989","Kavi Corp."
edit,"uv0618033990","Nonmember Co."
edit,"fs12358132134","Member Company, Inc."
To prepare this file for upload you would remove any lines (i.e., records) you didn't want to change. If you were going to delete any records you would change the 'upload_action' from 'edit' to 'delete'. If you were going to add records, you would have to add more columns, including those required for Upload Data 'add' actions (i.e., 'purpose').
A data file used to add companies when populating the database for the first time should look something like this. Since companies must be added before users can be added, these companies include a staff company, a company created to group individual members, a couple of nonmember companies (in this case, a company that's part of the adopter community and an academic institution) and a member company that prefers not to be displayed on signup or have their roster displayed (since this member was added without a membership, the membership would have to be added later as part of an 'edit' action as shown in Data file to add new memberships by editing companies).
This example only includes required fields. In reality, you'd want to add all available information for these companies, so your data file would have many more columns. You would probably want to upload member companies and memberships as well. For an example of a data file used to add preexisting and new memberships as companies are added, see the next sample: Data file to add new companies and memberships.
Sample file description:
The first row contains the column headings and the second and subsequent rows contain records of companies being added to the database.
The first new company is legal staff rather than a member company.
The second is a company that doesn't exist in the real world, but is added to group individual members so they can be managed effectively in Kavi Members.
The third is a nonmember company that has been classified as an adopter by assigning it the 'Adopter' Company Type. Because this nonmember company became associated with the organization before the database was created, the value for the 'start_date' is set to the actual date of this event.
The fourth record belongs to an active member company that wishes to remain hidden from rosters and the Company Representative Signup form. In this example, the company membership is going to be added in a different batch upload.
Table E.1. Sample data file for adding companies
| upload_action | name | purpose | display_publicly | show_on_signup_form | status | signup_date | company_type |
|---|---|---|---|---|---|---|---|
| add | Legal Staff | Staff or Administration | 0 | 0 | 1 | Legal | |
| add | Individual Members | Company for Individual Members | 1 | 1 | 1 | ||
| add | Existing Nonmember, LLC. | Nonmember Company | 0 | 0 | 1 | 2000/01/01 | Adopter |
| add | Hidden Member Co. | Member Company | 0 | 0 | 1 |
If you are adding memberships for companies, your data file will look something like this. For simplicity's sake, this example includes only the fields that the Upload Data tool requires when adding company memberships plus the 'signup_date' field. When this file is uploaded, other fields required by the database (i.e., 'display_publicly', 'show_on_signup_form', 'status' and 'company_type') will be set to the default values. All of this organization's memberships have a one year duration, and the membership terms begin and end on the first day of each year.
Sample file description:
The first row contains the column headings and the second and subsequent rows contain records of companies and memberships being added to the database.
The first record belongs to an applicant whose new membership will go through the normal membership workflow.
The second is a current member who joined the organization at the beginning of this year. If adding memberships that are already current but the membership type has billing or moderation steps, the memberships won't go current until the memberships are moderated and any membership bills are paid. Organization Admins can use the Company Membership Applications tool to moderate these memberships in bulk.
The third is a long term member whose prior memberships are added to the database along with their current membership (the current year is 2006) to provide a historical record of their involvement with the organization. Memberships can only be added when populating the database or adding a company's first time membership. Once a company has acquired membership, the next membership they acquire is generally considered a renewal, and in order for the current and renewed membership to be properly recorded as part of the series of memberships held by that member, renewed or replaced memberships must go through the automated membership workflow. This example represents the only exception to that rule: adding legacy memberships that existed prior to the creation of the Web site to establish a historic record of these memberships in the Kavi Members database. When viewing the CSV file through a text editor, the series should be encased in doublequotes like this: "Membership Historic 2005,Membership Current","2005/01/01,2006/04/12","2006/01/01,Lifetime".
The fourth is an member company that allowed their membership to lapse on the first of the year. In this example, the lapsed member is added as an active company. If this organization enforced membership rules more strictly, this former member might be added as as an inactive member, in which case the data file would have to include the 'status' column so the former member could be added as an 'inactive' company. Because this value isn't specified in the data file, this company will be assigned the default value of 'active'.
Table E.2. Sample data file for adding companies and memberships
| upload_action | name | purpose | membership_types | membership_start_dates | membership_end_dates |
|---|---|---|---|---|---|
| add | Applicant Company, Inc. | Member Company | Regular | ||
| add | Current Member, Ltd. | Member Company | Regular | 2006/01/01 | 2007/01/01 |
| add | Longtime Member, LLC. | Member Company | Sponsor, Sponsor, Regular | 2006/01/01,2005/01/01,2005/01/01 | 2007/01/01,2006/01/01,2004/01/01 |
| add | Lapsed Member Co. | Member Company | Regular | 2005/01/01 | 2006/01/01 |
If you need to edit a large percentage of the records in the database you might choose to download the 'company.csv' file through the Upload Data tool. If you were only going to edit a small percentage of records in the database, you might choose to run a report such as the Company Data Report and use search criteria to narrow the set of records retrieved through the search.
Sample file description:
Imagine an organization has just decided to change Kavi Members configuration by enabling address fields, so it's adding addresses for companies that are already in the database. The first row contains the column headings and the second and subsequent rows contain records of companies retrieved from the database.
The required 'upload_action' column will already contain the value 'edit' if the file was downloaded through the Upload Data tool, otherwise the column and values will have to be added.
The 'item_key' column is also required. These keys must never be edited. If you accidentally edit this field, you will not be able to upload this record. Remove the record and download it again with the item key intact.
The 'name' column isn't required by the Upload Data tool, but the person adding the address will want to use the company name to determine which company the record is for so they know where to add the address.
The remaining columns will be used to collect the address information.
Table E.3. Sample data file for editing companies
| upload_action | item_key | name | street | city | state_province | country | postal_code |
|---|---|---|---|---|---|---|---|
| edit | 656565 | Current Member, Ltd. | 123 Alberta St. | Marnie | IL | USA | 65656 |
| edit | 565656 | Longtime Member, LLC. | 456 Baker St. | Vancouver | BC | Canada | V5M 6M5 |
If you are adding memberships by editing companies that are already in the database, begin by downloading data through the Upload Data tool 'company.csv' data file link or through a report such as the Company Data Report. If you use a report, you might set the search criteria so that only companies with the 'Member Company' purpose are returned if you are adding memberships as a separate step when populating the database, or 'Nonmember' if the organization has been tracking potential members who have decided to join the organization. Be sure the 'item_key' field is included in the report results. If you downloaded the 'company.csv' data file through the Upload Data tool you could sort the results by the 'purpose' column heading and remove companies that don't have the appropriate purpose. Remove any other records you don't want edited and any unnecessary columns, then add the memberships.
Sample file description:
The first row contains the column headings and the second and subsequent rows contain records of company memberships being added to the database.
The required 'upload_action' column will already contain the value 'edit' if the file was downloaded through the Upload Data tool, otherwise the column and 'edit' values will have to be added.
The 'item_key' column is also required.
The 'name' column isn't required by the Upload Data tool, but you will want to use it to determine which company you are adding memberships for.
The 'purpose' column was included in the data file to sort out companies with the desired purpose. If you are populating the database and only adding memberships to member companies, you may remove this column once the records of companies with other kinds of purposes have been removed. If you are adding a membership for a company that is classified as a nonmember in the database, change the value in the 'purpose' column from 'Nonmember' to 'Member Company'.
-
The last three columns are required when adding memberships, but with the exception of the 'membership_types' field, may be left blank. The 'membership_types' field contains the name (or names) of the membership type(s) being added. The 'membership_start_dates' field is required only if the start date is in the past. The 'membership_end_dates' field is optional unless you are adding historic memberships. In most cases, omitting the end date is both foolproof and the easiest approach, since this value will be set automatically as the uploaded membership goes through the normal membership workflow. You want to omit the start date for the same reason unless you specifically want to override the default.
If multiple memberships are added to create a historic record of memberships that existed prior to the creation of the database, they are added as a comma-separated series. Dates in the 'membership_start_dates' and 'membership_end_dates' field must appear in the order in which the memberships appear in the 'membership_types' field, so that the correct dates are assigned to each membership. If a current membership is included in this series, the end date doesn't need to be specified, but the placeholder comma should be included to maintain the structure of the ordered series. In the CSV file, each series is encased in doublequotes, like this: "Membership Historic 2005,Membership Current","2005/01/01,2006/04/12","2005/01/01,Lifetime".
The first record shows a single membership being added for a company. This company is applying for a new membership so the 'membership_start_dates' and 'membership_end_dates' fields have been left blank.
The second record shows a single membership being added for a company that is a founding member of the organization. This company's membership already existed at the time the database was created, so the start date is in the past. A 'Founding' membership is a lifetime membership, so the value in the 'membership_end_dates' field is set to 'Lifetime'. This field could also have been left blank.
Table E.4. Sample data file for editing companies to add memberships
| upload_action | item_key | name | purpose | membership_types | membership_start_dates | membership_end_dates |
|---|---|---|---|---|---|---|
| edit | 555555 | Applicant Company, Inc. | Member Company | Regular | ||
| edit | 555566 | Founder Co. | Member Company | Lifetime | 2006/04/12 | Lifetime |
The delete action is very simple. It is also very permanent. Deleting a company permanently deletes all users associated with that company.
You might want to remove companies from your database that haven't participated in the organization for several years to reduce the database footprint. The criteria you'll use to identify companies that should be deleted is that the company will be inactive and have a database record that hasn't been updated in three years. You could begin by running a report and searching for inactive companies only to minimize the number of files in the report results, then download the results as CSV data file. Now you'll sort the results by the 'company_last_modified' date and remove those where this date is less than three years old, leaving only the records you want to delete.
Sample file description:
This sample shows a set of records for inactive companies that haven't been updated in the last three years. When performing a delete action, the only columns you really need are 'upload_action' and 'item_key'.
This example includes the 'name' and 'company_last_modified' which might have been left in the file for reference by the person preparing the data file. The first row of the data file contains the column headings and the second and subsequent rows contain records of companies retrieved from the database.
It is possible to perform more than one action in a single upload—performing the delete action on some records and the edit or add action on others. For the moment let's ignore the add operation, since it's usually easiest to do this independently. There are two ways to create files with mixed actions. You can selectively retrieve the records you want to delete separately from the records you want to edit, then merge the data into a single file, or you can export a large set of records and manually whittle them down into just those records you want to edit or delete. Prepare the records you want to edit first, since the edit action requires more columns than the delete action.
Imagine the organization just sent a mailer to nonmember companies in the 'Eastern' region to find out which mailers are delivered successfully and which are returned because the company is no longer at that address. A record of the mailing was created in a spreadsheet and results were added. Now these results are used to prepare the data file.
The action to be taken on each record depends on the company status as well as whether the mailer was delivered or not. No action will be taken if the mailer was delivered successfully. If the mailer was returned and the nonmember company is currently listed as active, the company will be deactivated, if the company is already inactive, it will be deleted.
Sample file description:
-
The first row contains the column headings and the second and subsequent rows contain records of companies retrieved from the database. If these records were retrieved through the 'company.csv' link on the Upload Data tool, the first step would be to remove all columns except those that the Upload Data tool requires to perform edit or delete actions (i.e., 'upload_action', 'item_key'), those that need to be edited ('status') and human-readable fields used by the person preparing the file for upload (i.e., 'name', 'region').
Now the rows that won't be changed should be removed. The records can sorted by region and records from other regions (any region that isn't 'Eastern') should be removed. Then the records can be sorted by purpose and any record that doesn't have 'Nonmember Company' in this field can be removed.
Now the preparer combs through each record in the file, removing the record if the mailer was delivered or preparing the record to be edited or deleted if the mailer was returned.
The first record in the sample has been prepared for an edit action. Since the data file was downloaded through the Upload Data tool, the 'upload_data' column value is prefilled with 'edit', so there was no need to change it. The value in the 'status' field had to be changed from 'active' to 'inactive'.
The second record in the sample has been prepared for a delete action. The value in the 'upload_data' column had to be changed from 'edit' to 'delete'.
|
|
 |
|
| Appendix D. Kavi Members Default Types |  |
Appendix F. Sample User Data Files for the Upload Data Tool |