When importing data into VerticalChange, it is important that your data is in a format that the system recognizes. Let's go over the requirements needed to do a clean import.
Getting your data into Excel CSV format
In Excel, you can get your data into a CSV format by using the “save as” feature and saving your data with a .CSV (Comma Separated Values) extension. Go to File>Save As, and a dialog box will appear.
Choose Comma Separated Values (.csv) and save your document. This will be the document you use to import your data.
Getting your data into a tabular format
Tabular format means that each line represents one instance of data. For example, if Jim Smith went to 12 workshops, his data should be represented on 12 lines. Columns represent the questions present in the form. Line 1 should be the header column. Like this:
Some rules of Tabular Data
Every record is housed on one row.
Each Column contains a type of data e.g. date, order number, quantity, amount, salesperson, region etc.
There are no blank rows or columns.
Column labels are in one cell per column and located on the first row.
There are no subtotals interspersed in the data.
Prepare data to match exactly what is in Vertical Change
For example, if you want to import data for this multiple choice question:
Question: What is Your Gender?
Response options: male, female
You will not be able to import data that has the response options of “M, F” in this case, or the import will not work. You must re-format your data in Excel to match exactly what is in the system. So in this case, You will need to change all instances of “M” to be “male”.
Make sure all multi-select fields are represented as columns
If data lives in a multi-select field in VerticalChange, each response option should be represented as a different column in your Excel File, with Yes or No in the corresponding cell.
In the following example, the question in VerticalChange is "Services Rendered" - and the selections are:
Therapy
Medication
Consultation
This should be reflected in Excel like this:
If importing from an external database, you must include a unique ID assigned to each contact.
When importing records that do not have a set unique IDs established, you must create some for your dataset and include them in your Excel file. You can do that by numbering each contact 1, 2, 3, 4, etc. The ID for each client must be indicated on every file you send over. For example, if you are sending a Demographics file, and John Jones has the ID 123 reflected in that file, and John Jones also received services, you must include 123 in the Services file as well for each instance he received a service.
Formatting for Certain Fields
There are other rules for how certain fields should be formatted, including:
Phone Numbers - please remove any punctuation such as dashes - , slashes /, and parentheses ( ). Phone number should only be the numbers
Numeric Fields - please ensure that any numeric fields you are importing do not contain any punctuation, such as commas, periods, or dashes
Address Fields - if importing into an address field, each section of the section needs to be in its own column. For example, create separate columns for Address 1, Address 2, Zip Code, City and State.
Check your files for bad characters
When using data from an external source, like a different database or web service, it's possible that bad characters can sneak in to your file.
Make sure to scan cells that have a lot of text for any of these bad characters, typically looking like symbols. If you find these, delete them from the text.
You can also use tools like OpenRefine to better sift through your data to look for bad characters.