Import the client's transactions from a CSV or Excel file
If you’re importing the client’s transactional data from a CSV file, you must import the trial balance data before you import the transactions. To learn more, see Import TB from CSV/Excel and Import TB from AuditXML .
Import from a CSV file
Go to the Import tab in the Data page and select Import Transactions CSV.
You can also import a CSV from the Import Transactions CSV document on the Document page.
To successfully import transactions from a CSV file:
- Prepare the CSV file.
- Import the CSV file.
- Map the fields.
- Save the data layout.
- Optionally, import additional transactions CSV files.
Step 1: Prepare the CSV file
Make sure the transactions CSV file is valid. The validity requirements display in the page once you select Import Transactions CSV.
Step 2: Import the CSV file
Drag and drop the file or browse.
Select the desired field Delimiter type.
- Comma (,)
- Tab (\t)
- Vertical Bar (|)
- Semicolon (;)
- Record Separator (\u001e)
- Unit Separator (\u001f)
Upon a successful file import, a confirmation message displays at the bottom of the page.
Step 3: Map the fields
To complete the transactions import process, you need to map the source fields in the CSV file to target fields that match the data.
In some cases, when you map a field, the MAP COLUMN VALUES drop-down becomes available for the field because you need to map the values in this field as well. For example, the DOCUMENT TYPE field. Once you select this field as the target field, you must also select the MAP COLUMN VALUES drop-down to specify target values for each source value.
To map the source fields in the CSV file to desired target fields:
The page splits into two sections. The upper section shows the source fields from the CSV and the lower section shows the data in each field.
Use the available drop-downs to select a target field to map each source field in the CSV.
The main categories and their subsequent fields display in the drop-downs.
TIP: Move your cursor to a specific field to view a description of it to help you map the values correctly. You can also find specific target fields using the search field at the top of the drop-down menu.
If value mapping is required for a specific field, the MAP COLUMN VALUES drop-down is available at that field.
Select the MAP COLUMN VALUES drop-down.
Specify your selections, then collapse the drop-down menu once you’re done.
The following figure shows an example of field value mapping for DOCUMENT TYPE. Note that DOCUMENT TYPE has been mapped to the cw_js field in the source CSV file. In the process of value mapping, the target value General Ledger Entries (GJ) is mapped to GJ from the CSV file.
Once you finish the mapping process, select the Import button () to finalize the transactions import process.
Tip: You can restart the mapping process if needed using the REMAP button on the Import Transactions page.
Close the Import Transactions page and go back to your engagement file.
To view the account transactions you've just imported, go back to the Data page and select the Transactions tab. To learn more, see View the client’s transactions.
Step 4: Save data layouts
You can save the layout of the data mapping you've just completed. This allows you to reuse the mapping structure you've initially defined in subsequent reimports of transactions.
To save a DATA LAYOUT:
- Select SAVE DATA LAYOUT from the Import Transactions page.
This option is only available for mapped transaction data files.
Enter a Data Layout Name in the Save Data Layout dialog, for example, Standard Import Layout.
The DATA LAYOUT name is updated on the Import Transactions page.
Now, when you select MAP or REMAP, a drop-down containing available layouts displays.
To select a different DATA LAYOUT, select the drop-down and elect the desired data layout from the list that displays.
To delete a different DATA LAYOUT,
- From the SELECT DATA LAYOUT drop-down, select the delete icon beside the desired Data Layout.
A layout can only be deleted by the person who created it.