Summary
This article will show the data formatting tricks that can be used to format your file for the ingestion process.
Date format
MindBridge is good at translating all kinds of date formats; as long as Excel recognized 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's date.
The date format should be grouped as appears in the image below.
Re-format the date
- Select the Text to Columns feature in Excel, found in the Data tab.
- Select Delimited then select Next.
- Use the Custom Delimiter (step 2 of 3) and choose "/" in the Other: text field.
Note: When using the Custom Delimiter, be sure to insert the appropriate amount of new rows after the original date column. These new columns will be used to delimit the date.
Alternatively, use the Date delimiter (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 to in 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.
Here is a before and after image displaying preferred date format of YYYY-MM-DD.
Format amount or debit and credit values
The amount or debit and credit columns should not contain any special characters. The easiest way to ensure this is to format the columns to a 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 type from an account number
- or-
Separate the account description from account number
- Insert a column after the column you are working with and re-name it.
- 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
If you want to speed up the data formatting and ingestion process, there are a few things you can do.
- Save the file as a .CSV file. This decreases the file size, making it easier to format and import into MindBridge.
- Remove empty rows.
- Rename the headers to align with the following MindBridge headers. This which 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.
Concatenation of Files
If the file is within the limits of Excel, simply copy and paste the files together without the headers from the second file.
If the file exceeds the limits of Excel, learn how to merge multiple files onto one sheet within MindBridge.
Anything else on your mind?
Head over to the community with your thoughts and inquiries! You can also chat with us, or, submit a request for further assistance.
Comments
0 comments
Please sign in to leave a comment.