Summary
Learn a few data formatting tricks that can be used to format your file before importing it into MindBridge.
Overview video
Date format
MindBridge can translate all kinds of date formats, as long as Excel recognizes the data as a date, so will MindBridge.
Check to see if your date is properly formatted
- Apply a filter to the data in Excel.
- Use the Filter by Date menu to see how Excel interprets the data
The date format should be grouped as appears in the image below.
Re-format the date
- If data requires re-formatting, select the Text to Columns feature in Excel, found in the Data tab.
- Select Delimited then click Next.
- In step 2 of 3, in the Delimiter section, check the box next to Other, then enter " / " (a forward slash) into the text field.
Note: When using a custom delimiter, be sure to insert the appropriate amount of new columns after the original date column in advance. These new columns will be used to delimit the date.
Alternatively, use the Date delimiter (in step 3 of 3). Be sure the date format in the menu matches your file's date format.
- To pull the date back together, paste the following formula into an empty column:
=DATE(Year Column, Month Column, Day Column)
- Go to the Number tab in the Home ribbon.
- Use the menu to select Short Date.
Below is a before and after image displaying the preferred date format of YYYY-MM-DD.
Format amount or debit and credit values
The amount column, or debit and credit columns, must not contain any special characters. The easiest way to ensure this is to change the column(s) into number format.
- From the Number tab in the Home ribbon, use the menu to select Number.
This will remove special characters (such as "$") from your columns.
Extract a transaction ID from an account number
- or-
Separate the account description from the account number
- Insert a column after the column you are working with and re-name it.
- There are two ways to separate the data into two fields:
- Use Text to Columns by following the above steps to separate the data if delimiters are used.
- Use Visual Basic to extract the data by following step 3 and onwards.
- Go to the Developer tab.
- Open the Visual Basic editor.
- In the Visual Basic editor, select Insert > Module.
- Copy and paste the text below into the white space that appears and select Save (if a prompt appears, select Yes).
Function TextOnly(pWorkRng As Range) As String
'Updateby20140515
Dim xValue As String
Dim OutValue As String
xValue = pWorkRng.Value
For xIndex = 1 To VBA.Len(xValue)
If Not VBA.IsNumeric(VBA.Mid(xValue, xIndex, 1)) Then
OutValue = OutValue & VBA.Mid(xValue, xIndex, 1)
End If
Next
TextOnly = OutValue
End Function
File formatting
Large file sizes take a while to import into MindBridge, but there are a few things you can do to speed things up.
Save the file as a .csv
Saving the file with the extension .csv decreases the file size so it's easier to format and import into MindBridge.
Remove empty rows
Rename the headers to align with the following MindBridge headers. This will help speed up the import process.
- Account ID
- Account Name
- Posted Date
- Memo
- Transaction ID
- Transaction Type (if you have it)
- Amount (or Debt and Credit)
- Source (if you have it)
- User ID (if you have it)
Fill down on account numbers and names (grouped files)
- Select the column(s) you want to fill down.
- Open the Editing tab in the Home ribbon.
- Open the Find & Select menu.
- Select Go To Special...
- Select Blanks.
- Select Ok.
This selection highlights the empty cells and makes the first empty cell white.
- Press F2 on your keyboard to enter values into the white cell.
- When the type indicator blinks, enter = then the cell above (for example, "=K1").
- Press Ctrl+Enter on your keyboard.
All of your cells should now be filled. - Copy and paste the values in that cell to remove the formula.
Concatenating files
If the file is within the limits of Excel, simply copy and paste the files together without the headers from the second file into one sheet.
If the file exceeds the limits of Excel, learn how to merge multiple files onto one sheet within MindBridge.
Additional considerations
When formatting your data, pay attention to these key considerations:
- Trial Balance files should always contain unique accounts, as redundant accounts will cause errors. Learn how to resolve this issue.
- It is always recommended to import an "ungrouped" file format into MindBridge. Learn about grouped versus ungrouped file formats.
- If you want to filter your analysis by the values found in a particular column, add that column during column mapping. Learn how to add a column for filtering.
Anything else on your mind? Chat with us or submit a request for further assistance.