Skip to main content

Transaction Entry Data Import Preparation

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

  1. Verify that the total Debit (Dr) and Credit (Cr) values match in the provided data.
  2. Check if there are any issues such as:
    • Narration or description breaks.
    • Vouchers containing multiple dates.
    • Any missing or duplicate transactions.
  3. 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
  • SUMIF
  • UPPER
  • TRIM

Convert Narration and Description to Uppercase using Excel:

=UPPER(TRIM(CellNum))

Remove Unnecessary Characters Before Semicolons

Use the Power Query Tool:

  • Go 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 for Import

Once the data is cleaned and verified, structure it as per Aegis Software's format. Ensure that the following columns are present and named exactly as below:

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 (Dedicated for data import)
transaction_date Old system’s voucher date (Format: YYYY-MM-DD)
ref_number Old system’s voucher number (Ensure a unique prefix for proper separation)
description Ledger-wise entry description (Optional if needed)
cost_center_code Cost center for P/L visibility (Mandatory for expense/income ledgers)
narration Whole voucher description (Aggregated)
dr_amount Debit value (without commas)
cr_amount Credit value (without commas)

Example:

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 0.00

Data Saving Format

  • After preparing the data, save the file as "CSV (Comma Delimited)" and store it in a secure location.

3. Importing Data into Aegis Software

After verifying the prepared file, proceed with the data import process. Always test in the backup database before importing into the live system.

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 of the data upload.

B) Error Handling

  • If errors occur during import, check the error message and resolve the issue 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 making modifications.
  • In case of discrepancies, compare the final imported data with the raw data to ensure accuracy.
Back to top