Excel Tips

In this article

Overview

When importing data into Zengine from a spreadsheet or when exporting data out of Zengine to Excel, there are a number of useful tips to make this experience smooth.

Tips

1. If your data has leading zeroes, they will be deleted when your data is exported to Excel.

You have to change the column type to ‘text’ and re-add any zeroes that were removed to match your import data with Zengine.

This is important if you have the ‘no duplicates’ validation turned on in your form.

Example:



To change your column to text, right click on the column header and select ‘Format Cells’:


Select ‘text’ from the number tab and then ‘ok’:

To prevent this from happening: At the very beginning of the number, type an apostrophe (i.e. ‘01234098765). When you press Enter after typing the number, Excel will remove the apostrophe, but leave the telephone number as you typed it. The apostrophe told Excel to treat what followed as ordinary text, not as a number. An unfortunate side-effect is that Excel will put a little green triangle in the corner of the cell and, whenever you select the cell, will display a little information box saying that the number has been formatted as text.


2. Trailing zeros after decimal points will be deleted in Excel.

To change your column to text in Excel, right click on the column header and select ‘Format cells’:

Select ‘text’ from the number tab and then ‘ok’:

To prevent this from happening: At the very beginning of the number, type an apostrophe (i.e. ‘01234098765). When you press Enter after typing the number, Excel will remove the apostrophe, but leave the telephone number as you typed it. The apostrophe told Excel to treat what followed as ordinary text, not as a number. An unfortunate side-effect is that Excel will put a little green triangle in the corner of the cell and, whenever you select the cell, will display a little information box saying that the number has been formatted as text.

3. If you write an integer with more than 15 digits (which is quite feasible), Excel will transform all integers starting with the 16th to zero.

So when you put 1234567890123456789 in a cell, you get 1234567890123450000. The same goes for 1234567890.123456789 that would give 1234567890.123450000! This is by itself quite a drawback, but it doesn’t end there, this limitation impacts all parts of Excel, including calculations. Below is a very simple calculation…

Is there a way around this? The short answer is NO. The long answer is, you can store longer number s text (so begin writing in Excel with an apostrophe), and you will see more than 15 integers, but if you will want to convert them back to numbers and do calculations with them, you will again only work with 15 integers.

The only way to make Excel more precise, is by using an Add-In. There are many out there, here is an example: xlPrecision

Source: Excel Unplugged


4. Trailing spaces

There's a bug with converting text fields to dropdowns with  ‘last name, first name’ type data - it tries to strip out trailing spaces to give you consistent choices, but the trailing space isn't correctly cleared out of some of the records

Real life example: A client's .CSV data has trailing spaces while others didn't (which must have come from the import).; e.g. some rows had "Jones,Alice" and some had "Jones,Alice " (notice the extra space).

So in this example, there's only one valid choice, "Jones,Alice"  So the record that had "Jones,Alice" works correctly, but the one like "Jones,Alice " (with the space) technically doesn't match the choice

5. Dates must be in yyyy-mm-dd format

Example: 5/25/2017 would have to be imported as 2017-05-25

6. States must be imported as their US abbreviation

Example: Pennsylvania must be imported as “US-PA, New York as US-NY, Washington, DC (District Of Columbia) as US-DC, etc.” Take a look at the country and states codes that work within Zengine.

7. Importing into checkbox fields

If you are importing multiple answer options into a checkbox field, they should be separated by a line break within the cell block like this:

Each line will be a value in the checkbox.


8. Your .CSV file may be corrupted and not recognized in our system

If you receive this error:

This likely means that your file format is not recognized as a .CSV.

You can fix this by copying your data and pasting it into a new Excel sheet and then save it as a new .CSV.

9. You may have columns that are missing headers

It’s easy to miss extra columns if you are exporting data into Excel.

Make sure that you scroll through your entire Excel/.CSV file to make sure that there aren’t any extra bits of data in columns that do not have a header.

Still need help? Contact Us Contact Us