Transaction Entry Data Import Preparation

Aegis Software - Data Import Workflow

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.