Click Here to view this article as a PDF.
Author: Kristenson, Joel
Last Updated: 2016-01-27
Overview
This article will walk you through the steps to import expenditures into your campaign database. This used to require that our support staff ran the import for political customers internally but the program was upgraded at the end of 2016 to allow end-users to import their own expenditures. This KB provides an example spreadsheet showing how to structure your data, the steps involved, the required fields for the import to work, and some best practices along the way.
Tip: Interested in learning all about importing? Watch this video playlist, and read these articles to get up to speed.
Steps for Prepping your File
Download this spreadsheet template to use as an example when preparing your expenditure data for import: http://www.trailblz.com/kb/images/kb0355/expenditure-import.xlsx
Format your expenditure data so it’s consistent with the example. My finished example is also available for download here.
You can email that spreadsheet to support@trailblz.com if you need a confirmation that the data is mapped properly, or call us at 1-866-909-8700.
Once the file is prepped you’ll need to save it as a .csv and close the file. Store it at a location on the machine that’s easy to remember (ex: Temp folder).
Steps to Run the Import
1) Start a new session of Trail Blazer so that if the import gets botched it’s easier to roll-it-back.
2) Navigate to Financial > Disbursements > Payees (Invoicing).
3) Your current list of payees will populate automatically. Select File > Import from the tool strip at the top.
4) Make sure the Import File Type is set as CSV (Comma Delimited Text). Select ‘Expenditures’ from the drop-down for the Type. Click [Browse] to select the prepped spreadsheet file to import.
5) Navigate to the location where the prepped .csv spreadsheet is located, select it, and click [Open]. If you don’t see the file, make sure that CSV Files is selected in bottom-right. My example is below.
6) Click [OK] once the file has been selected.
7) The Add Expenditures window will open. Click [Match Fields].
8) *If you named the column titles in your spreadsheet the same as the example (at the top of this article) the columns will automatically map. Otherwise click the corresponding drop-downs and select the appropriate field to map too from the list (Ex: Check Number = CheckNumber). My example is below.
9) Once the fields are mapped correctly click the [Pre-Import Check] button (shown in the screenshot above).
10) Once the pre-import check is finished running you’ll be prompted with the results. *if you have errors they will need to be fixed before you can run the import. Warnings should also be fixed but they won’t prevent the import from running. In my first attempt I had 36 errors as shown below.
11) If you have any errors or warnings click [OK] to open the output file. The file will automatically be saved onto your machine and the directory will be shown.
12) *If you do have errors to fix you’ll need to first close out of the column mapping screen in Trail Blazer by clicking [Cancel]. My example is below. *If you don’t have any errors you can skip this step and proceed to the importing of your data.
13) Widen out the columns to view what the errors and warnings are. In my example the dollar signs in the amount column are not supported and the errors mention that there are non-numeric values that need to be fixed.
14) Compare this error file with the original file. Make the corrections and save-and-close the .csv file. Once those steps are completed you can begin the import/column mapping process again.
Important Note: The pre-import check will look for this missing information which is *required to run an expenditure import (even if the data in the column is blank):
- Company Name
- First Name
- Last Name
- Date
- Amount
- Street
- City
- Zip
- Description
- ExpenditureTypeID
- CategoryCodeID
- Election Cycle Name (a.k.a. cycle for non-profits)
It will also check for this:
- Duplicate column mapped from (source file column names)
- Duplicated column mapped to
- No rows of data
- Too few items in a line of data
- No or non-numeric Expenditure Type ID
- Expenditure Type ID outside range of Trail Blazer’s values (currently 1-15):
- No or non-numeric Expense Category ID
- Expense Category ID that doesn’t match any in Trail Blazer. Currently they are:
Tip: You can add new expenditure categories by following System Manager > Lists > Expenditure Categories > [+ New].
- Non-date data for Date
- Non-decimal amount for amount
- No election cycle set up in Trail Blazer matching the one in the data
15) Continue running the pre-import check until zero errors occur. My example is below after making the fixes.
16) Once the pre-import check passes, click [Import].
17) You’ll be prompted with a pop-up message asking you want to skip any rows. Click [No] to proceed.
18) Let the import run and click [OK] once you’re prompted with the final results. My example is below where 33 records were created.
19) *If any records weren’t created you’ll be prompted with another pop-up message showing where the file is saved. Click [OK] to open it and view the results. Example below.
20) Navigate back to the Payees List and click [Search] to refresh the list and verify the results. In my example about 30 new payee records were created.
Tip: Setup a training class with one of our financial trainers to learn how to correctly manage your finances in your database, and how to file FEC reports (if a federal campaign or PAC).
Review the related resources below to learn more about importing and the other financial tools in Trail Blazer.
Related Resources
Article: How to Enter an Invoice and Payment with the New Payment System, and How to Add a Refund and Deduction
Article: Delete an Invoice/Payment
Article: How to Enter an Invoice for a Payee
Article: How to Enter an Expenditure Refund
Article: Do I have to Create an Invoice Before I Create a Payment
Article: Expenditure Reimbursement Schedule A itemization
Article: How to Un-Deposit a Contribution (Primarily for Political Customers)
Article: Delete a Contribution
Article: Import Templates
Article: Pre-Import Check
Article: Start Import – Importing Data From Access – Add/Update Voter
Article: Import Information – Definitions and Column Data Type
Article: Field/Column Mapping When Importing Voter/Donor Data
Video: Importing 102 Importing your first list
Video: Importing 201 Merge or Do Not Merge during import
Video: Importing 101 What to avoid
Video: Importing Contributions
Video: Attributes – Import with Date and Value
Video: Excel- Leading0s – Retaining When Opening CSV Files In Excel
Video: Enter Invoice and Make Immediate Payment
Video: Credit Card Memo Entry (using 2015 version of Financial)
Video Playlist: Importing
Trail Blazer Live Support
Phone: 1-866-909-8700
Email: support@trailblz.com
Facebook: https://www.facebook.com/pages/Trail-Blazer-Software/64872951180
Twitter: https://twitter.com/trailblazersoft
* As a policy we require that you have taken our intro training class before calling or emailing our live support team.
Click here to view our calendar for upcoming classes and events. Feel free to sign up other members on your team for the same training.
* After registering you’ll receive a confirmation email with the instructions for how to log into the GoToMeeting session where we host our live interactive trainings.
* This service is included in your contract.
