From RPM Wiki
Adding or updating information in RPM from a Microsoft Excel file. This page contains specific rules for preparing Excel files for import into RPM and expands on the Import page.
| Table of contents |
The file
Microsoft Excel workbook
The data is imported by uploading an Excel file (a workbook).
Version
The RPM servers use Microsoft Excel 2003. In theory, any program that creates a Microsoft Excel format file should work. This includes past and present versions of Microsoft Excel. Internally we have run tests of versions 2003, XP, 2000, and 97 with no problems.
- In reality, using a an old version or non-Microsoft program may introduce something that causes an error. See "New file" below.
- The .xlsx format of Office 2007 is not supported.
First worksheet
The workbook must contain a worksheet. The worksheet used by the importer is the first worksheet in the file.
- A workbook may have additional worksheets that aren't immediately visible because of the way Excel lists worksheets. If the last worksheet is selected, there may only be a tiny triangle visible indicating a previous sheet. Be sure to check this if your import is generating unexpected results.
- Also, make sure there's no hidden sheets. Format > Sheet > Unhide.
New file
An Excel file generated from another system may have cells that reference other pages (or systems), strange formulas, or version incompatibilities. This can be hard to notice because these things can be well hidden If you can't get an Excel file to import properly, try copying and pasting the information into a new Excel file.
Rows
Headers
Row 1 contains the column names. Column names must be exact.
Data
Rows 2 and on contain the information to be imported. One row per form, agency, etc.
Cell format
References
When a cell defines a relationship to something else in RPM, the name must match what's in RPM exactly.
- Example: The "Agency" column for the rep import.
- See Company Name Format
Numbers
Leading zeros are removed from numbers. To force leading zeros to be imported, like maybe for an account number, put a ' in front of the number.
- Example: 000123 becomes 123
- Example: '000123 becomes 000123
- More information: Leading zeros in Excel
If you are importing a number, money, or percent column make sure the cells are not being formatted as text in Excel.
Calculated fields
Formulas should be rounded to avoid potential very small numbers.
- ROUND(formula, digits)
- This has led to rare errors in the past. For example, one subscriber had a simple division formula that in one case resulted in the number "1.6808E-05" and this was causing a server error during import.
Data fields & custom fields
The name of the column is the name of the field and must match exactly.
- Example: A field called "My description" would have a column header of "My description"
State/Province
The state or province name must appear in full with the correct spelling or in the standard 2 character abbreviation (case insensitive).
Zip code/Postal code
Up to 12 character long.
Percents
RPM reads percent values the same way Excel does. Percent type fields are same meaning 50% is read as 50%. Using Excel’s percent type is the recommended way of working with percents in Excel. If the column is a number then RPM reads percents the standard way, 1 = 100%.
- Example: 0.5 will become 50%
- Example: 10.5 will become 1050%.
Salutation
If there is a problem with the salutation treat it as n/a for create, ignore for complete and include it in the cell warnings on the finished page.
Phone numbers
User logon
See Importing users
Yes/no
- "0", "N", and "No" (case insensitive) are interpreted by RPM as "No"
- Any other text is interpreted to indicate the "Yes" selection.
- If nothing is found in the cell, neither "Yes" or "No" are interpreted by RPM. You cannot update an already populated "Yes/No" field with no selection.
Formula bar
If there is ever doubt about how RPM will interpret the contents of a cell when performing an import just click the mouse on the cell in question and observe how the value appears within the formula bar at the top of the worksheet:
Hidden columns
We've seen cases where users have reported RPM column errors when the Excel file appeared to be fine. Upon further inspection, we found there were actually hidden columns. RPM reads hidden columns and treats them as a normal column.
Macros
The data from the supplier will usually need to be formatted first. We provide some Excel Macros for common supplier formats.
- This page was last modified 17:07, 29 Oct 2007.
- This page has been accessed 3569 times.