Skip to main content
All Systems Operational

Downloading GL & TB from Civica Financials

Introduction

  • This guide will help you download the General Ledger (GL) and Trial Balance (TB) reports from Civica Financials, ensuring the data meets Validis' upload requirements.
  • Reports should be exported in (.CSV), (.XLSX), or (.XLS) file format.
  • File size must not exceed 100MB.
  • Reports must already contain the required fields without manual manipulation.

 

How to Extract a General Ledger Report

Note: This process requires access to the Civica SQL database or a backup copy. Please consult your IT or database administrator if you do not have access.

 

Step 1: Obtain a Backup of the Civica SQL Database

  • Request a backup of the Civica Financials SQL database from your IT team.
  • Ensure the backup includes the relevant financial period.

 

Step 2: Restore the Database in SQL Server

  • Use Microsoft SQL Server Management Studio (SSMS) to restore the Civica database to a local or test SQL Server instance.
  • Confirm that the restored database is accessible and intact.

 

Step 3: Run a SQL Query to Extract the General Ledger

Use a SQL query similar to the following to extract the required fields listed below:

SELECT 
  AccountName,
  AccountCode,
  TransactionDate,
  TransactionNumber AS Reference,
  TransactionAmount AS Balance,
  TransactionDescription
FROM 
  GeneralLedgerTransactions
WHERE 
  TransactionDate BETWEEN '2024-01-01' AND '2024-12-31'
ORDER BY 
  TransactionDate;

Required Fields:

  • Account Name
  • Account Code
  • Transaction Date
  • Transaction Number/Reference
  • Transaction Balance
  • Transaction Description (optional but recommended)

 

Step 4: Export the Results

  • In SSMS, right-click the results grid and choose Save Results As.
  • Export the report in (.CSV), (.XLSX), or (.XLS) file format.
  • Check that all expected account balances are present, including those with zero balances.
  • Save the report securely for upload to the Validis portal.

 

How to Extract a Trial Balance Report

Step 1: Use SQL to Query the Trial Balance

  • Use the below sample SQL query to request the Required Fields:
    • Account Name
    • Account Code
    • Account Balance
SELECT 
  AccountName,
  AccountCode,
  SUM(TransactionAmount) AS AccountBalance
FROM 
  GeneralLedgerTransactions
WHERE 
  TransactionDate BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY 
  AccountName, AccountCode
ORDER BY 
  AccountCode;

 

Step 2: Export the Report

  • Check that all expected account balances are present, including those with zero balances.
  • Export the report in (.CSV), (.XLSX), or (.XLS) file format.
  • Save the report securely for upload to the Validis portal.

Related to

Related articles