Questions? We have answers.

Summary

Learn about the process of transforming for import into MindBridge a general ledger file exported from NetSuite.

Notes/Prerequisites

This guide assumes that you have exported a CSV file from NetSuite in the following format, as detailed in the NetSuite: Export the general ledger guide.

Transform the data

The problem

The Account column contains the Account ID, followed by a space, followed by Account Description (for example, 1000 Checking). The Account ID is a required field in MindBridge, while the Account Description is an optional field. Hence, the Account column must be separated into two columns with Account ID and Account Description.

The solution

Split Account ID and Account Description into different columns. This can be done by:

1. Using MindBridge's split columns feature during column mapping (this is the preferred approach)
   • You can split columns in MindBridge by position if all Account IDs are of the same length, or split on the space character if they are not; or
2. Formatting using the "Option 2" steps:

Option 2: Instructions/formulas for formatting in Excel

• Add an Account ID column (in column K in the example below). Input the following formula:

=LEFT(D2,SEARCH(" ",D2)-1)

• SEARCH(" ",D2) finds the position of the space character in cell D2 (Account). We then subtract 1 from that position to get the number of characters of the Account ID (4 characters for Account ID "1000")

  • LEFT(D2,) extracts the leftmost number of characters from that account string. In this case, the first 4 characters in cell D2, "1000"

• Add an Account Description column in column L.

=MID(D2,SEARCH(" ",D2)+1,LEN(D2))

• SEARCH(" ",D2) finds the position of the space character in D2 (Account) similar to the formula above for Account ID. We then add 1 from that position to get the starting position of the Account Description("C" in D2)
  • MID() extracts the Account Description text from the middle of the field (D2) by starting at the position of SEARCH(" ",D2)+1 for a specified number of characters determined by LEN(D2)

For more information

• Ungroup and split: Extract complex patterns — consult this guide to split out complex account patterns.