Table of Contents

Introduction

Downloading CIF File Templates

General Information

Base Structure CIF File

Base Structure Members CIF File

Account Summary or Account Transactions File

Transaction Details CIF File

 

Please Note: Use this document after you have used Preparing Your Data to Create CIF Files to create output files of data from your GL (General Ledger) or ERP system.

 

Centage Interchange Format (CIF) Files are the file format through which you upload your company’s data to Centage. These files are .csv files containing your General Ledger data, including your company’s GL Account Numbers and departments.

This format is broken into separate files that use specific file names and contain metadata that helps Centage set up your account.

After you upload your CIF Files, you can use this data to build your budget.

This document shows you, a Centage user, how to format your company’s data into CIF Files.

 

 

Please note:  If you want to use Excel to format your existing CIF Files, see Opening CIF Files in Excel. To upload your CIF Files after you create them, see Upload Data. Centage treats CIF Files as case sensitive: for example, it treats account and ACCOUNT as different values.

General Information

Types of CIF Files

There are five types of CIF Files:

1. Base Structures – Contains your Base Structures, or the building blocks of your company (such as your departments and currencies).

 Example: Account Number, Division, and Department.

2. Base Structure Members – Contains your Base Structure Members, or the parts and pieces of each of your Base Structures (such as individual GL Account Numbers).

3. Account Summary – Contains a summary of transactions for the set of Base Structures (debit, credit, and net amount ).

4. Account Transactions – Contains transactions for the set of Base Structures for every period (debit, credit, and net amount). This file may also contain extra information about each transaction.

5. Transaction Detail Columns (optional) – Contains information about extra columns included in your Account Transactions file.

 

Please Note: Not every data upload needs to include all five types.  This document explains: The format of each CIF File. When to use each type of CIF File.  Please Note: If needed, you can download the sample CIF Files attached to this article to help you build your own.

 

Creating .csv Files

If you are creating the CIF Files yourself, use the following format to successfully upload them to Centage.

1. csv files – Centage only accepts comma delimited files. Insert a comma between the value for each column.

2. Double quotes – To use special characters, like a comma, inside a value, you can use double quotes around the text. For example, if you wanted a name to display as Smith,John after the import, use “Smith,John” in the .csv file .

• Do not use line breaks as part of a name, description, or other text.

3. Header line – Use a header line, or list of column names, as the first line in the file. Use a comma between each column name value.

• You can’t have two header values that are the same (for example, two TransactionDate columns).

4. Rows – (Optional, but recommended) Start each set of values on its own row.  This format makes the files easier to read and edit in text editors.

5. Number of Values – Use the same number of values for each row.

6. Carriage returns – Do not add a carriage return after the last record. (In other words, don’t press Alt-Enter to start a new line after the last row).

Please note: For examples of correctly formatted CIF Files, review the sample files available on the Source Data page in the Data Modeler module of Centage.

 

Editing CIF Files through Excel

If you are editing the CIF Files using Excel, see Opening CIF Files in Excel. Use the following guidelines:

1. Format the columns correctly.

• Use the Text column type for most of the columns. Using Text as the type helps prevent upload errors, since Excel crops leading and trailing zeros for anything it identifies as a number.

 

• In your Base Structures and Base Structure Members files, set all columns to the Text column type.
• In your Account Summary file, set every column before the FiscalYear column to the Text column type.
• In your Account Transactions file, set every column before TransactionDate to the Text column type.
• For columns with numbers, use the Number column type.

2. When you save the file, select Save As and CSV UTF-8 as the file type.

Please note: If your data contains leading or trailing zeros (zeroes that begin or end some values, like the zip code 01470) do NOT open your Excel file by double-clicking on it. Double-clicking causes Excel to treat everything as a number, so it strips these zeros, even for zip codes and account numbers. Instead, open Excel first with a blank workbook, and then open the file (as described here).

 

File Names

File Names

Use the following format for file names:

(Filetype)_(TemplateID)_(datetime)_(optional additional details).csv

The descriptions of files below give an example for each type.

Please Note: Currently, the TemplateID is CIF_V1, so that is what is used in each of the examples.

