Skip to main content

Transaction Entry Data Import Preparation

Voucher Import User Process - Aegis Software

1. Data Verification & Cleaning

Before preparing the data for import, verify and clean the data as per Aegis Software's requirements.

A) Verify Data Completeness

Ensure the provided data includes at least the following fields:

  • Voucher Number
  • Date
  • Ledger
  • Subledger
  • Description
  • Narration
  • Debit (Dr)
  • Credit (Cr)
  • Cost Center

B) Check for Data Consistency

  • Verify that the total Debit (Dr) and Credit (Cr) values match.
  • Check for issues such as:
    • Narration or description breaks.
    • Vouchers containing multiple dates.
    • Any missing or duplicate transactions.
  • Ensure that each voucher’s Dr and Cr values are equal.

C) Data Cleaning & Preparation Steps

For data preparation & verification, you can use the following Excel formulas:

  • VLOOKUP : To cross-reference data.
  • SUMIF : To sum data based on conditions for verification (e.g., voucher, ledger)
  • UPPER : To convert text to uppercase for consistency.
  • TRIM : To remove unnecessary spaces.

Convert Narration and Description to Uppercase using Excel:

  • Remove unnecessary characters before semicolons.

Use the Power Query Tool:

  • Navigate to Data Tab > From Table/Range and clean the data accordingly.
  • Ensure the cleaned data aligns with Aegis import requirements. The final data should match the raw data provided by the client in terms of Dr and Cr values.

2. Data Formatting and Validations for Import

  • Aegis system records all vouchers in its own format; create a separate voucher only for import (e.g., IMPORT JOURNAL VOUCHER (OJV)).

  • Client’s vouchers are recorded as Ref Voucher.

  • Include the Client’s voucher name after the narration and description for better tracking. (good practice for finding vouchers in ledger reports, etc.).

  • Import should be only one fiscal year per file. If multiple fiscal years are needed, create separate files.

  • Date Format: Ensure transaction date format is YYYY/MM/DD (use slashes, not dashes).

    • Correct Format: YYYY/MM/DD (e.g., 2024/01/23)
    • Incorrect Format: YYYY-MM-DD (e.g., 2024-01-23)

  • One Transaction Date per Voucher: Ensure that each voucher contains only one transaction date to prevent import errors.

IMPORTANT:

Once you upload data, you cannot undo this process. However, you can update it by uploading a corrected new file to the system. Best practice is to first test in your local system, then after verification, upload to the live client database.

  • File format should be CSV with the following exact header names (order can vary but names must match):

Required Columns & Descriptions

Column Name Description
account_code Aegis Chart of Account Code
sub_ledger_code Aegis Subledger Code (e.g., Payable/Receivable Profile)
voucher_code Aegis Import Voucher Code (Only one per import)
transaction_date Voucher Date (Format: YYYY/MM/DD)
ref_number Old system’s voucher number (Unique prefix required)
description Ledger-wise entry description (Optional)
cost_center_code Cost center for departmental P/L (Mandatory for expense/income)
narration Whole voucher description (Mandatory; use "..." if blank)
dr_amount Debit value (without commas)
cr_amount Credit value (without commas)

Example CSV Format:

Table Format:

transaction_date account_code sub_ledger_code voucher_code ref_number description cost_center_code narration dr_amount cr_amount
2024/01/23 I101001
OJV SV-01 SALES BOOKED FRD BEING SALES BOOKED FOR 240123 0.00 4424.78
2024/01/23 A101002
OJV SV-01 SALES BOOKED FRD BEING SALES BOOKED FOR 240123 0.00 575.23
2024/01/23 A101003
OJV SV-01 SALES BOOKED FRD BEING SALES BOOKED FOR 240123 5000.00 0.00

CSV Format:

transaction_date,account_code,sub_ledger_code,voucher_code,ref_number,description,cost_center_code,narration,dr_amount,cr_amount
2024/01/23,I101001,,OJV,SV-01,SALES BOOKED,FRD,BEING SALES BOOKED FOR 240123,0.00,4424.78
2024/01/23,A101002,,OJV,SV-01,SALES BOOKED,FRD,BEING SALES BOOKED FOR 240123,0.00,575.23
2024/01/23,A101003,,OJV,SV-01,SALES BOOKED,FRD,BEING SALES BOOKED FOR 240123,5000.00,0.00

Data Saving Format

After preparing the data:

  • Save the file as CSV (Comma Delimited).
  • Store it in a secure location.

3. Importing Data into Aegis Software

Once the data is cleaned and formatted, proceed with the import process.

A) Importing the File

  1. Navigate to Account > Transaction Entry Import.
  2. Select the prepared CSV file.
  3. Click on Save and wait for confirmation.

B) Error Handling

  • If errors occur, check the error message and resolve issues accordingly.
  • Re-verify data formatting and alignment before reattempting the upload.

Final Notes

  • Follow each step precisely to ensure a smooth data migration process.
  • Always maintain a backup of the original data before modifications.
  • In case of discrepancies, compare the final imported data with the raw data to ensure accuracy.
Back to top