Opening CIF Files in Excel

Follow
We're transitioning to a new User Interface, and are in the process of updating Help Hub content to match the new interface.

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 create your CIF Files in a text editor, you can re-open them in Excel to edit them.

This document shows you, a Planning Maestro user, how to open your existing CIF Files in Excel, edit them, and save them in the correct format.

Warning.png PLEASE NOTE:
If your data contains leading or trailing zeros (zeros that begin or end some values, like the zip code 01470) do NOT open your CIF File by double-clicking on it.

Double-clicking causes Excel to guess the data type of every value. When it reads a value as a number, it strips leading and trailing zeros, even for values like zip codes and account numbers that need those zeros. Instead, open Excel first with a blank workbook, and then open the file (as described below).

Example.png EXAMPLE:
This document uses an example of a CIF File with an account number that has three segments (XXX-XXXX-XX) and Dimensions for each segment (XXX, XXXX, and XX).

Step 1: Open Excel with a blank workbook and select the Data tab.

C5a.png

Step 2: In the Data tab, select From Text/CSV.

C5b.png

Step 3: A popup opens. Select the CIF (.csv) file that you wish to open.

Step 4: The file opens. Select Transform Data.

C5c.png

Step 5: The Power Query Editor popup opens. Select the column for the data with leading and trailing zeros (in this example, Segment 1 of the Account Number, which has three digits).

C5d.png

Step 6: In the Transform section, select the Data Type dropdown menu and select Text.

C5f.png

Step 7: A popup appears. Select Replace Current.

C5g.png

The values in the column you selected show their actual values (because Excel no longer treats them as numbers, and doesn’t trim the leading and trailing zeros).

Example.png EXAMPLE: 

Segment1 column values are replaced by the actual value of 000, instead of being shortened to 0.

Step 8: Repeat the previous steps for other columns that use leading and trailing zeros (zip codes, account numbers, etc.).

Step 9: Click Close & Load.

C5j1.png

The Power Editor Query closes, and your transformed data appears on the Excel sheet. Now, you can edit or update the file without losing the formatting.

Step 10: When you are finished editing, delete all of your extra, blank worksheets besides the one containing your data (having more than one sheet causes issues when you save).

C5m.png

Step 11: Click on the File dropdown menu and select Save As. Save the file using the CSV UTF-8 (*.csv) file type.

C5k.png

Step 12: Select or enter a file name and click Save.

C5k1.png

Step 13: A popup warning may appear. Click OK.

C5l.png

Your edits to the .csv file are saved.

2 out of 2 found this helpful

Comments

1 comment

Please sign in to leave a comment.