Import the client's data from a CSV or Excel file

If the accounting software package you have is not supported by any of our import utilities, you can export your client data to a CSV (comma-separated value) or Excel file and then import it using this file. The format of the file can be either .xlsx or .csv and must meet the file requirements for successful import. To learn more, see  Prepare a CSV or Excel file to import transactions or subledger data and Date format.

After you upload the file, you need to assign the data fields in the file to predefined target fields in a Casware dataset, to complete the import. To learn more about the dataset options, see Dataset types described. Not all dataset types may be available if they have not been enabled in your product.

Date format

In fields where a date is required, date format must be properly set:

Day

D: One digit for days below 10 (for example, 2 for the 2nd day of the month)

DD: Two digits (for example, 02 for the 2nd day of the month)

DDD: Day of the year (for example, 365 for December 31st)

DDDD: Three-digit day of the year (for example, 002 for January 2nd)

Month

M: One digit for months below 10 (for example, 4 for April)

MM: Two digits (for example, 04 for April)

MMM: Three-letter abbreviation (for example, Apr for April)

MMMM: Full month name (for example, April)

Year

YY: Two digits (for example, 19 for 2019)

YYYY: Four digits (for example, 2019)

Time

H HH: Hours in 24-hour time (for example, 0 for midnight, 23 for 11 PM)

h hh: Hours in 12-hour time used with AM/PM (for example, 1 for 1 AM, 12 for noon)

k kk: Hours in 24-hour time from 1 to 24 (for example, 1 for 1 AM, 24 for midnight)

a A: Post or ante meridiem (for example, am for morning, pm for afternoon)

m mm: Minutes (for example, 0 for the top of the hour, 59 for the last minute of the hour)

s ss: Seconds (for example, 0 for the start of the minute, 59 for the last second of the minute)

S SS SSS ... SSSSSSSSS: Fractional seconds (for example, 0 for no fractional second, 999999999 for the last fractional second)

Z ZZ: Offset from UTC as +-HH:mm, +-HHmm, or Z (for example, +12:00 for 12 hours ahead of UTC)

Example Transaction

Let's say we have a transaction that occurred on April 2, 2019, at 1:30:45 PM with 123 milliseconds, and the UTC offset is +02:00. Here’s how it would look in different formats:

• Date: 2/4/19 or 02/04/2019

• Time: 13:30:45.123 +02:00

Import client data

To import client data from an Excel or CSV file:

1. Go to the Data page. The Data page opens to the Datasets tab.

• If you’ve previously imported data, select Add data.

• If your engagement is consolidated and has internal subsidiary entities, select the entity for which you want to import data from the list.

2. UNDER DATA FILE UPLOAD, browse or drag the CSV or Excel file to the file upload area.

3. In the Configure new dataset dialog, select the Dataset type from the dropdown.

   To learn more about the dataset options, see Dataset types described. Not all dataset types may be available if they have not been enabled in your product.

   If you are importing a trial balance, in Dataset type, select Trial balance. If planning balances are enabled in your product, select whether you are importing the Final trial balance or a Planning trial balance.

4. Review the automatically selected header row to ensure the correct header has been selected. If you want to manually select the header row, select Adjust header row and select the correct row.

   If you are importing a CSV file and want to change the delimiter to something other than the default comma (,), select Adjust header row and select the correct delimiter.

   Note: Both the automatic header detection and manual header selection only take into account the first 30 rows of the imported file. Rows beyond the first 30 rows cannot be selected as a header.

5. Select Upload.

6. On the Datasets tab, click Configure to assign columns in the imported data to Caseware fields.

   

7. On the Assign Fields page,

   • Use the dropdowns to manually assign a field to each column in the imported file.

   • Use the Select data layout dropdown to apply a previously saved data layout and automatically assign the fields. To learn more, see Save a data layout.

If there are any errors, an indicator displays for the columns that contain the errors. You can then select the indicator and the error to view more information about the error and the specific rows that have been flagged.

8. If you are importing a trial balance, select Include zero-balance accounts if you want to include accounts with a balance of zero in the import.

9. Select Import.

To view the imported data, click View data next to the dataset you imported.

 

If you imported a trial balance, the Accounts tab appears. If general ledger or any of the subledgers, a new page appears displaying the dataset.

By default, all the columns in your dataset display. Click Columns to deselect those you want to hide.

Click Filters to filter the data displayed in the dataset.

See View the client's general and subledger data, for more information.

Reimport the trial balance

After you complete the import, you can reimport the trial balance data at any point in time or import a new trial balance:

When you reimport data,

• Account names and balances will be overwritten with the reimported data for accounts that already exist in the engagement

• Any new accounts in the reimported data that do not already exist in the engagement will be added to the engagement.

• Accounts that exist in the engagement that are not included in the reimported data will be deleted, unless:

  • The existing account has a balance for a period, balance type or other entity that is not included in the reimported data. For example, if the existing account has a prior year balance but the reimported data only includes data for current year balances, the existing account will not be deleted.

  • The existing account has a corresponding adjustment, issue or annotation.

• Any adjustments, issues and annotations made prior to reimport will remain after the data is reimported.

Note: If you reimport the data from a different source (from a different file name, for example) and there are accounts in the existing data that are not in the new data, the balance of these existing accounts is set to 0.

Note: In engagement files created from carry forward, account balances from prior years are not overwritten or deleted by the new data from reimport.

To reimport the trial balance:

1. Reassign the fields in the trial balance. Select Options () | Reassign fields on the Datasets tab on the Data page.

2. In the dropdowns, make any necessary changes in how fields in the data file are assigned to Caseware fields. Select Reimport.

Import a new trial balance

In an engagement where you have already imported a trial balance, you can import a new trial balance. Delete the existing trial balance and then proceed to import the new trial balance.

To import a new trial balance:

1. Delete the existing trial balance. Select Options () | Delete data on the Datasets page.

2. Proceed to import the new trial balance. Under DATA FILE UPLOAD, browse or drag the CSV or Excel file to the file upload area.