Maximum Characters

The maximum number of characters allowed in a column is indicated in brackets [ ] after the column name.  If a value is greater than the maximum number of characters, Centage truncates (shortens) it.

Names You Cannot Use for Base Structures

The following is a list of names you cannot use for your Base Structures (either because they are either already column headers in some files, or for some other reason).

This means that in the Base Structures file you cannot use these as Names, in the Base Structure Members file you cannot use them as Base Structure Names, and in Account Summary or Account Transactions you cannot use them as column headers.

• GLSource
• Name
• Type
• BaseStructureName
• Status
• FiscalYear
• PeriodID
• PeriodType
• Debit
• Credit
• Amount
• TransactionDate
• Measure
• Transaction Type
• Dataprojection Type
• ID
• Activity
• Balance

Base Structures CIF File

Base Structures are the building blocks of your company: your GL Accounts, departments, regions,  currencies, programs, products, grants, classes, and Versions.

We recommend that you upload the Base Structures file as part of your first upload, so your Base Structures are created correctly from the start.

If you do not include a Base Structures file in your first upload, Centage builds your Base Structures using your Base Structure Members, Account Summary, and/or Account Transactions CIF Files (described below). Then, you need to do some extra steps inside Centage in order to be able to deploy. 

After your first upload, you only need this file if you are adding new Base Structures (for example, a new Office Base Structure containing your company’s offices).

File Name: BaseStructures_(TemplateID)_(datetime YYYYMMDDHHMMSS)_(optional additional details).csv

 Example: BaseStructures_CIF_V1_20190121154247_Sample.csv

Base Structure CIF File - Columns
GLSource [up to 128 characters]	Name [up to 128 characters]	Description [up to 256 characters]	Type
Do not leave this column blank.*	Do not leave this column blank.*	If you leave this column blank, Centage uses the Name as the Description.	Do not leave this column blank.*
In this column, enter information about the source company the data comes from (often the name of your company).  Example: You could use the source system type and company name (for example, DynamicsGP My Company) Use a unique name for each GL Source to help yourself identify it in Centage.	In this column, enter the name of the Base Structure.  Example: Account Number, Department, Office, Product, Program, Grant, Currency, Version	In this column, enter a description of the Base Structure.	In this column, enter the type of the Base Structure (what kind of data it contains). Use one of the following types: AccountNumber – Use this type for Base Structures that contain General Ledger Account Numbers. Currency – Use this type for Base Structures that contain currencies (such as dollars, euros, etc.). Version – Use this type for Base Structures that contain your Versions (your Actuals, next year’s budget, best case scenario, etc.). Dimension – Use this type for “wild card” Base Structures that don’t fit any other type (departments, programs, classes, etc.). AccountGroup – Use this type for Base Structures that contain groups for your GL Account Numbers (Assets, Equity, Liabilities, Expenses, Revenue, etc.). Attribute – Use this type for Base Structures containing group names or categories for Members of other Dimensions (for example, categories for projects for a Project Dimension).

 

Please Note: *If you leave any cells in the GLSource, Name, or Type columns blank, your file upload will fail.

Example from a .csv file in Notepad:

 

Base Structure Members CIF File

This CIF File provides the names and descriptions for the Members of the Base Structures (for example, the list of departments for the Department Base Structure).

We recommend that you upload this file as part of your first upload to make sure all Members have the correct description.

Please Note: You can also add new Base Structure Member information from within your Account Summary and the Account Transaction CIF Files. When Centage finds a new Member in those files, it adds them to the system with only a Name and a Description that is a duplicate of the Name.

In future uploads, you only need this file if you are adding Members to your Dimensions that need exact descriptions.

File name: BaseStructureMembers_(TemplateID)_(datetime YYYYMMDDHHMMSS)_(optional additional details).csv

 Example: BaseStructureMembers_CIF_V1_20200121154247_Sample.csv

