Import Transactions in Bulk

Steps to import Transactions in bulk

1 Create and format your Import File
2 Create an Import Profile
3 Run a sample Import Job
4 Run your Import

Related Help Guide Pages

Learn how to add Transactions to your platform from a spreadsheet, using your in-built platform import tool!

If you process payments outside of your accessplanit platform, for example, through an external finance or accounting system, you can import transactions using the Import tool to keep your platform records in sync!

This guide will walk you through the process of importing transaction data, helping to ensure that your platform reflects up-to-date payment information for your customers and bookings.

Please note

The Import tool is available to Super Administrators, if you are not able to access the Import options please speak to a member of the accessplanit team.

Create and format your Import File

This step will show you how to create a .csv file that contains all of the information needed for your Transactions import

To create and format your Import File, you will follow these steps:

Download the Transactions Import Template File (included in this guide)
Format the Transactions Template File
Populate the Transactions Import Template File
Save as a .csv file

Download the Import Template File

In this step you will download our template file for Imports.

There are two files to download to support your import, there is a reference spreadsheet which you can look at to understand the import options, and there is an import template which you will edit and fill with your import data.

Please download our reference Import Profile here to view all of the options for your import file:
1. All mandatory fields are shown in Red
2. All optional fields are shown in Grey
3. This file is a reference file only
Please download our blank Import Template here (this is the file you will add your import data into):

Format the Import Template File

This step will take you setting up your import file with the columns that you want to import data for, ready for populating it with data!

You will remove the columns you don’t need, and you can add additional columns if you want to import any custom field data.

Open the ‘BLANK Transaction Import Template’ file (download available above), this will open an Excel file which will only contain column headings, these will be listed in the first row
This is the file that you will be working from, amending, and adding data to
Open the ‘EXAMPLE Transaction Import Template’ file (download available above), this will open an Excel file with further information and example data for each available import column
This file is a useful reference, to understand what data is required in each column
Make sure that you have the following mandatory columns included within your 'BLANK Transaction Import Template’ file
1. Date
2. TransactionMethodID
3. InvoiceID
4. Amount
5. Tax
6. ExternalID
Progress to the next step, to fill your import file in with your Transaction data

Populate the Transaction Import Template File

This step will take you populating your import file with your Transaction data, ready for importing it into your platform!

Using the 'EXAMPLE Transaction Import Template’ file as a reference, add in your Transaction data into your 'BLANK Transaction Import Template’ file
The below table outlines each column that you can include in your import file:

Column	Description	Format	Rules
Date Mandatory	This is the Date and time of the Transaction	Date	This must use the same Date & Time format as your Platform
TransactionMethodID Mandatory	This is the method used for the Transaction. This lets the Platform know how the Transaction was paid	Number	This needs to be in a number format as follows: 2 = BACS 3 = Card 9 = Cheque 10 = Cash 17 = Voucher
InvoiceID Mandatory	This is the Invoice ID, NOT the Invoice Reference This can be found by going to Finance > Invoices and adding in the ‘ID’ column to your DataGrid. Please see our dedicated page for adding columns to your DataGrid	Number	This is MUST match an Invoice ID already within your Platform. Make sure the Invoices are committed before importing the Transactions
Amount Mandatory	This is the total Amount of the Transaction (including tax)	Number	Do not put a currency symbol in front of your Amount as the Platform will automatically assign the currency of the relevant Training Provider when it imports the transactions
Tax Mandatory	This is the Tax paid for the Transaction	Number	Do not put a currency symbol in front of your tax amount as the Platform will automatically assign the currency of the relevant Training Provider when it imports the transactions
ExternalID Mandatory	This is the ID for an External Payment system you may use. This helps with identifying the payments between accessplanit and your external system	Text	We recommend using a Prefix for your External ID to help differentiate payments from refunds originally recorded within your accessplanit Platform, e.g. ‘Sage…’ , ‘Xero…’, ‘Quickbooks…’

If your organisation/business operates using Languages where the following special characters are present - Č, Š, Ž and Ć - and you wish to use the importer to upload content containing these characters to the platform - you will need to contact the accessplanit Support Team.

Save as a .csv file

This step will take you through saving your Transactions Import file into the file format needed for the import tool

Once you have all the information you require within your import file, you can then save it as a .csv file
Within Excel, click the ‘File’ option in the top left hand corner
Click ‘Save as’
Using the drop down box under the file name, select CSV (comma delimited) (*.csv) and name your file (for example ‘New Transactions Import’)
Click the ‘Save' button to complete the Save

