How to import the trial balance from MS Excel to CaseWare?

1.    Indirect Import 

1.1.    Importing from Microsoft Excel 

• Your PC will require Microsoft Excel 2003 or later 
• Click on Export and save the trial balance as an Excel spreadsheet. 
• From the Caseware Menu, select File | Import | Excel File… 

• The Excel File Import wizard opens. 

• Select the following “Import File” Settings. The following components will be imported:
  • Chart of Accounts & General Ledger Balances:  Select this option to import the Trial Balance. 
  • General Ledger Details:  Select this option if the spreadsheet contains transactional detail. 
  • EXCEL file to import: Click on the browse button to navigate to where the file is stored. 
  • Select a worksheet:  Enter the number of the Excel worksheet where the data is saved (counting tabs from the left). 
  • Record layout file (optional):  This field is normally left blank. A previously saved layout can be selected.  Click on the browse button to navigate to where the layout file is saved (.vtb file).

• Period Date Sequence for importing period balances: The period date sequence defaults to the current date sequence set up in the Engagement Properties. 

• ​Click on the “Next” button to specify the data properties

• Field Separator: For Excel imports, this field is set to Tab and cannot be changed. 
• Text Qualifier: This can usually be ignored. If the data file contains attributes such as percentage signs, currency symbols, or quotation marks, select it here to tell Caseware Working Papers to ignore it during the import. 
• Exclude First ? Rows: If the data has header information that should not be imported, indicate the number of rows that should be omitted from the import. As the number of rows increases, they will be greyed out in the preview box below. 
• Exclude Last ? Rows:  If the data has totals or footer information that should not be imported, indicate the number of rows that should be omitted from the import.

• Click on the “Next” button to enter Field Details.
  • The checkboxes on the left can also be used, especially for subtotal lines in the middle of the data – check the lines that should be excluded.

• The Field Details dialogue will open.  This is where the properties of the data columns are defined. 

• The column headers are labelled “Ignore” by default. They will change to the header selected once defined.
• All columns with the header “Ignore” will not be imported.  Only the data to be imported should be defined. 
• Define the data columns by clicking on the heading and selecting the appropriate category.  The essential fields to define are: 
• Account Number: Account Details > Account Number. 
• Account Description:  Account Details > Account Description. 
• Current Year Account Balances: Balances > Current Year Balance > (select appropriate period, for example, period 12 if monthly). 
• Prior year account balances (for new CaseWare files only):   Balances > Prior Year 1 Balance > (select appropriate period). 

• Once all the columns have been identified, click on the “Next” the button to set advanced settings.

• Default Account Type: Income Statement (unmapped accounts will be allocated the default type Income Statement when imported – this is updated as soon as the account has been mapped).
• Accumulate Balances:  Check this box to ADD the balances being imported to balances already in Working Papers. 
• Clear Balances of Period(s) Being Imported:  Check this box if when reimporting an updated trial balance to ensure that the old balances are deleted. 
• Round Balances:  The option to round balances being imported to the nearest ones, thousands or millions is available.  This will eliminate rounding differences in the financial statements due to cents.  Check the box if to round the balances being imported. 

• Click on the “Settings” button to specify import rounding settings. 
• The Import Rounding Settings dialogue will open. 

• Round to: From the drop-down box, select the rounding level. 
• Rounding differences for:  From the drop-down boxes, select the account to post any rounding differences to for the balance sheet and income statement. 

• Click on the “OK” button to save the settings.
• Import:  Defaults to all accounts. To import accounts with balances, change selection to Accounts with Non-Zero Total Balance. Accounts with Opening Balance/Period Movement. 
• Regional settings: If the default regional settings should not be used, uncheck the box to overwrite the default symbols. 
• Balance settings: Do not change any balance settings (default).

• Click on the “Next” button to proceed to the last step – The Completing Excel wizard will appear

• Save Layout Settings:  This option allows the user to save the layout and settings (file type, column selection and assignments, etc.) that they have just specified to enable them to be reused for another input file, either for the same CaseWare file or for a different CaseWare file with the same file layout, without having to re-enter all the settings again. 
• Click on the “Browse” button to navigate to where the layout file should be saved. 
• Click on the “Import” button to complete the import. The Working Trial Balance will be updated with all accounts and balances imported. 

1.2.    Advanced importing – divisional

