Import the client's transactions from a CSV
If you're using desktop or online third-party accounting software, you typically get the option to choose to import transactions data as you import the trial balance data.
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.
Go to the Import tab in the Data page and select Import Transactions CSV.
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.
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. To learn more, see Prepare the transactions CSV file.
Based on the data analytics feature enabled in your product, you may need to include specific data in the CSV file to ensure the feature functions correctly. To learn more, see Mandatory fields for importing transactions.
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)
Step 3: Assign data fields
You must assign the source fields in the CSV file to predefined target fields that match the data, so they properly display in the Transactions tab in the engagement.
The figure below explains what data assignment means. When the source field in the CSV file "nr" is assigned to "Entry Number", the values of the column will display in the Entry Number column in the Transactions tab. Similarly, the values for "cw_js" will display in the Document Type column, and so on.
To assign the source fields in the CSV file:
Select ASSIGN DATA 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 assign each source field in the CSV.
The main categories and their respective fields display in the drop-downs.
For field descriptions, see:
- Transactions details - for fields within the TRANSACTION DETAILS category.
- Account details - for fields within the ACCOUNT DETAILS category.
- Additional details - for fields within the ADDITIONAL DETAILS category.
- Subledger details - for fields within the SUBLEDGER DETAILS category.
- Process details - for fields within the PROCESS DETAILS category.
Tip: Move your cursor to a specific field to view a description of it to help you assign 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 displays at that field.
Select the MAP COLUMN VALUES drop-down.
Specify your selections, then click outside of the drop-down menu when 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 complete the import.
Tip: You can restart the mapping process if needed using the CHANGE DATA FIELDS button on the Import Transactions page.
To view the account transactions you've just imported, open 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 field assignment you've just completed. This allows you to reuse the structure you've initially defined in subsequent transaction reimports.
To save a DATA LAYOUT:
- From the Import Transactions page, select SAVE from the DATA LAYOUT column.
Enter a Data Layout Name in the Save Data Layout dialog, for example, Standard Import.
The DATA LAYOUT name is updated on the Import Transactions page.
Now, when you select ASSIGN DATA FIELDS or CHANGE DATA FIELDS, a drop-down containing available layouts displays.
To select a different DATA LAYOUT, select the drop-down and select the desired data layout from the list that displays.
To delete a 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.
Delete imported transactions
Users can delete transactions from an engagement after they have been imported.
To delete imported transactions:
From the Data page, select the Import tab.
Select Import Transactions CSV.
The Import Transactions page displays. Select REMOVE IMPORTED DATA.
A prompt to remove transactions that have been imported displays. Select Continue.
Transaction removal is processed and users are no longer able to access the removed transactions within the engagement.
Change data sources after import
Users that have imported from an online or desktop accounting package can change data sources after an import failure or success.
To change data sources to a different Accounting Software or Structured File data source after import:
From the Data page select the Import tab.
Select Accounting Software or Structured File.
Select CHANGE DATA SOURCE.
A prompt to change the data source displays. Select Continue.
Choose the new data source and select NEXT.
Select CONNECT to continue through the import process.
To change data sources from a non-CSV source to a CSV source after import:
From the Data page select the Import tab.
Select Re-import from a different source.
Choose Excel or CSV and select NEXT.