Your Import file has now been saved, ready for importing.

Please Note
If your organisation/business operates using other Languages, when saving a .csv file, ensure that the document is saved with ‘,' rather than ';’ as this can prevent the document from Importing

Create an Import Profile

This step will run through how to create an Import Profile in your accessplanit platform, to use when importing Transactions.

You will be able to reuse this profile each time you import Transactions using the same file structure (i.e. the same columns of data are included).

Open the ‘Administration’ menu from the Profile options at the top-right of your platform
Scroll down the page until you find the ‘Import Profiles’ menu option
The menu is in alphabetical order
Click on the ‘Import Profiles’ option to be taken to the Import Profiles page
Click the ‘New Profile’ button at the top right corner of the page to create a new Import Profile
Provide a ‘Profile name’ for your Import Profile
We advise providing your Profile with a unique name so that's its easily identifiable
Select ‘Transaction’ as your import ‘Destination’, this lets the Platform know that you will be importing Transaction information
The next step is to upload the .csv file that you created and saved earlier, click the selecting the ‘Choose file’ button and selecting the file from your saved location
Leave the ‘Header Label Row’ set to '1', this tells your platform that the column headings of your import are in the first row
Click the ‘Next ->’ option at the top right of your page to proceed to the column mapping step for your Import Profile
In the column mappings step you will match each column in your import file (Source column) to a Transaction setting in your accessplanit platform (Destination column), this is where you define where the information in your import file will be imported to and displayed in your platform, much of this will be pre-populated for you!
If you see ‘IGNORE' within the 'Destination Column’, you will need to select the correct column from the drop down option
Leaving it as IGNORE will mean that the Platform will ignore this column in the file when running the Import
When running an Import, your platform needs to know which column(s) can be used to uniquely identify each row, this is important as the importer supports both creating Transactions and updating Transactions, if each row is not uniquely identified by its key columns there is a risk that the existing Opportunities will be overwritten with the details of new Transactions. Use the ‘Key column' field to select 'Yes’ for your unique identifiers.

Key Columns for Transactions

Key Column(s) are used to identify Transactions, so that your platform to differentiate between one and another.

For example, if you have two Transactions with the same Amount, your platform needs to know the difference between the two, in this case you could set your key columns to InvoiceID and ExternalID, because this combination will be different on the two Transactions.

Click the ‘Finish’ button at the top right of your page to complete your Import Profile
Click the ‘OK’ button to return to the ‘Import Profiles’ page

Your Import Profile has now been created

Please note

The columns within your Import Profile MUST match the columns within your .csv file exactly otherwise your Import will not work.

Run a sample Import Job

Before you run your full import into the platform, we recommend that you first run a test to check that your Import Profile has been set up correctly.

This involves only importing a small sample of Transactions and checking that they have imported correctly.

To run a test Import Job, you will follow these steps:

Create a sample import file
Run an Import Job
Check the Transactions

Create a sample import file

This step will show you how to import your sample Transactions using your Import Profile

Open the .csv file that you created for your import earlier and remove data so that you only have 3-4 lines of data remaining in the file
Go to ‘File’ in the top left hand corner
Click ‘Save As’ to save this as a new import file
Do not click ‘Save’ as this will overwrite your import file
Rename this version of your import file so that you know if it is the test import file, for example 'New Transaction Import - Test File', and make sure that the format is still CSV (Comma Delimited) (*.csv)
Click the ‘Save’ button to create your Test import file

You are now ready to run your test import

Please Note
If your organisation/business operates using other Languages, when saving a .csv file, ensure that the document is saved with ‘,' rather than ';’ as this can prevent the document from Importing

Run an Import Job

This step will show you how to import your sample Transaction using your Import Profile