Base Structure Members CIF File - Columns
GLSource [up to 128 characters]	BaseStructureName [up to 128 characters]	Name [up to 128 characters]	Description [up to 256 characters]	Status [Active or Inactive]
Do not leave this column blank.*	Do not leave this column blank.*	Do not leave this column blank.*	If you leave this column blank, Centage uses the Name as the Description.	Do not leave this column blank.*
In this column, enter information about the source company the data comes from (often the name of your company).  Example: You could use the source system type and company name (for example, DynamicsGP My Company). Use a unique name for each GL Source to help yourself identify it in Centage.	The name of the Base Structure this Member belongs to.  Please Note: If the name you enter in this column does not match a Base Structure from the Base Structures file, Centage creates a Base Structure with that Name using Dimension as the Base Structure Type. "GLSource" cannot be used as a BaseStructureName here.	The name of the Base Structure Member.	A description of the Base Structure Member.  Please Note: If you leave this column blank, Centage uses the Name as the Description.	This column notes whether the Base Structure Member is active or inactive. After the upload, you can check this column to see which Base Structure Members to exclude from a Plan. Uploading inactive Base Structures is useful if you have a Member you want to keep for your records, or plan to add soon, but don’t want to include in your Plans yet (such as an office your company is opening soon).  Please Note: You can only enter “Active” or “Inactive” for this column. This column only does not automatically make a Base Structure Member inactive or inactive; this status reminds you to manually exclude Members in Centage.

 

Please Note: *If you leave any cells in the GLSource, Name, BaseStructureName, or Type columns blank, your file upload will fail.

Example from a .csv file in Notepad:

 

Account Summary or Account Transactions CIF Files

Please Note: Some GL systems can only generate Account Summary. If your GL system can generate Account Transactions, you only need an Account Transactions file. You do not need to upload both types of files.  Please Note: If you upload only an Account Summary or Account Transactions file, Centage uses this file to build your Base Structures and Base Structure Members.

 

Account Summary	Account Transactions
Summary	Details
Contains a summary of transactions for each period for each Base Structure (debit, credit, and/or the amount).	Contains every transaction for each Base Structure for every period May also contain extra information about each transaction. Can provide extra information in a drill-down when you use Analytics Maestro later.  Please Note: If you can upload Account Transactions, you do not need to upload Account Summary.

Account Summary File

This CIF File contains a summary of transactions for each period for each Base Structure (debit, credit, and/or the amount).

Please Note: If you can provide an Account Transactions file, you do not need an Account Summary file. Only upload an Account Summary file if your GL system can only export a summary of your accounts.

File Name: AccountSummary_(TemplateID)_(datetime YYYYMMDDHHMMSS)_(optional additional details).csv

 Example: AccountSummary_CIF_V1_20190121154248_Sample.csv

Account Summary CIF File - Columns
GLSource [no more than 128 characters]	BaseStructure 1 [no more than 128 characters]	Other Base Structures (as many columns as needed) …	FiscalYear	PeriodID	PeriodType	Debit	Credit	Amount
Do not leave this column blank.*	If you leave one of these columns blank, when you deploy, Centage inserts a default value as the Base Structure/Dimension.		Do not leave this column blank.*	Do not leave this column blank.*	If you leave this column blank, Centage assumes the PeriodType is P.	If you leave one of these columns (Debit, Credit, or Amount) blank, Centage can calculate the missing amount. If you leave all three blank, Centage assumes they are all zero.
The GL Source, or name of the source company the data comes from (see the previous slides).	The name of the first Base Structure.	The names of your other Base Structures.	The four-digit fiscal year for the Base Structure.  Example: 2021	The one or two-digit ID number for each period.  Example: If your Fiscal Year follows the calendar, use 1 or 01 for January	The type of each period: P – A normal period (this type must be one or two digits between 1 and 13, or 01 and 13*) C – Closing Transaction, or part of the year end close (some sources use this for transactions on December 31 at 11:59:59 pm) B – Beginning Transaction, or beginning balance data (sometimes called P0) If you leave this column blank, Centage assumes the PeriodType is P. *Use 1 and 12 or 01 and 12 if your company uses a 12-month fiscal calendar. You can use 13 if your company uses a 13-month fiscal calendar.	Debit amount	Credit amount	Net amount: debit amount - credit amount

 

