General Journal Entries

Top  Previous  Next

 

Import Journal Entry from a Spreadsheet

Journal entries can be created in a spreadsheet and imported into the AB program using the Accuimport utility.

 

AccuImport Spreadsheet Tips

You may have multiple journal entries in the spreadsheet and each group of entries may have different transaction dates.  Be sure that an individual group of entries has the same transaction date and the same Group Number.  The transactions will only post to open accounting periods.

Jobs, phases, and ledger accounts must be set up in their respective master lists before using them in the spreadsheet.  

Note:  These fields are not ‘list’ fields in the spreadsheet; they are simply fields that you type in a value.  The import function does NOT check whether these values are valid but the entries will be placed in the user’s batch.  However, if the account, job or phase number doesn’t exist in the AB database, then the imported batch will fail to post.

 

AccuImport Setup

To use the AccuImport utility, each user will need to set up Program Links.  

Note:   The Windows Explorer references in this guide describe the AB Cloud user’s experience.  If your AB program is installed on your server or another Cloud system, then your directory tree may look different.

 

SY_ABUtilities_AccuImport_Accounts Payable Invoices_1

 

Create a Program Link

Log in to the AB program with your normal username and password.  On the main program screen, you will find the Program Links option under the Shortcuts section of the screen.  The Program Links option is used to set up easy access links to external programs or locations outside of the AB program.  

 

Program Link - Accuimport

Right-click on the Program Links option and select Add Program Link.  

Browse to the location where the AB program has been installed such as F:\AccuBld9 folder (your folder name may be AccuBld8 depending on how long you have had the AB system).  

If you are on the AB Cloud, keep in mind that your local server may also also have an F Drive.  On the AB Cloud, your drive will be noted with your company name (MasterTest (F:) in this example)

Scroll down through the AccuBld9 folder until you find the Accuimport option.  Highlight this option and then click OK.  This will add the Accuimport option as a shortcut under Program Links.

SY_ABUtilities_AccuImport_Accounts Payable Invoices_2

Program Link - User Folder

Right-click on the Program Links option and select Add Program Link.  

Browse to the file path where your data is located (ie. F:/Accubld9/CompanyName). Within this folder is your personal user folder; this is typically the name that you use when you log in to the AB program.   In this example, I am logged in as ABADMIN.

Highlight your user folder and then click OK.  This will add a shortcut to your user folder under the Program Links.  This is where you will store the Excel spreadsheet that you will use for entering and importing your journal entry into the AB program.

 

SY_ABUtilities_AccuImport_Accounts Payable Invoices_3

AccuImport Spreadsheet

 

The spreadsheet that you will use for your journal entry will need to be copied into your user directory.

Click on the Program Links > YOURUSERFOLDER shortcut to open the Windows Explorer on the Cloud.

Browse up to the AccuBld9 folder and then browse to the Samples folder.

Locate the GLBatchSpreadsheetSample file

 

SY_ABUtilities_AccuImport_Accounts Payable Invoices_6

Paste the file into your user directory (ie. F:\Accubld9\CompanyName\UserName).

Close the Windows Explorer.

Open your user folder once more from the Program Links shortcut.  Be sure you can see the GLBatchSpreadsheetSample.xls in this location.  Double-click on the file to open the file with Excel.

 

Using the Spreadsheet

The first time you open the spreadsheet you will notice that there is sample data within the spreadsheet which will serve as a guide as to how you should enter your data.

Do not reformat any of the cells.  The cell formatting has already been made for you. The formatting needs to be very simple for the import process.

The top row of the spreadsheet contains the field names which will be used for the import process. DO NOT CHANGE THE VALUES IN ROW 1.

Make all your entries for your spreadsheet and be sure that the net total of the amount field equals zero.

Close your spreadsheet before attempting to import the spreadsheet.   Failure to close the spreadsheet before importing will cause an error during the import process.

 

Before Importing a Spreadsheet

The import process copies the journal in the spreadsheet into your Ledger > General Journal screen.  This General Journal screen is unique to you and cannot be viewed by any other user.  We recommend that you start with an empty batch when importing invoices.  Before using the import process, go to the Ledger > General Journal screen to verify that there is no data in this screen.  If you have data in this screen, it means that you did not post your previous entries.  

 

Importing a Spreadsheet

To import the journal entry, go to Program Links and click on the Accuimport option.

1.Select Company and User - You may have more than one company within AB so be sure you choose the proper company name on this screen.  The user names are NOT listed alphabetically but you may type in your user name and the system should find it on the list.

2.Select Excel File - Use the browse button on the right to find your spreadsheet in your user folder (ie. F:\Accubld9\CompanyName\UserFolder).

3.Select Target Table:  Choose the GL General Journal option on the list.

4.Map Fields - The first time that you use the spreadsheet you may need to map the columns to the fields within AB.  The first row of data are the fields names; if they appear in RED text then the field needs to be mapped; if the text is GREEN, then the mapping has already been completed.   The fields and column names are very similar.  Simply click the drop down list to choose the appropriate field name.

 

Column Name

Mapped Field Name

Journal Entry Desc

TRANS_DESC

Transaction Date

TRANS_DATE

Amount

AMOUNT

Ledger Account

GLACCOUNT

Group Number

GL_REF_NO (All entries for one journal entry should have the same group number)

Job Number

JOB

Phase Code

PHASE

Units

UNITS (Choose ‘Discard’ if you don’t use Units)

 

SY_ABUtilities_AccuImport_Accounts Payable Invoices_7

5.Export to User Batch - Click the Export to Batch button to copy your spreadsheet data into your user batch.    Close the Accuimport screen once completed.

6.Once the entries have been imported, go to Ledger > General Journal to view the entries.  You may print your list of entries by using the Print button at the top of the screen.  Printing and reviewing your entries is a ‘best practice’ in order to catch any mistakes before they are posted.  If you do need to make corrections, you can simply edit your entry on the General Journal screen.  Or, you can delete all your entries from this screen, make your corrections in the spreadsheet and import the data once again.

7.Use the Post button when you are ready to commit your entries.  Depending on the number of records in your batch, this process may take a few minutes.  A pop up screen will let you know when the process is complete.  If you get a notice that the process did NOT complete, please contact AB for assistance.  When the batch is posted, the journal records will be removed from your user batch.  The journal entry screen should be empty after each successful batch posting.