Kavi Help CenterKavi® Members Help
| Appendix A. Preparing CSV Files for Upload | ||
|---|---|---|
 |
Part X. Appendix | 
|
Table of Contents
The Upload Data tool accepts files in comma-separated values (CSV) file format (i.e., files with a .csv extension). The Upload Data tool uses CSV data files to perform batch adds, edits or delete operations.
Before uploading data to the Kavi Members database, be certain that you have followed basic file preparation rules, that the data adheres to the rules of your organization and that your data is properly prepared for the kind of upload action you wish to perform (i.e., add, edit or delete). This document contains instructions to help you prepare data for specific operations.
Further information is available in the Guide to Predefined Data Fields, which provides information on required fields and values that will be inserted by default. For instructions on how to prepare files to perform each type of upload operation, see Instructions for Upload Data Add, Edit and Delete Operations: Populate the Kavi Members database, Add companies, Add users, Add new memberships, Edit companies, Edit users or Delete companies or users.
Back to topBegin by downloading a data file or template. Use a data file if you want to edit or delete records. Use a template if you want to add new records or are setting up a new site and need to populate the database for the first time. There are several ways you can acquire data files or templates.
Table A.1. File and template sources by data type
| Data Type | Action | Source | Description |
|---|---|---|---|
| Company | Populate database | CSV template for adding new companies | Click this link at the top of the Upload Data tool. The template contains column headings but no data. You can also add memberships for the companies you are adding. |
| Add | CSV template for adding new companies | Click this link at the top of the Upload Data tool. The template contains column headings but no data. You can also add memberships for the new companies you are adding. | |
| Edit | CSV data file for editing or deleting existing companies | Click this link at the top of the Upload Data tool. | |
| Company Data Report | This is available through the Reports and Downloads menu. | ||
| Delete | CSV data file for editing or deleting existing companies | Click this link at the top of the Upload Data tool. | |
| Company Data Report | This is available through the Reports and Downloads menu. | ||
| User | Populate database | CSV template for adding new users | Click this link at the top of the Upload Data tool. The template contains column headings but no data. You can also add memberships for the users you are adding. |
| Add | CSV template for adding new users | Click this link at the top of the Upload Data tool. The template contains column headings but no data. You can also add memberships for the new users you are adding. | |
| Edit | CSV data file for editing or deleting existing users | Click this link at the top of the Upload Data tool. | |
| User Data Report | This is available through the Reports and Downloads menu. | ||
| Delete | CSV data file for editing or deleting existing users | Click this link at the top of the Upload Data tool. | |
| User Data Report | This is available through the Reports and Downloads menu. |
Upload Data tool accepts data in the form of comma-separated values (CSV) files. In a CSV file, each line in the file contains an ordered sequence of values separated by commas. Each value is encased in double quotes (unless they are single-character values). Unlike standard English, there is no space after a comma. The first row contains the database column headings. Each subsequent row corresponds to a record in the database.
The commas maintain file structure. Each comma in the first row (which contains the column headings) delimits a column and a place in the ordered sequence of columns. Commas in subsequent rows also maintain the sequence of ordered columns, so the first value in every subsequent row represents a value in the first column, the second value in every subsequent row represents a value in the second column and so on.
The whole system breaks down if there is a missing or extraneous comma in a row. Every value after that missing or extra data field will be entered into the wrong column. In worst case scenario, the database may be corrupted so seriously that it's necessary to revert to a backup version, resulting in a loss of the most recent data changes and a colossal headache for the admin, so it's very important to maintain file structure.
Simplified example of a CSV file used to add company data:
The first line of the file contains an ordered list of column headings.
The second line contains the first record, with the data in each field corresponding to the column heading that occurs in the same order in the sequence.
In this example the second record (in the third line of the file) contains an empty 'url' field, but the field is delimited by a placeholder comma to maintain the file structure.
Fields should only be left empty if the type of data stored in that field is optional for your organization, or if an appropriate value will automatically be assigned during the upload process by default. For information on whether an empty field will be filled by default and what value will be inserted, see the Guide to Predefined Data Fields.
"upload_action","name","url","signup_date"
"add","Kavi","http://www.kavi.com","1997/05/28"
"add","Example Company",,"2001/03/18"
The columns in your CSV file can be in any order as long as the sequence is maintained so that fields in subsequent lines occur in the same order as the column headings in the first row.
You may also omit any columns that aren't required by the Upload Data tool or configuration unless you want to add or edit data in that column. It's actually a good practice to omit optional columns to simplify the structure of your data file and reduce the likelihood of introducing errors in those unnecessary columns. But if the column is required and the default value isn't appropriate for the records you are adding or editing, you should include that column and supply the appropriate values.
Those who are experienced at maintaining CSV file structure in text editors can use whichever text editor they prefer, but most users will find it easiest to use a spreadsheet program, even though you have to clean up after some spreadsheet applications that insert unwanted cruft (e.g., newline characters) into your nice clean data files. You won't even be aware that your spreadsheet application is doing this until you export the file to CSV and view it in a text editor. Characters that were hidden when the file was viewed through the spreadsheet application are suddenly visible when the file is viewed through a text editor. Unfortunately, this is true of a spreadsheet application that is otherwise a favorite of many users, so you should test it.
Test your spreadsheet application to see whether it is well behaved and exports clean CSV files. If it doesn't, you can either plan to clean up the file in a text editor or find a spreadsheet application that exports cleanly, such as OpenOffice Calc.
How to test your spreadsheet application:
Import a CSV file into your spreadsheet software, edit it and save in .csv format.
Open the file in a text editor and look to see whether the field values are demarcated by double quotes (except for single/character values)—this is a Good Thing—and there are no extraneous characters—which is a Bad Thing. The most common hidden character inserted by spreadsheet applications is a newline character, so look for '/n' or '^n' at the ends of rows. If you do see double quotes and don't see newline characters (or other oddities), proceed to the next test.
Use the Test Run feature to perform a test upload on your file so the edits you made in your practice file aren't committed to the database. If everything works as expected and the Upload Data report doesn't mention that any extraneous characters were encountered, you should be able to export cleanly to CSV from this spreadsheet application.
If you have to use a spreadsheet application that inserts extraneous characters in your CSV files, you will need to clean up the file after exporting it from the spreadsheet application. Open the CSV file in a text editor and remove the unwanted characters.
Note
If you are using OpenOffice and are having problems opening an .xls (Microsoft Excel Spreadsheet) file, see the OO and .xls FAQ.
Simplify your data file or template by removing any columns that aren't required by the Upload Tool, aren't required by the database and otherwise aren't useful under the circumstances. This includes all membership-related columns (except 'member_types' which actually stores User Types), since membership data cannot be edited through this tool.
Add the 'upload_action' column and specify which action you want performed on that record (i.e., 'add', 'edit', 'delete').
If you downloaded a data file and are performing an edit or delete operation, remove any rows (records) that you want to remain unchanged (i.e., rows you don't want to edit or delete).
Perform Basic data checking as described in the following section.
When you have completed these steps, finish your data preparation by following the instructions for the specific operation you're performing as described in Add operations or Edit and delete operations.
It's important to do some basic data checking before, during or after importing data into the CSV file— whatever is most convenient for you.
Check for these and similar common errors:
To eliminate the most common cause of errors, be careful to preserve the proper structure in the comma/separated file.
-
Be sure the 'upload_action' column is present and correct values have been assigned as required by the Upload Data tool. It is included as the first column in the templates and data files, but if you have acquired the data through reporting tools or other sources, you need to add an 'upload_action' column.
Fields can't be left empty in the 'upload_action' column. Every field must contain a value specifying which action is to be performed on that row: 'add', 'edit' or 'delete'. Records where no data is to be changed can be designated by 'none', but it's easier to omit these rows entirely. Upload action values must be in all lowercase letters.
If you are adding records, always check for duplicate entries and remove any you find. The Members database will not allow the same exact name to be entered twice, but duplicates will occur when a company or user who already exists in the database is entered again under a slightly different name (e.g., 'Example Company' and 'Example Co.', 'John Q Public' and 'Jon Quincy Public').
Check the format of all fields containing dates. The correct date format is YYYY/MM/DD for all date-related fields. The one exception is the 'membership_start_dates' and 'membership_end_dates' which may contain dates for multiple memberships as a comma-delimited string. Other formats will not be rejected during validation, but the Upload Data tool will attempt to force the data in the date fields into the YYYY/MM/DD format, which will result in the corruption of data in the date fields.
Eliminate any special characters that could cause the upload to fail, such as non/English language characters or curly quotes.
|
|
 |
|
| Part X. Appendix |  |
Appendix B. Predefined Data Fields |