Please Note: *If you leave any cells in the GLSource, FiscalYear, or PeriodID  columns blank, your file upload will fail.

Example from a .csv  file in Notepad:

Please Note: If you leave one of the three last columns (Debit, Credit, or Amount) blank, Centage can calculate the missing amount. If you leave all three blank, Centage assumes they are all zero.

 

Account Transactions

This CIF File contains every transaction for each Base Structure for every period. This file may also contain extra information about each transaction. This data can provide extra information in a drill-down when you use Analytics Maestro later. 

Please Note: If you upload only an Account Transactions file, Centage uses this file to build your Base Structures and Base Structure Members.

File Name: AccountTransactions_(TemplateID)_(datetime YYYYMMDDHHMMSS)_(optional additional details).csv

 Example: AccountTransactions_CIF_V1_20200121154248_Sample.csv

Account Transactions CIF File - Columns
GLSource [no more than 128 characters]	Base Structures [no more than 128 characters each] …	TransactionDate	PeriodType	Debit	Credit	Amount	Transaction Detail Columns [optional, no more than 128 characters each] …
Do not leave this column blank.*	If you leave one of these columns blank, when you deploy, Centage inserts a default value as the Base Structure/Dimension.	Do not leave this column blank.*	If you leave this column blank, Centage assumes the PeriodType is P.	If you leave one of the three amount columns (Debit, Credit, or Amount) blank, Centage can calculate the missing amount. If you leave all three blank, Centage assumes they are all zero.			You can leave these columns blank.
The GL Source, or name of the source company the data comes from (see the previous slides).	The names of your Base Structures (one per column).	The date of the transaction. Use one of following formats: ISO (yyyymmddhhmmss)  Example: 20200307101532 SQL (yyyy/mm/dd hh:mm:ss)  Example: 2020/03/07/10:15:32 YYYY-MM-DD  Example: 2020-03-07 YYYYMMDD  Example: 20200307	The type of each period: P – A normal period C – Closing Transaction, or part of the year end close (some sources use this for transactions on December 31 at 11:59:59 pm) B – Beginning Transaction, or beginning balance data (sometimes called P0) If you leave this column blank, Centage assumes the PeriodType is P.	Debit amount	Credit amount	Net amount: debit amount - credit amount	For Transaction Details, use the column names listed in your Transaction Details File (optional).  Please Note: Transaction Details provide extra information about each transaction.

 

Please Note: *If you leave any cells in the GLSource or TransactionDate columns blank, your file upload will fail.

Please Note: All Base Structure columns MUST be situated to the left of the TransactionDate column.  Any columns listed to the right of the Amount column are indicated as Transaction Details.

Example from a .csv file in Notepad:

Please Note: If you leave one of the three amount columns (Debit, Credit, or Amount) blank, Centage can calculate the missing amount. If you leave all three blank, Centage assumes they are all zero.

 

Transaction Details CIF File (optional)

Please Note: The Transaction Detail CIF File is most useful for finance professionals or IT specialists who are used to working with and transferring large, detailed amounts of data. You do not need to upload a Transaction Details CIF File unless your data requires more detailed information you can use later for filtering, reporting, and data analysis.

 

This file provides the names of any extra columns you included in the Account Transactions file. These extra columns provide more information about each transaction, such as the weight of a package or the number of stores that carry a certain product.

 

Please Note: This file is optional. If you do not have any extra columns or the information in extra columns is just plain text, then you do not need to include this file.

 

If the extra columns contain items that should be treated as numbers, or dates, then you should include this file the first time you upload an Account Transactions file.  For future uploads, you only need to include this file if you wish to add more columns that require a definition.

File Name: TransactionDetailColumns_(TemplateID)_(datetime YYYYMMDDHHMMSS)_(optional additional details).csv

 Example: TransactionDetailColumns_CIF_V1_20200121154247_Sample.csv

