Creating CIF Files

Follow

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

Warning.png 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 Planning Maestro. These files are .csv files containing your General Ledger data, including your company’s GL Account Numbers and departments.

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

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

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

Info.png 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.
  • Planning Maestro treats CIF Files as case sensitive: for example, it treats account and ACCOUNT as different values.

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 Planning Maestro support 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

Downloading CIF File Templates

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

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

General Information

Types of CIF Files

There are five types of CIF Files:

mceclip1.png

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

Example.png 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.

Info.png 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.

Best_practice_icon.png 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 Planning Maestro.

C4f.png

1. csv files – Planning Maestro 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).

Info.png 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 Planning Maestro.

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.

C2a.png 

  • 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.

C2b.png

Warning.png 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.

Info.png 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, Planning Maestro 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, Planning Maestro 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 Planning Maestro 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).

mceclip2.png

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

Example.png 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, Planning Maestro 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.png 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 Planning Maestro.

In this column, enter the name of the Base Structure.

Example.png 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).

Warning.png 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:

C4f.png

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.

Info.png Please Note: You can also add new Base Structure Member information from within your Account Summary and the Account Transaction CIF Files. When Planning Maestro 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.

mceclip3.png

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

Example.png 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, Planning Maestro 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.png 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 Planning Maestro.

The name of the Base Structure this Member belongs to.

Info.png Please Note: If the name you enter in this column does not match a Base Structure from the Base Structures file, Planning Maestro 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.

Info.png Please Note: If you leave this column blank, Planning Maestro 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).

Info.png 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 Planning Maestro.

Warning.png 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:

C4e.png

Account Summary or Account Transactions CIF Files

Best_practice_icon.png 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.

Info.png Please Note: If you upload only an Account Summary or Account Transactions file, Planning Maestro 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).

Warning.png 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.

mceclip4.png

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

Example.png 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, Planning Maestro 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, Planning Maestro assumes the PeriodType is P. 

 

If you leave one of these columns (Debit, Credit, or Amount) blank, Planning Maestro can calculate the missing amount.

If you leave all three blank, Planning Maestro 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.png Example: 2021

The one or two-digit ID number for each period.

Example.png 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, Planning Maestro 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

Warning.png 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:

C6c.png

Info.png Please Note: If you leave one of the three last columns (Debit, Credit, or Amount) blank, Planning Maestro can calculate the missing amount. If you leave all three blank, Planning Maestro 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. 

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

mceclip5.png

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

Example.png 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, Planning Maestro inserts a default value as the Base Structure/Dimension. 

 Do not leave this column blank.*

 If you leave this column blank, Planning Maestro assumes the PeriodType is P.

 

If you leave one of the three amount columns (Debit, Credit, or Amount) blank, Planning Maestro can calculate the missing amount.

If you leave all three blank, Planning Maestro 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.png Example: 20200307101532

  • SQL

(yyyy/mm/dd hh:mm:ss)

Example.png Example: 2020/03/07/10:15:32

  • YYYY-MM-DD
Example.png Example:
2020-03-07
  • YYYYMMDD
Example.png 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, Planning Maestro 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).

Info.png Please Note: Transaction Details provide extra information about each transaction.

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

Warning.png 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:

C4c.png

Info.png Please Note: If you leave one of the three amount columns (Debit, Credit, or Amount) blank, Planning Maestro can calculate the missing amount. If you leave all three blank, Planning Maestro 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.

Info.png 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.

mceclip6.png

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

Example.png 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, Planning Maestro 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):

  1. varchar
  2. date
  3. datetime
  4. numeric
  5. float
  6. tinyint
  7. smallint
  8. int
  9. bigint
  10. 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.

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

Warning.png 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 columns specified within the TransactionDetailColumns CIF file can be Department.

Example from a .csv file in Notepad:

C4a.png

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.png Example: Notes or comments.

2. date

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

Example.png 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.png 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.png 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.png 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.png 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.png 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.png 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.png 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)

1 out of 1 found this helpful

Comments

3 comments
  • TransactionDate supports this format as well:
    yyyymmdd

    0
    Comment actions Permalink
  • During the new ERP implementation in my company, we have 2 Account Codes with different Numbers. this is possible to upload both of account codes? please advice.

    0
    Comment actions Permalink
  • Hello Rahmat - Thanks for reaching out. If these are distinct account codes, Planning Maestro will *only* treat these as distinct account numbers (unless we define them differently, i.e. merge these two as if they are one and the same account, which is a different topic). Therefore yes, when you upload 2 different account numbers, these will maintain their distinctions within Planning Maestro. Thanks again for sharing your question!

    0
    Comment actions Permalink

Please sign in to leave a comment.