Administration Guide
      Back to home
      1. Help Center
      2. Administration Guide

      Instructions for Extracting Data

      This article provides instructions on extracting data for implementation projects.

      Overview

      FI Works uses the SmartSheet application for project planning and to document data sources and requirements.

      Project Sheets

      The implementation project folder contains these sheets:

      • Project Plan
      • Project Contacts
      • Data Sources
      • Data Requirements

      Data Source

      The data sources sheet shows the list of files that can be loaded into FI Works. This sheet will be updated during the data extract phase of the project as we discover the specific files needed for your project.

      Data Requirements

      The data requirements sheet shows the individual fields that can be included in each file. This is the documentation of what will be loaded along with any specific comments and instructions.

      Instructions for Extracting Data

      Please refer to this sheet as you build your extract files. We will update this sheet as we evaluate test files for completeness and accuracy.

      1. Review the requested data for each file type.
      2. Determine the earliest historical month that data is available. If you can get data for month-end files, please start building your queries with the earliest month available.
      3. For monthly transaction files, include all transactions posted within the month. For daily files, only include transactions posted on that business day.
      4. Do not rename the columns in the query to match FI Works names. Use the native name provided by your query tool.
      5. Name the files according to the names provided in the Data Sources task.
      6. If multiple files are needed for the same source (i.e. separate applications for deposits), your FI Works project manager will update the project folders for you.
      7. Use the naming pattern of "filenameyyyymmdd" where the year, month, and name represent the business day being extracted. For example, "customers20240121" should contain customer data from 1/21/2024. For monthly files, use the last business date of the month.
      8. Save files in a tab-delimited format (TXT) or comma-delimited format (CSV). Tab-delimited is preferred.
      9. Fill in the Source Column Name that you are providing for each file along with any comments in the Customer Comments field.
      10. All numeric fields (balances, interest rates, interest accrued) should not be formatted (i.e. commas, dollar signs, or percent signs)
      11. Interest rates should be formatted as regular numbers (i.e. 3.5% should be 0.035)
      12. Data files should not contain duplicate records. If this is unavoidable, FI Works will work to de-duplicate the data if possible.
      13. If a non-required column is not available, do not include it in the file.
      14. Test extract files should be sent to FI Works via FTP