• Method 1: Cost/divisional code exists in a separate field as the account number  
• Method 2: The account name and the Cost/divisional code share the same field  

1.2.1.    Method 1

• In CaseWare, Go to menu File | Import | Excel File. 
• The Excel File Import wizard will appear: 

• Complete the following fields:
• Components to import: Select ‘Chart of Accounts and General Ledger Balances’. 
• EXCEL file to import: Type or select the exact path where the file resides or click ‘Browse’ to locate the file.  
• Select a worksheet: Allows the user to enter the Worksheet number of the TB if not on Worksheet 1 in the Excel document. 
• Period Date Sequence for importing period balances: as setup in the Engagement properties.

• Click on the “Next” button to continue.  

• Use the Exclude First row and Exclude last rows fields to make outlines that the “Specify Data Properties” screen will appear.
• Should not be imported into CaseWare Working Papers. Alternatively, the user can also use the tick boxes to the left of each row to select items that should be excluded.  
• Click the “Next” to continue.      
• In the ‘Field Details’ details screen, assign the columns as indicated below: 

• Entity Description: Assign the field to the column that represents the Division description if applicable.  
• Entity Abbreviation: Assign the column representing the cost/divisional code. This abbreviation has to be the same as the abbreviation created in the 
• Engagement Consolidate screen: Refer to Setting Up the Organisational Structure for more information on adding the entity abbreviation.   Assign the rest of the columns:  
• Account Description: Assign the account description by clicking in the relevant column and selecting Account Details | Account Description.     
• To assign current year balances: Click on the relevant column, select Balances | Current year | Period 1 – 12. If only the final at year – end balances are being imported, only Period 12 needs to be assigned.  
• Prior Year balances can be imported in the same way by selecting Balances | Prior year (1 – 4) | Period 1 – 12.  

• The ‘Assign Entity’ screen appears. The entity structure appears on the right hand side of the screen. All accounts have been assigned to the relevant entity.

• Click on the “Next” button to continue.  
• In the ‘Advance Specifications’ screen are the options for the account, regional settings and file settings presented clearly for users to set as needed. 

• Click on the “Next” button to continue.  
• The Completing Excel Import Wizard appears: This screen informs the user about whether he/she has filled out the settings for the import correctly. If they are correct, click the Import button to run the process. 
• If the settings are not complete, use the “Back” button to return to the screen needing further information and then return to this completion screen to complete the import.  

• Save Layout Setting - The user can save the layout and settings used for this import in order to reuse it for another file sharing the same layout options.

• Click on the “Import” button to continue the import process  

  1.2.2.    Method 2 

• Setup the divisional structure in the CaseWare file before importing. Refer to Setting Up the Organisational Structure for more information how to setup the divisional structure.  
• Follow the same import process as mentioned earlier.  
• When completing the field details, select the following additional options.  

• Entity Position: Select the tick box ‘Account Number and Entity share the same field but are not separated’. The ‘Entity Start Position’ should be the position where the entity code starts for e.g. the position on the screen shot shown above would be 1, as the entity code is numbered first. Then enter the ‘Number of Characters’ that the entity code is, for e.g. the number of characters on the screen shot below is 3 as the entity code is the first 3 characters 
• Assign the rest of the columns as indicated on pg.Method 1 20. And complete the import. 

1.3.    Troubleshooting Import Failure 

• Before importing; ensure that the trial balance being imported is in balance. 
• Once the import process has been completed, check that the import has succeeded by testing whether the trial balance balances: 
• On the Navigation toolbar, click on the “Trial Balance” button to access the Working Trial Balance. 
• At the bottom of the Working Trial balance, scroll across to find the Final column for the current year. 
• At the bottom of the Working Trial balance – check that the total for each column is zero (that means it is in balance). 
• Alternatively, the user can browse to the Final Trial Balance (01.30) located in the Document Manager and ensure that the final balance is zero. 

1.4.    What if the total is not zero? 

• Check the original Excel trial balance: 
• Is there any duplicate account numbers? 
• Does the trial balance balance? 
• For reimports, see below. 
• Was the correct version of the accounting software imported from selected? 

1.5.    Re-importing Data 

• Should the import be unsuccessful; the trial balance can be re-imported at any time.  Amend any errors that caused the import to fail and go through the import process again exactly as before.  
• Previously imported balances will be overwritten with new balances. 

  Instructions:

   Related Articles