CSV Migration Templates

This document provides CSV templates and formatting guidelines for migrating funds into Asset360 v3.

Overview

The fund migration process uses CSV files to import bulk data. Each data type has its own CSV format with specific required and optional columns.

Multi-Step Upload Flow

1. Fund Metadata (via UI form, not CSV)
2. Bank Accounts (bank_accounts.csv)
3. Investors (investors.csv)
4. Holdings (holdings.csv)
5. Journal Entries (journal_entry_headers.csv + journal_entry_lines.csv)

General CSV Requirements

• Encoding: UTF-8
• Header Row: Must include exact column names (case-sensitive)
• Empty Rows: Automatically skipped
• Dates: ISO 8601 format (YYYY-MM-DDTHH:mm:ss.sssZ)
• Money: Decimal numbers (e.g., 1234.56)
• Booleans: "true"/"false" or "1"/"0"

1. Bank Accounts (bank_accounts.csv)

Import bank accounts with their current balances.

Required Columns

Column	Type	Description	Example
sourceId	string	Unique ID from legacy system	BA001
accountNumber	string	Bank account number	1234567890
bankName	string	Name of the bank	Standard Chartered
accountName	string	Name on the account	Growth Fund - Operations
currentBalance	number	Current balance	1500000.50

Optional Columns

Column	Type	Default	Description	Example
routingNumber	string	""	Bank routing number	123456789
accountType	enum	"current"	Account type: current, savings, cash	current
currency	string	"BDT"	3-letter currency code	BDT
interestRate	number	0	Interest rate as percentage	5.25
minimumBalance	number	0	Minimum balance requirement	10000.00
closedAt	string	-	ISO 8601 date if account is closed	2024-12-31T00:00:00.000Z
createdAt	string	now	ISO 8601 date when created	2024-01-01T00:00:00.000Z
updatedAt	string	now	ISO 8601 date when updated	2024-01-15T00:00:00.000Z

Example Template

sourceId,accountNumber,bankName,routingNumber,accountName,accountType,currency,interestRate,minimumBalance,currentBalance,createdAt,updatedAt
BA001,1234567890,Standard Chartered,123456789,Growth Fund - Operations,current,BDT,5.25,10000.00,1500000.50,2024-01-01T00:00:00.000Z,2024-01-15T00:00:00.000Z
BA002,9876543210,HSBC,987654321,Growth Fund - Investment,savings,BDT,6.50,50000.00,3200000.00,2024-01-01T00:00:00.000Z,2024-01-15T00:00:00.000Z

2. Investors (investors.csv)

Import investor personal information.

Required Columns

Column	Type	Description	Example
sourceId	string	Unique ID from legacy system	INV001
investorType	enum	individual or institutional	individual
fullName	string	Full name of investor	Ahmed Khan
email	string	Primary email address	[email protected]
mobilePhone	string	Primary mobile phone	+8801712345678

Optional Columns

Column	Type	Description	Example
taxId	string	Tax identification number	123-45-6789
registrationNumber	string	Company registration (institutional)	C-12345
boId	string	Beneficiary Owner ID	BO123456
emailOptional	string	Secondary email address	[email protected]
mobileOptional	string	Secondary mobile phone	+8801898765432
address	string	Physical address	123 Main St, Dhaka, Bangladesh
createdAt	string	ISO 8601 date when created	2024-01-01T00:00:00.000Z
updatedAt	string	ISO 8601 date when updated	2024-01-15T00:00:00.000Z

Example Template

sourceId,investorType,fullName,email,mobilePhone,taxId,boId,address,createdAt,updatedAt
INV001,individual,Ahmed Khan,[email protected],+8801712345678,123-45-6789,BO123456,"123 Main St, Dhaka, Bangladesh",2024-01-01T00:00:00.000Z,2024-01-15T00:00:00.000Z
INV002,institutional,ABC Corporation,[email protected],+8801898765432,,C-12345,"456 Business Ave, Dhaka, Bangladesh",2024-01-01T00:00:00.000Z,2024-01-15T00:00:00.000Z

3. Holdings (holdings.csv)

Import investor holdings (units and average cost).

Required Columns

Column	Type	Description	Example
sourceId	string	Unique ID from legacy system	HOLD001
investorSourceId	string	References investor sourceId	INV001
fundId	string	Fund ID (must match migration fund)	fund-abc-123
totalUnits	number	Total units held	1000.5

Optional Columns

Column	Type	Description	Example
averageCost	number	Average cost per unit	10.50
lastTransactionDate	string	ISO 8601 date of last transaction	2024-01-10T00:00:00.000Z
createdAt	string	ISO 8601 date when created	2024-01-01T00:00:00.000Z
updatedAt	string	ISO 8601 date when updated	2024-01-15T00:00:00.000Z

Example Template

sourceId,investorSourceId,fundId,totalUnits,averageCost,lastTransactionDate,createdAt,updatedAt
HOLD001,INV001,fund-abc-123,1000.5,10.50,2024-01-10T00:00:00.000Z,2024-01-01T00:00:00.000Z,2024-01-15T00:00:00.000Z
HOLD002,INV002,fund-abc-123,2500.0,10.25,2024-01-12T00:00:00.000Z,2024-01-01T00:00:00.000Z,2024-01-15T00:00:00.000Z

Important Notes:

