--- title: How to import my chart of accounts from Optigem? description: "To sync transaction data from Optigem to ChurchTools, you’ll need to set up your chart of accounts in ChurchTools first. To do this, export your accounts from Optigem, edit the data, and then import them into ChurchTools. Here’s a step-by-step…" url: "https://staging.churchtools.academy/en/help/churchtools-modules/import-from-optigem/how-to-import-my-chart-of-accounts-from-optigem/" updated: "2026-07-15" category: Finances / Import and export / Import from Optigem --- # How to import my chart of accounts from Optigem? To sync transaction data from Optigem to ChurchTools, you’ll need to set up your chart of accounts in ChurchTools first. To do this, export your accounts from Optigem, edit the data, and then import them into ChurchTools. Here’s a step-by-step guide on how to do it. ## 1. Prepare ChurchTools ### 1.1 Create Account Groups in Finance In the Finance module, you create all *the account groups* you need for your chart of accounts in advance. To learn how to create account groups and other master data in the Financials module, see the article " [How to create a chart of accounts?"](https://staging.churchtools.academy/en/help/churchtools-modules/master-data-finance/how-to-set-up-an-account-chart/#2-toc-title) **Tip** In Step 3.2, you'll then map the account groups in the import file accordingly. Alternatively, you can simply create an " *Optigem Import* " account group. In that case, you won’t need to adjust the import file, since the *“Optigem-Import”* account group is set as the default for all accounts. However, this means you’ll need to manually assign the accounts to the various account groups in Finance after the import is complete. **Note** If you have assigned all accounts to only one account group (e.g., Optigem Import), budgets, for example, cannot be calculated correctly because bank accounts must be designated as cash accounts for this purpose. If you want to calculate budgets, you should definitely assign at least the *“Bank and Cash Balances”* account group to the relevant accounts and adjust the accounts accordingly in your import file. > ### 1.2 Create Tax Rates If you want to assign specific tax rates to accounts, create all *the* necessary *tax rates* in the Finance module beforehand. In step 3.5, you can then adjust the tax rates in the import file accordingly. To learn how to set up taxes, check out the article " [How to Set Up Taxes?"](https://staging.churchtools.academy/en/help/churchtools-modules/master-data-finance/19-how-to-add-taxes/) ## 2. Export and prepare general ledger accounts from Optigem Next, you'll need to export the accounts you've been using from Optigem and prepare them for import into ChurchTools. You have two options for doing this. One is to export your general ledger accounts as an Excel file, and the other is to run an SQL query directly in Optigem, which will return only the account numbers and names. ### Option 1: Export your general ledger accounts as an Excel file from Optigem To do this in Optigem, follow these steps: Open the general ledger accounts and access the *data sheet* in the *View* tab. [![Screenshot: View the Optigem data sheet](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-20.png)](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-20.png) Next, switch to the " *More* " tab and click on " *Excel*." [![Screenshot: Select Optigem Excel](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-21.png)](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-21.png) Then save the export under the name of your choice. You don't need to save the export steps. [![Save the file exported from Optigem (screenshot)](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-22.png)](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-22.png) Open the Excel file and select the entire table. Under " *Data*," sort everything by *subaccount*. **Note** Subaccounts from Optigem are automatically assigned to the main account, since there are no subaccounts in ChurchTools. If you need this breakdown, you'll be able to assign the relevant transactions to the appropriate cost centers after the transaction data has been synchronized. [![Screenshot: Select the table and sort by subaccounts](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-23.png)](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-23.png) In the sort warning, you specify that *numbers and numbers formatted as text* should be *sorted separately*. [![Screenshot of Excel Sort Warning](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-24.png)](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-24.png) Select and then delete all rows that **do not** contain **a 0** in the " *Subaccount* " column. ### Option 2: SQL Query If you only want to export the account number and name, the easiest way to do this is with an SQL query. [![Screenshot: How to Run an SQL Command in Optigem](https://staging.churchtools.academy/wp-content/uploads/2025/11/Optigem_SQL-Kommando-ausfuehren.png)](https://staging.churchtools.academy/wp-content/uploads/2025/11/Optigem_SQL-Kommando-ausfuehren.png) To do this, open the first fiscal year you want to import in ChurchTools (1), and then use the question mark (2) to enter an SQL command (4). Enter the following SQL command here and confirm your entry by clicking OK. `SELECT Sachkonten.[Hauptkonto-Nr], Sachkonten.Kontoname FROM Sachkonten WHERE (((Sachkonten.[Hauptkonto-Nr]) In (select [HK] from Teilbuchung)) AND ((IIf(([Personenunterkonten] Or [Art]>=3) And [Unterkonto-Nr]>0,-1,0))=0)) ORDER BY Sachkonten.[Hauptkonto-Nr];` Optigem will then display the account numbers and names in a table. **Note** Subaccounts from Optigem are automatically assigned to the main account, since there are no subaccounts in ChurchTools. If you need this breakdown, you'll be able to assign the relevant transactions to the appropriate cost centers after the transaction data has been synchronized. [![Screenshot of the SQL query results](https://staging.churchtools.academy/wp-content/uploads/2025/11/Optigem_Ergebnis-SQL-Kommando.png)](https://staging.churchtools.academy/wp-content/uploads/2025/11/Optigem_Ergebnis-SQL-Kommando.png) ## 3. Fill in and customize the account import template ### 3.1 Fill in the ChurchTools-Optigem account import template with your data Download this [account import template](https://staging.churchtools.academy/wp-content/uploads/2022/09/ChurchTools-Optigem-Kontenimportvorlage.xlsx). **Copy all the rows from your export file and**paste them into the " *General Ledger Accounts from Optigem* " tab in the account import template. Be sure to overwrite all the sample data. The template is designed so that the account numbers and names of your accounts are automatically transferred to the " *Account Import* " tab *in ChurchTools*. **Note** If fields marked in red appear in your data, as shown here, it means you haven't deleted all the rows that **do not** contain **a 0** in the *"Subaccount* " column. In this case, repeat step 2b and copy the data from your export file again. ![Screenshot of an Excel spreadsheet with subaccounts highlighted in red](https://staging.churchtools.academy/wp-content/uploads/2022/09/CleanShot-2025-10-16-at-15.25.20.png) Finally, in the *"Import Accounts to ChurchTools* " tab, select the last visible row and use the small triangle to scroll the table down until **all your accounts** are **visible**. [![Screenshot: Make Accounts Visible in the General Ledger Export File](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-25.png)](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-25.png) **Add a 0 to the beginning of all account numbers less than 1,000** so that all account numbers are 4 digits long; in other words, write 0520 instead of 520. ### 3.2 Optional: Customize Account Groups Under " *Account Group"* in the *"Account Import to ChurchTools* " tab, *"Optigem Import"* is selected by default. If you have already created your own account groups in ChurchTools (see step 1a), you can now assign them directly to the relevant accounts here. **Note** - If the *"Account Group* " column is missing or left blank, ChurchTools will use the first account group from the master data in "Finances" by default during import. - The accounts can still be adjusted after the import. **Tip** If you're not sure which account groups correspond to which accounts, or whether an account is an EB account or a donation account, you can search for the account in [the SKR03 chart of accounts](https://staging.churchtools.academy/wp-content/uploads/2024/07/skr03.csv) to find the correct account group and determine whether it's a donation account, etc. ### 3.3 Optional: Mark donation accounts In this step, you select the donation accounts by entering a 1 or an x in the " *Donation Account* " column. **Note** - If the *"Donation Account* " column is missing or left blank, the account will not be created as a donation account when imported into ChurchTools. - Accounts can still be edited after import. ### 3.4 Optional: Select Opening Balance Sheet Accounts In the *"EB Account" column, you should also mark the account* with a 1 or an x to indicate that it is an EB account. EB accounts are typically accounts starting with 9000. **Note** You can also manually designate these accounts as EB accounts in ChurchTools even after they have been imported. ### 3.5 Optional: Fill out the tax form In the " *Sales Tax"* column, enter the name of the tax rate. This must already exist in the master data in Finance (see point 1b). **Note** - If the *"Sales Tax* " column is missing or left blank, no tax rate will be assigned to the account when it is imported into ChurchTools. - You can also add the tax rate manually after importing into ChurchTools. **Tip** We recommend that you compare the account groups in your template and in ChurchTools before importing and align them if necessary. Only entries that match 100% can be imported. ### 3.6 Save Document Once you have correctly adjusted all the data, make sure the *"Account Import to ChurchTools* " tab is currently selected. Save your adjusted account import via *File* > *Save As...* in *CSV UTF-8 (comma-separated values)* format as a CSV file. ![Screenshot: Saving the General Ledger Export File](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-5.png) Since the account import template contains multiple sheets, you'll be asked if you want to save only the active sheet. Confirm this prompt. ![Save a screenshot of the active sheet of the general ledger export file](https://staging.churchtools.academy/wp-content/uploads/2022/09/image-6.png) ## 4. Import Accounts into ChurchTools In *the Finances module*, open the *master data*, go to the *accounts*, and open the *context menu* for the accounts. Here, select " *Import costs from CSV file*." We'll show you exactly how to do this in our guide, " [How to import account data from a file?](https://staging.churchtools.academy/en/help/churchtools-modules/import-and-export-finances-en/how-to-import-account-data-from-a-file/)" ## Read more - [How to import account data from a file?](https://staging.churchtools.academy/en/help/churchtools-modules/import-and-export-finances-en/how-to-import-account-data-from-a-file/) - [How to import projects from Optigem as cost centers into ChurchTools?](https://staging.churchtools.academy/de/help/churchtools-module/optigem-import/wie-importiere-ich-projekte-aus-optigem-als-kostenstellen-nach-churchtools/) - [How to synchronize transaction data?](https://staging.churchtools.academy/en/help/churchtools-modules/sync-finances/how-to-synchronize-accounting-data/) - [Establish a connection with Optigem](https://staging.churchtools.academy/de/help/churchtools-modules/verbindungen-konfigurieren/verbindung-mit-optigem-herstellen/)