Transaction Details CIF File - Columns
GLSource [no more than 128 characters]	Name	DataType	Length (for varchar)	NumericPrecision (for numeric or float)	NumericScale (for numeric)
Do not leave this column blank.*	Do not leave this column blank.*	If you leave values in this column blank, Centage assumes they are the varchar Data Type.		If you are using the numeric or float Data Type and leave this column blank, this column defaults to 19.	If you are using the numeric Data Type and leave this column blank, this column defaults to 5.
The GL Source, or name of the source company the data comes from (see the previous slides).	The name of the Transaction Detail.	The type of data in the column. Use one of following Data Types (described in detail on the next slides): varchar date datetime numeric float tinyint smallint int bigint bit	In this column, enter the number of characters for Transaction Details belong to the varchar DataType. For other Data Types, leave this column blank. When you leave it blank, the column defaults to 256.	In this column, enter the number of digits for the numeric or float Data Types. Leave this column blank for other Data Types.	In this column, enter the number of decimal places for the numeric Data Type. Leave this column blank for other Data Types.

 

Please Note: *If you leave any cells in the GLSource or Name columns blank, your file upload will fail.

Please Note: The Name for any Transaction Detail column cannot be the same as any Base Structure Names. For example, if you have a Base Structure of Department, then none of the columns specified within the TransactionDetailColumns CIF file can be Department.

Example from a .csv file in Notepad:

1. varchar

Use this type for any plain text. When you use this Data Type, enter the number of digits for the value in the Length column (up to 256 digits).

 Example: Notes or comments.

2. date

Use this type for dates, with the format YYYY-MM-DD.

 Example: 20201105 for the day of a shipment.

3. datetime

Use this type for the date and time of a detail. Use one of two formats:

• ISO (yyyymmddhhmmss)
• SQL (yyyy/mm/dd hh:mm:ss)

 Example: The date a file was updated.

4. numeric

Use this type for numbers 0 through 15. When you use this Data Type, enter the number of digits in the value in the NumericPrecision column, and the number of decimal places (up to 6 decimal places) in the NumericScale column.

 Example: The exact weight of a package.

5. float

Use this type for numbers up to 15 digits long. When you use this Data Type, enter the number of digits in the value in the NumericPrecision column.

 Example: The number of employees on a company committee.

6. tinyint

Use this type for integers (numbers without decimal places) between 0 and 255.

Example: The number of store locations that have a specific product

7. smallint

Use this type for integers (numbers without decimal places) between -32,768 and 32,767.

 Example: The number of cars sold in a year in a large car corporation.

8. int

Use this type for integers (numbers without decimal places) between -2,147,483,648 and 2,147,483,647.

 Example: The number of customer support tickets in a global software corporation.

9. bigint

Use this type for integers (numbers without decimal places) between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.

 Example: The number of nails produced by a manufacturing company over 20 years.

10. bit

Use this type for certain numbers: 0, 1, or null (no value).

 Example: Whether an office location has a conference room or not (0 for no, 1 for yes, or null for the office of an employee who works remotely and doesn't need a conference room)

 

Downloading CIF File Templates

You can download CIF template files and populate them with data from your GL.  See Upload Data.

Templates and Sample files for the five types of CIF Files are downloaded to your designated download directory. Refer to the following sections for how to enter your company’s data into these CIF Files.

*Technical Notes: File Formatting*

For more information about CIF Files and .csv format, see the following documents:

Opening a CIF File in Excel

After you set up your .csv CIF Files, you can open them in Excel to edit them. Use this document to format the CIF File in Excel correctly.

RFC-4180 Format (General Standard for .csv Files)

https://www.ietf.org/rfc/rfc4180.txt

https://en.wikipedia.org/wiki/Comma-separated_values

UTF-8 Character Encoding

Centage’s CIF File format and Centage supports the UTF-8 character encoding. UTF-8 is a variable-width character encoding that supports every character in the Unicode character set and is widely recognized as the most common encoding across the World Wide Web. UTF-8 has become the dominant character encoding because it is self-synchronizing, compatible with ASCII, and avoids the endian issues that other encodings face.

Therefore, please ensure that you declare the character encoding for the CIF files that you create as UTF-8.

https://www.unicode.org/faq/specifications.html 

https://www.w3.org/International/questions/qa-what-is-encoding