Click ‘Profile’ and then ‘Administration’ in the top right corner of the Platform
Scroll down the menu and click ‘Import Jobs’, the menu is in alphabetical order
Click on the ‘Import Jobs’ option to be taken to the Import Jobs page
Click ‘New job’ in the top right hand corner
Select your Import Profile from the ‘Profile’ drop down menu. It will be prefixed with ‘Transaction’
From the File option, click on the ‘Choose File’ button and upload the Test file that you created in the previous step
The ‘Initial State’ option is where you can choose to Import now, or put the Import on Hold if you would like to set the import off to run later
Click the ‘Upload’ button to start the Import Job
Once you have uploaded your file to start the Import Job, your page will show the Import job being processed
a. ID - This is the ID number of the Import Job
b. Profile - This is the name of the Profile used for the Import
c. Filename - This is the name of the Import file uploaded
d. Status - This is the status of the Import Job, it will begin as ‘Queued’ but will change to ‘Completed’ once the Import has finished
e. Due - This is when the Import is due to run
f. Started - This will be blank to begin with, once the Import Job has started, the Start date and time will populate.
g. Completed - This will be blank and will populate once the Import has completed
h. Row Count - This shows how many rows are to be imported
i. Processed - This will show the number of rows that have been imported and will change as the Import runs.
Your Import will have the status of ‘Completed’, the Completed time and date will be populated and the Processed' column number will match the same number as the ‘Row Count' column
Once your import is complete, click the ‘Show Errors' button to see if there were any issues with the import

Move on to the next step to check whether your Import Profile has worked or not.

Check the Transactions

This step will talk you through the process of checking your new Transactions have imported correctly

Navigate to ‘Finance’ and then 'Invoices’ from the Main Navigation Menu
This will open the Invoices DataGrid
Using the search bar, search for each of your Invoice ID’s that you have just imported
One Invoice at a time, right-click on each Invoice and select ‘Transactions’ from the context menu options
Check that the details of the Transaction have been imported as expected, for example:
1. The Amount is correct
2. It is for the correct Invoice
3. The External ID is correct
4. + any other information you have imported
  You can do this by adding columns into the DataGrid. Please see our dedicated page for adding new Columns to your DataGrid
  
  If you are satisfied that all the sample Opportunities have been imported correctly, your testing is now complete

Run your Import

Once you have run your test import and you are happy that everything imported correctly, you are ready to import your Transactions!

Go back to the original .csv file that you created earlier within Excel and open the file with all your data showing and check you are happy with the file
Within your accessplanit platform, open the ‘Administration’ menu from the Profile options at the top-right of your platform
Scroll down the menu and click ‘Import Jobs’, the menu is in alphabetical order
Click on the ‘Import Jobs’ option to be taken to the Import Jobs page
Click ‘New job’ in the top right hand corner

Select your Profile from the drop down menu. This is the name of the Profile created in the step above. In this case it is ‘Transaction - New Transactions’
Select the ‘File’ you wish to upload, this is the original import file you created and saved earlier
The ‘Initial State’ option is where you can choose to Import now, or put the Import on Hold if needed
Click the ‘Upload’ button to start your Import Jobs
Once you have uploaded your Import Job, it will show the Import Job uploading
a. ID - This is the ID number of the Import Job
b. Profile - This is the name of the Profile used for the Import
c. Filename - This is the name of the Import file uploaded
d. Status - This is the status of the Import Job, it will begin as ‘Queued’ but will change to ‘Completed’ once the Import has finished
e. Due - This is when the Import is due to run
f. Started - This will be blank to begin with, once the Import Job has started, the Start date and time will populate.
g. Completed - This will be blank and will populate once the Import has completed
h. Row Count - This shows how many rows are to be imported
i. Processed - This will show the number of rows that have been imported and will change as the Import runs.
Your Import will have the status of ‘Completed’, the Completed time and date will be populated and the Processed' column number will match the same number as the ‘Row Count' column

Screenshot 2024-01-11 092913-20240111-093001.jpg

There is a section at the end of the Import named ‘Actions’. Here you will find the option to ‘Show Errors’, ‘Restart’, ‘Stop’ and ‘Cancel’ your Import
You can use the ‘Show Errors’ button to see if anything has stopped your Import from working correctly, if the Import Errors page is blank, this means the data in your import file hasn’t failed any validation checks

Please note

You can download the errors file to view more information about any failures from the ‘Import Errors’ page

If you do not understand what any of the errors mean, please reach out to the support team and they will be able to provide further guidance and help you with your import

Example of an Import with no errors:

Example of an Import with Errors:

The Import Errors page shows you which rows have failed

The ‘Stop’ and ‘Restart' buttons can be used while your Import Job is processing, to pause and restart your import, please note that it can take a few seconds for these to be actioned after you have clicked them
We do not recommend using the 'Delete’ button for your Import Jobs unless absolutely necessary