Summary

Within this article you can find links to a variety of popular data formatting resources available within the MindBridge Knowledge Base. This can be a useful starting point for any questions you may have regarding data formatting tips, tricks and requirements.

Before you begin

The first step to formatting a general ledger file for import into MindBridge is to keep in mind what the data needs to look like and work backwards from there to identify what needs to be done and then how to do it.

See Data Formatting Guide: Data Requirements for details. It is equally important to determine as soon as possible whether the file can even be formatted; if the file cannot be formatted, you will need to ask your client for a better export.

Especially with larger files, you should also identify which columns are needed in your analysis before beginning any formatting. Large files can take a lot of time to ingest, not to mention to manipulate during formatting, so if you know which columns you need to examine, keep only those in order to save time and space.

How-to guide for common data formatting scenarios

• Combining multiple files Excel workbooks and/or worksheets - you must make sure that all of the columns are the same across all workbooks and worksheets. If they are not, you will need to manually rearrange them into one order to follow for all files. Once this is done, copy and paste the data (without the headers) from all the workbooks and worksheets into one single worksheet with one header in row 1. If there are more than 1048576 rows combined, you will not be able to combine them in Excel.
  • December 2021 update: The new merge tool lets you combine multiple files that are otherwise in a good and uniform format into one! To combine multiple worksheets within a workbook, first split the sheets into individual files, then use merge.
• Data Formatting Guide: The Basics - Start here for the most common issues (extraneous header lines and totals, simple sign flipping, simple ungrouping/dragging down/splits without needing to write formulas).
• Data Formatting Guide - Quickbooks Sub-Accounts - How to format a Quickbooks file that is grouped by accounts and with sub-accounts in different columns. MindBridge requires that each entry have an account number, and at only the most granular level account level.
• Data Formatting Guide: Quickbooks Sub-Accounts separated by : (extracting the text after the last occurrence of a delimiter) - How to format a Quickbooks file with sub-accounts in one column, delimited by : or any other character, by extracting all text coming after the last occurrence of the delimiter.
• Data Formatting Guide: Simple ungrouping of multiple columns, fill in blank account numbers (common QuickBooks example) - How to do a simple ungrouping (dragging down) for multiple columns and fill in blank account numbers, commonly found in Quickbooks exports that are grouped by transaction.
• Data Formatting Guide: Flipping signs for a range of accounts or based on condition(s) - If using Amount column, all debits must be positive and credit columns negative. If using Debit and Credit columns, all accounts must have default positive balance. Consult this guide if you need to flip signs for a range of accounts or based on a debit/credit indicator.
• Account Number Inconsistencies - Why and How to Fix for Accurate Account Mapping - how to stopping the stripping of leading and/or trailing 0s and make your account numbers consistent across GL, TB and Chart of Accounts.
• Data Formatting Guide: Looking up and Retrieving Data from Another File - Have some data that needs to be brought in from another file? Consult this guide.
• Data Formatting Guide: Ungroup and split account numbers and descriptions - Grouped files, especially with account headings followed by all transactions for each account, are very common but not readable by MindBridge. Here's how to ungroup them.
• Data Formatting Guide: Ungroup and split: Extract complex patterns - More tips on ungrouping grouped files with more complex patterns to extract account information.
• Data Formatting Guide: Ungrouping and Checking Imbalance - The formatted file does not balance. Review ungrouping and how to troubleshoot the imbalance.
• Data Formatting Guide: Consolidating Duplicate Trial Balance Accounts - Here's how to take care of the most common issue preventing ingestion of your TB.
  
  

Tools already available in MindBridge

MindBridge allows for minor data manipulation to be performed within the app during the column mapping process:

• Split columns
• Join columns
• Duplicate columns
• As noted above, as of the December 2021 release, our new merge tool lets you combine multiple files that are otherwise in a good and uniform format into one! To combine multiple worksheets within a workbook, first split the sheets into individual files, then use the merge tool.
  
  

Additional resources

• Data Formatting Guide: Excel quick reference - A cheat sheet with the most useful Excel formulas and tools to format data
• Converting text files (delimited or fixed width) to Excel - MindBridge can ingest text files, but Excel is still the best way to examine data for a number of tasks. Here's how to import your text files, whether provided by your client or our data formatting team, into Excel.
• If you still need help, please see Best practices: Before submitting a data formatting request and feel free to submit a data formatting request.
  
  

Anything else on your mind? Chat with us or submit a request for further assistance.