Migrating legacy data can be a challenging and often costly undertaking. Simple Accounting 1.8 offers an automatic, easy-to-use, and free utility to assist with legacy data migration, providing comprehensive help and guidance throughout the process.
Simple Accounting 1.8 automates data migration, which is divided into two main parts: current financial year migration and previous financial years migration. The system includes utilities to support both.
f GL account codes differ from the Simple Accounting System, use the Legacy GL Code Migration Preparation Utility to validate and migrate them via the 'GL Accounts' option in the Data Migration Utility's 'Legacy Data Migration Current FY' button.
Important Instructions
It is recommended to prepare your legacy data by cleaning it and ensuring it adheres to the following format:
#
Name
Type
Null
Default
Mandatory
1
date
date
No
None
Required
2
description
varchar(255)
No
None
Required
3
amount
decimal(10,2)
No
None
Required
4
tax_amount
decimal(10,2)
Yes
0
Optional
5
gl_account_code_debit
varchar(10)
Yes
NULL
Required
6
gl_account_code_credit
varchar(10)
Yes
NULL
Required
7
created_at
timestamp
No
current_timestamp()
Required
8
reconciled
tinyint(1)
Yes
0
Optional
9
customer_id
int(11)
Yes
NULL
Optional
10
vendor_id
int(11)
Yes
NULL
Optional
11
category_id
int(11)
Yes
NULL
Optional
12
reference_no
varchar(100)
Yes
NULL
Optional
13
status
varchar(20)
Yes
approved
Optional
14
notes
text
Yes
Optional
15
created_by
int(11)
Yes
NULL
Optional
If adhering to this format is not feasible, and your legacy data consists of paired records, utilize the Blue Button to convert them to a single record format. There are two migration processes, each accessible through a dedicated button, namely "Legacy Data Migration: Current FY" and "Legacy Data Migration: Previous FYs. These initiate the migration process for records already in the required format.
Migration Process Tasks (Adhering to Accounting Principles)
For Current Legacy Data
Data Migration: Migrate the cleaned and formatted data, including all transactions from the current financial year, ensuring accurate recording in the appropriate accounts
Data Validation:
Thoroughly validate the migrated data to ensure accuracy and completeness.
Compare the data in the source and target systems.
Reconcile the trial balance of the old and the new systems.
Testing:
Perform thorough testing of the new system with the migrated data. Test key accounting functions.
Data Migration Tool User Guide
Data Migration Tool User Guide
Overview
The Data Migration Tool is a comprehensive solution for importing and managing financial data, with special emphasis on GL (General Ledger) account migration. This tool allows you to seamlessly transfer data from CSV files into your database while providing powerful mapping capabilities to ensure data integrity.
Main Features
Multi-Table Support: The tool supports migration for various financial tables:
GL Accounts
Bank Transactions
Customers
Vendors
Transactions
Data Backup: Before performing any migration, you can create backups of your existing data:
One-click backup functionality for any table
Downloads a complete SQL dump file with table structure and data
Timestamped filenames for easy identification
CSV Import with Field Mapping: The tool provides a flexible CSV import process:
Upload any CSV file containing your financial data
Intuitive field mapping interface to match CSV columns to database fields
Automatic field matching based on column names
Preview of the first 5 rows of data to verify content
GL Account Code Validation: A specialized feature for GL account migration:
Displays all CSV fields for each GL account record for context
Clearly indicates which GL accounts already exist in the system
Supports both adding new GL accounts and updating existing ones
Data Validation: Ensures data integrity during import:
Type validation (numbers, dates, etc.)
Required field validation
Date range validation against the financial period
Detailed error reporting
Debug Mode: For troubleshooting and verification:
Detailed logging of each record processed
Shows which records were inserted, updated, or skipped
Displays any errors encountered during import
GL Account Migration Workflow
Step 1: Use option Data Migration
Click Legacy GL Code Migration Preparation Utility link
Input legacy gl code CSV and Validate gl codes
Step 2: Import Legacy Gl Code Data
Migrate them via the 'GL Accounts' option in the Data Migration Utility's 'Legacy Data Migration Current FY' button.
Navigate to the "GL Accounts" tab
Click "Upload CSV" and select your GL accounts validated CSV file
The system will analyze your file and display a preview
The system will:
Add new GL accounts that don't exist in the database
Update existing GL accounts with new information from the CSV
Skip any records with validation errors
A summary report will show the number of records processed, added, updated, and any errors
Best Practices
Always backup before importing: Use the backup feature before any major data import
Verify your CSV format: Ensure your CSV has headers and the data is properly formatted
Check financial period settings: Verify that transaction dates fall within your financial period
Use debug mode for large imports: Enable debug mode to track the progress of large imports
Review mapping carefully: Double-check your field and GL code mappings before importing
Handle duplicates appropriately: Understand that existing GL codes will be updated, not skipped
GL Account Mapping Tips
Review all fields: Look at all CSV fields for each GL code to ensure you're mapping correctly
Check for similar codes: Pay attention to similar GL codes that might represent the same account
Use descriptions: Compare descriptions between source and target GL codes to ensure semantic matching
Handle special characters: Be aware that GL codes with special characters may need special attention
Map strategically: Consider your chart of accounts structure when mapping GL codes
This migration tool streamlines the process of transferring financial data while providing the flexibility and control needed for accurate GL account mapping, ensuring your financial data remains consistent and reliable throughout the migration process.
For Legacy Data of Previous Financial Years (FYs)
Historical Data Migration: Migrate the cleaned and formatted data onto the system, ensuring transactions are allocated to the correct historical periods and accounts in accordance with Generally Accepted Accounting Principles (GAAP) or relevant accounting standards.
Financial Year Balance Calculation: Calculate the beginning and ending balances for each account within each migrated FY, ensuring the accuracy of these balances reflects the financial position at those specific points in time. This process will adhere to accrual accounting principles where applicable.
Carry Forward of Balances: Transfer the ending balances from each previous FY as the beginning balances for the subsequent FY, ultimately transferring the final balances of the last migrated previous year to the beginning balance of the Current FY. This ensures the continuity of financial reporting.