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 in the provided data.match.
    • Check if there are anyfor 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:

=UPPER(TRIM(CellNum))

  • Remove Unnecessaryunnecessary Characterscharacters Beforebefore Semicolons

    • semicolons.

    Use the Power Query Tool:

    • GoNavigate 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 Andand Validations for Import

    • OnceAegis 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 dataClient’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 cleanedYYYY/MM/DD and(use verified,slashes, structurenot itdashes).

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

    • One Transaction Date per Aegis Software's format.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 columnsexact areheader presentnames and(order namedcan exactlyvary asbut below:

      names must match):

    Required Columns & Descriptions

    Column Name Description
    account_code Aegis Chart of Account Code
    sub_ledger_code Aegis Subledger Code (e.g., payablePayable/Receivable profile/receivable profile/Subledger Code)Profile)
    voucher_code Aegis Import Voucher Code Dedicated for data import, (Only use one voucher.per import)
    transaction_date Voucher date Should BeDate (Format: YYYY/MM/DD)* Format, Use "/" Slash between Year Month & Day.
    ref_number Old system’s voucher number (Ensure a uniqueUnique prefix for each old system voucher for proper separation)required)
    description Ledger-wise entry description (Optional if needed)Optional)
    cost_center_code Cost center for departmental P/L (Mandatory for expense/income ledgers)income)
    narration Whole voucher description (Aggregated),Mandatory; narration is mandatory so, adduse "..." if narration is blankblank)
    dr_amount Debit value (without commas)
    cr_amount Credit value (without commas)

    Example: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-2024/01/23 I101001
    		OJV SV-01 SALES BOOKED FRD BEING SALES BOOKED FOR 240123 0.00 4424.78
    2024-01-2024/01/23 A101002
    		OJV SV-01 SALES BOOKED FRD BEING SALES BOOKED FOR 240123 0.00 575.23
    2024-01-2024/01/23 A101003
    		OJV SV-01 SALES BOOKED FRD BEING SALES BOOKED FOR 240123 50005000.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,data:

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

      3. Importing Data into Aegis Software

      After verifyingOnce the prepareddata file,is cleaned and formatted, 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.confirmation.

      B) Error Handling

      • If errors occur during import,occur, check the error message and resolve the issueissues 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