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.
|
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). |
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.
Step 2: In the Data tab, select From Text/CSV.
Step 3: A popup opens. Select the CIF (.csv) file that you wish to open.
Step 4: The file opens. Select Transform Data.
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).
Step 6: In the Transform section, select the Data Type dropdown menu and select Text.
Step 7: A popup appears. Select Replace Current.
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).
|
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.
The Power Query Editor 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).
Step 11: Click on the File dropdown menu and select Save As. Save the file using the CSV UTF-8 (*.csv) file type.
Step 12: Select or enter a file name and click Save.
Step 13: A popup warning may appear. Click OK.
Your edits to the .csv file are saved.
Comments
Wow, deep Exel know
Please sign in to leave a comment.