• investorSourceId must reference a valid investor from investors.csv
• fundId must match the fund being migrated
• totalUnits must be non-negative
• averageCost can be null/empty if unknown

4. Journal Entries

Journal entries require two CSV files:

• journal_entry_headers.csv - Entry metadata
• journal_entry_lines.csv - Entry lines (debits and credits)

4a. Journal Entry Headers (journal_entry_headers.csv)

Required Columns

Column	Type	Description	Example
sourceId	string	Unique ID from legacy system	JE001
fundId	string	Fund ID (must match migration fund)	fund-abc-123
description	string	Entry description	Opening balances for cash and equity
createdBy	string	User who created the entry	[email protected]
date	string	ISO 8601 date of the entry	2024-01-01T00:00:00.000Z

Optional Columns

Column	Type	Default	Description	Example
reference	string	-	External reference number	OB-2024-001
entryType	enum	"primitive"	primitive or computed	primitive
status	enum	"posted"	draft, posted, or reversed	posted
postedAt	string	now	ISO 8601 date when posted	2024-01-01T00:00:00.000Z
createdAt	string	now	ISO 8601 date when created	2024-01-01T00:00:00.000Z
updatedAt	string	now	ISO 8601 date when updated	2024-01-01T00:00:00.000Z

Example Template

sourceId,fundId,description,reference,entryType,status,createdBy,date,postedAt,createdAt,updatedAt
JE001,fund-abc-123,Opening balances for cash and equity,OB-2024-001,primitive,posted,[email protected],2024-01-01T00:00:00.000Z,2024-01-01T00:00:00.000Z,2024-01-01T00:00:00.000Z,2024-01-01T00:00:00.000Z
JE002,fund-abc-123,Initial bank deposits,DEP-2024-001,primitive,posted,[email protected],2024-01-05T00:00:00.000Z,2024-01-05T00:00:00.000Z,2024-01-05T00:00:00.000Z,2024-01-05T00:00:00.000Z

4b. Journal Entry Lines (journal_entry_lines.csv)

Required Columns

Column	Type	Description	Example
journalEntrySourceId	string	References journal entry sourceId	JE001
sourceAccountCode	string	Account code from chart of accounts	1010
amount	number	Amount (positive for debit, negative for credit)	50000.00
description	string	Line description	Opening cash balance

Example Template

journalEntrySourceId,sourceAccountCode,amount,description
JE001,1010,50000.00,Opening cash balance
JE001,3000,-50000.00,Opening equity balance
JE002,1010,25000.00,Bank deposit
JE002,1020,-25000.00,Bank liability

Important Notes:

• Each journal entry must have at least 2 lines (debit and credit)
• journalEntrySourceId must reference a valid entry from journal_entry_headers.csv
• Total debits must equal total credits (sum of all amounts must be 0)
• Positive amounts = debit, negative amounts = credit
• sourceAccountCode must exist in the fund's chart of accounts

Complete Journal Entry Example

Headers:

sourceId,fundId,description,reference,entryType,status,createdBy,date
JE001,fund-abc-123,Opening balances,OB-2024-001,primitive,posted,[email protected],2024-01-01T00:00:00.000Z

Lines:

journalEntrySourceId,sourceAccountCode,amount,description
JE001,1010,100000.00,Cash - Opening balance
JE001,1500,50000.00,Investments - Opening balance
JE001,3000,-150000.00,Equity - Opening balance

Result: Balanced entry (100000 + 50000 - 150000 = 0) ✅

Validation Rules

The system validates all CSV files before processing:

1. CSV Format: Correct headers, valid UTF-8 encoding
2. Required Columns: All required columns must be present
3. Data Types: Correct types (strings, numbers, dates)
4. Referential Integrity:
   • Holdings reference valid investors
   • Journal entry lines reference valid entries
   • Account codes exist in chart of accounts
5. Business Rules:
   • Journal entries must balance (total = 0)
   • Units and balances must be non-negative
   • Email addresses must be valid
   • Dates must be valid ISO 8601

Error Reporting

If validation fails, the system returns detailed errors:

{
  "valid": false,
  "errors": [
    {
      "file": "holdings",
      "errors": [
        {
          "row": 5,
          "field": "investorSourceId",
          "message": "Investor source ID is required"
        },
        {
          "row": 8,
          "field": "totalUnits",
          "message": "Total units cannot be negative"
        }
      ]
    },
    {
      "file": "journalEntries",
      "errors": [
        {
          "row": 3,
          "message": "Journal entry JE003 does not balance. Total: 100.50. All journal entries must have equal debits and credits."
        }
      ]
    }
  ]
}

Best Practices

1. Test with Small Files First: Validate a few rows before uploading thousands
2. Use Excel/Google Sheets: Create templates, then export as CSV
3. Check Encoding: Always save as UTF-8 (not Windows-1252 or other encodings)
4. Validate Dates: Use ISO 8601 format consistently
5. Balance Entries: Verify all journal entries balance before upload
6. Unique IDs: Ensure sourceId values are unique within each file
7. Reference Integrity: Check that all referenced IDs exist (investors, accounts)
8. Back Up Legacy Data: Keep original data until migration is verified

Download Blank Templates

[Coming Soon: Links to download blank CSV templates]

Support

For questions or issues with CSV migrations, please contact:

• Technical Support: [email protected]
• Documentation: https://docs.asset360.com