FIMS Data Extract

Extract FIMS Data

Extract FIMS data for use in conversions with the FIMS Data Extract tool.

• For General NXT conversions, the extraction compiles data from each FIMS table and creates a corresponding TXT file for each table.
• For Accounting conversions, the extraction pulls General Ledger transaction data starting from a specified start date into one CSV file.

Notes

You can run both extractions concurrently or just one extraction individually.

This tool can be used for other purposes, such as if you would like to extract your data for archival purposes. 

To extract data from FIMS:

1. From anywhere in FIMS, go to Tools > System Utilities > System Reports > FIMS Data Extract.

2. Select either or both of the following options: 

• Extract FIMS data for General NXT Conversion which extracts FIMS table data as TXT files.
• Extract FIMS GL Transactions, Vendors, Bank, and others for Accounting Conversion which extracts information into one CSV file.

3. Browse to an existing Destination Folder or specify a path for the system to create files. 

Tips: 

On-premises: 

• The tool can create a sub-folder within the main one if it does not exist. The subfolder does not have to exist, the process will create it but the main folder does have to exist. For example, let's say you want to export out to C:\Data_Exports\Accounting. The Data_Exports folder has to be created in advance prior to running the export, but the sub-folder Accounting can be created by the export. You an also choose to create all of the folder structure first before doing the export.

Azure Cloud: 

• You have to first create all Destination folders in the path before doing the extract because the tool will not create folders in Azure Cloud. If all of the folders do not already exist in the S drive and you try to run the export, you will get an error. 
• If you are on Azure Cloud, extract data out to the S drive.

For Accounting Conversion export only: 

Complete the remaining fields as shown above:

• Enter a Start Date as MM/01/YYYY such as 05/01/2018. Use any month and previous or current year, but the day of the month must be the first day (01). Any day entered that is not the first automatically adjusts to the first day. The extraction includes General Ledger data from this date to the current day.
• Choose whether you want only Active or All General Ledger Master Chart of Accounts in the extract and whether you want the Master Chart on Pre-Consultation Report.
• Choose option for mapping file. By default, the extract creates a mapping file. You can either choose a different file or select to Ignore the mapping file. The mapping file shows FIMS GL to FE GL mapping. It is recommended to create the mapping file and not ignore it so that it can be used during the conversion process.
• Choose whether to Extract FIMS Invoices, Distributions and Payments. 
• Select Voucher Types from the drop-down.
• Choose to Replace Carriage Returns with Paragraph Mark (Alt-182)? This option replaces embedded carriage returns with the Microsoft paragraph character (¶) so you can more easily import data into other applications.

4. Click OK.

• For on-premises FIMS users, you will be prompted if the folders for the General NXT Destination or the Accounting Destination do not exist. Click Yes and then the tool will create the sub-folders that you typed into the destination folder paths. If the folders already exist, you will not see these messages.

5. If exporting out Accounting information, choose Yes or No to create a single Excel file from Journal History and beginning balances

• During extraction for accounting information, you will see this message that asks if you want to create a single Excel Worksheet from Journal History and Beginning balances that will contain different tabs for each Quarter for each year.
• Click Yes to create a single Excel XLSX file that contains multiple tabs for each quarter and year in addition to the separate CSV files that are created for each year and quarter.
• Click No to not create a single Excel XLSX file with multiple tabs for each quarter and year. If you click No, there will still be the separate CSV files created for each year and quarter.
• Note if you are using FIMS hosted on NPact Azure, you will not get this prompt due to Excel not being installed.

• After clicking yes or no above, during the extract process, you will see various "Dumping" and other extraction messages display. Here are some examples of what you will see. You will see several of these messages indicating different tables. Do not click stop on any of these messages, the process will complete on its own.

• You will see this message when the extraction is complete:

6. Once the extract completes:

a. Accounting Conversion data is located in these files:

FIMSAccountingAPVendors.csv - Vendors/Grantees data export

FIMSAccountingBankInformation.csv - Bank/Investment Account for FE Bank/Treasury from FACTS and Accounts Payable Account records.

FIMSAccountingFiscalYears.csv-Fiscal year, description, number of periods, start date, end date

FIMSAccountingFund-Project-Attributes.csv – FIMS Fund coding, etc. that could become FE Project Attributes.

FIMSAccountingFund-Project-Comments.csv – FIMS Fund multi-line text fields including Fund Comments, Gift Language, Operational Notes, Yearbook and Fund Notes data.

FIMSAccountingFund-Projects.csv – Basic FIMS Fund information

FIMSAccountingGlJourhisBegBal.csv

• Beginning Balance and Journal History data from General Ledger. Includes the beginning balance and every transaction since the specified date. Columns for Division, FIMS Account Type and Net Asset Type are also included. 
• You will see separate CSV files for each quarter, FIMSAccountingGlJourhisBegBal=2023Q1.csv, etc. 
• If there are for example no Q2 or Q3 files for a certain year, it is most likely because there is no data for that year and quarter in question.

FIMSAccountingGlJourhisBegBal.xlsx-You will see this Excel file if you clicked Yes to export a single Excel file. 

FIMSAccountingInvoiceDistributions.csv

FIMSAccountingInvoices.csv

FIMSAcccountingMasterChart.csv

FIMSAccountingPayments.csv

FIMSAccountingPreConsultation.txt- Information for NPact's PreConsultation Survey including:

• List of Fiscal Years
• General Ledger Master Chart of Accounts (with either Active or Inactive accounts as selected)
• Master Account Chart Ranges to verify no overlapping accounts
• General Ledger Segments with counts of active Funds for each code value
• Other Codes with counts of active Funds for each code.
• Spending Policies
• Administrative Fees

> Net Asset Type:

• 00 = Assets and Liabilities (otherwise 0X)
• 01 = Account Types of Q1, T1, R1, X1
• 02 = Account Types of Q2, T2, R2, X2
• 03 = Account Types of Q3, T3, R3, X3

b. General Conversion data

• In the directory selected for the general export, for each table, a TXT file exists. Data is separated by a vertical bar delimiter.