Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

To successfully import 3rd party transactions, the file containing the exported transactions need to follow the default import format. Below are the default template specifications for the contents of the import file. The file needs to match these specifications to be able to import the transactions. These are normally in CSV file format, which you can create using Microsoft Excel.

The column names in your CSV file must be in the first line of your CSV file.

Some ledger fields are mandatory and some are optional, as shown in the following table.

AccountType
TaxCode
TransactionReferenceId
TransactionDescription
TransactionDate
Optional (mandatory if TransactionAmount is mapped)
The following formats are both valid:
  • dd/mm/yyyy
  • dd-mm-yyyy
Optional
Note: The file should be balanced:
Sum of Amount = $0
Sum of Amount for each unique Date = $0
No special characters are allowed ($ or ',')
Signage is allowed (+, -)
HTML Wrap
classsection group
<span dataswiftype-index="true">
HTML
-
html-wrap
classcol span_3_of_5

UI Text Box
typenote

This feature is not enabled for all users. If this option isn’t available in the Tasks bar, please contact your client manager to enable this feature for your practice.

In order to import a client file, make sure that the
HTML
<span data-swiftype-index="true">

Transactions can be imported into Client Accounting using the Manage client file imports wizard. You can find this function on the Tasks bar of your client's Client Accounting > Trial Balance (or Workpapers) tab.

Image Added

You can only import transactions via Client Accounting if your configured ledger is an MYOB Ledger or an AE/AO Ledger. If you need to use ledger data that was prepared using a ledger that cannot be directly connected to're using something different, you can load the ledger data by importing an MYE, TXT or CSV file.

An MYE or TXT file contains details about the client’s ledger being imported, and chart of accounts and journal transactions. A CSV file contains either chart of accounts, or chart of accounts and transactions.

UI Expand
titleHow to import an MYE client file
UI Text Box
typenote

- When you import an MYE file, the system identifies any accounts that need to be added to the chart of accounts, and adds all the journal transactions as journals to the ledger.
- If your MYE file exceeds the system limit for journal entries (16,000), an error message will display. To address this issue, decrease the number of transactions and consolidate prior period data.

  • Open the client’s Client Accounting > Trial Balance tab (called the Workpapers tab if you are licensed for Workpapers).
  • Click Ledger > Import client file on the Tasks bar.
  • In the Import Wizard window, Select the .MYE option under Choose file type.
  • In the Select file to import field, browse for the .MYE file to import.
  • Click Next. A message displays with details of the client’s name, start date and end date.
  • If the details are correct, click Yes. The system validates the file format.
  • Continue to Selecting account mapping options.

    still use the import functions in your configured ledger's software.

    The Manage client file imports wizard accepts the following file types:

    About the Manage Client File Imports wizard

    The Manage client file imports wizard is where you'll import journal transactions from your client's file into an MYOB ledger. In this window, you'll map your the client's accounts to its equivalent account in your ledger.

    There are two additional functions in this window:

    • The View/edit mapping template link lets you view and modify the mapping template for the most recently imported client accounts.
    • The Delete previous import link will delete all transactions and journal entries posted by the import wizard for the previously imported client file (i.e. undo the import).
    HTML
    <h2><i class="fa fa-comments"></i>&nbsp;&nbsp;Transaction Import FAQs</h2>
    UI Expand
    titleHow to import a TXT client file
    UI Text Box
    typenote

    When you import a TXT file, the system identifies any accounts that need to be added to the chart of accounts, and adds all the journal transactions as journals to the ledger.

    1. Open the client’s Client Accounting > Trial Balance tab (called the Workpapers tab if you are licensed for Workpapers).
    2. Click Ledger > Import client file on the Tasks bar.
    3. In the Import Wizard window, Select the .TXT option under Choose file type.
    4. In the Select file to import field, browse for the .TXT file to import.
    5. Click Next. A message displays with details of the client’s name, start date and end date.
    6. If the details are correct, click Yes. The system validates the file format.

    7. Continue to Selecting account mapping options.

    UI Expand
    titleHow to import a CSV client file
    UI Text Box
    typenote

    - Importing of CSV files is controlled by an XML configuration file, located in the Deploy folder. This file controls the mapping between columns in your CSV file and ledger fields. Additional information can be found in the XML file.
    - CSV files containing up to 38,000 journal lines are supported. To import a CSV file with more than 38,000 journal lines, separate the CSV file into multiple files, ensuring that each file does not exceed the journal line limit, and perform a separate import for each file. You can also try decreasing the number of transactions and consolidating prior period data.

    1. Open the client’s Client Accounting > Trial Balance tab (called the Workpapers tab if you are licensed for Workpapers).

    2. Click Ledger > Import client file on the Tasks bar.

    3. In the Import Wizard window, Select the .CSV option under Choose file type.

    4. In the Select file to import field, browse for the .CSV file to import.

    5. Select the type of mapping to use from the Select template dropdown.

      UI Text Box
      typenote

      - Choose the template that matches the client’s source file. If you’re not sure which one to select, contact your IT or MYOB Support
      - The default options are Default Template, Quickbooks 2014 Template and Quickbooks online Template. If there are any other options listed, that means they have been customised for your site.

    6. Click Next. The system validates the file format.

    7. Continue to Selecting account mapping options.

    UI Expand
    titleUsing the default MYOB template
    Column name    Maps toMandatory/optional
    account codeAccountCodeMandatory
    account nameAccountNameOptional
    account type

    Optional

    Note: The following values are valid:
    Income, Other Income, Liability, Equity, Expense, Other Expense, Assets, Cost of Sales, Uncategorised

    account type groupAccountTypeGroupOptional
    tax codeOptional
    The valid code values (and their definitions) are:
    • NA (No tax implied)
    • (Australia) CAP (Capital acquisitions)
    • (Australia) EXP (Export sales)
    • (Australia) FRE (GST free)
    • GST (Goods & Services Tax)
    • (New Zealand) E (Exempt)
    • (Australia) GNR (Not registered)
    • (Australia) INP (Input taxed purchases)
    • (Australia) ITS (Input taxed sales)
    • NTR (Not reportable).
    • (New Zealand) Z (Zero rate)
    • (New Zealand) I (GST on imported supplies).
    If no value is entered, the default value of NA is used.
    transaction referenceOptionaltransaction descriptionOptionaltransaction datetransaction amountTransactionAmount
    UI Expand
    titleUsing the QuickBooks Transaction detail template

    The provided QuickBooks Transaction detail template works with the following versions of QuickBooks (Windows and Mac):

    • QuickBooks 2016
    • QuickBooks 2015
    • QuickBooks 2014
    UI Text Box
    typenote

    We only support a single amount field, and not separate debit and credit fields.

    UI Expand
    titleGenerating a CSV file from QuickBooks

    In QuickBooks, perform the following:

    1. Select Edit > Preferences from the menu bar.
      The Preferences window displays.
    2. Select Accounting in the left-hand toolbar, and then select the Company Preferences tab.
    3. Make sure that the Use account numbers option is ticked (and the Show lowest subaccount only option, if you are using subaccounts), and click OK.
    4. Windows: Select Reports > Accountant & Taxes > Transaction List by Date from the menu bar.
      Mac: Select Reports > Accountant & Taxes > Transaction Detail by Date from the menu bar.
      The Transaction List by Date report will display in the main window.
    5. Click Hide header in the toolbar at the top of the main window.
    6. Select the range of dates and the sort order from the toolbar.

      UI Text Box
      typenote

      If you wish to use the Account Type and Tax Code fields in your template, you will need to add them manually.
      Click Options in the toolbar at the top of the main window, and then select the Account Type and Sales Tax Code columns.

    7. Windows: Select Excel > Create new worksheet in the toolbar.
      Mac: Click Export in the toolbar.
      The Send Report to Excel window displays.
    8. Select the Create a comma separated values (.csv) file option, and click Export.
      The Create Disk File window displays.
    9. Type a file name and choose the location to save your .CSV file.
    10. Click Save.
      You can now import the CSV file into AE/AO Ledger.
    UI Expand
    titleImporting transactions from Quickbooks Online into AE/AO Ledger

    In order to meet the specifications for this file to be imported, the CSV file exported from QuickBooks Online needs to be modified and the file MyobCsvMaps-System.xml updated to create a new mapping template.

    Note

    For details on how to import transaction into AE/AO Ledger using a Default MYOB template or a QuickBooks Transaction detail template (Quickbooks version 2014, 2015 and 2016), refer to KB 37963: How do I comply with the specifications for importing transactions exported from a 3rd party product in AE/AO Ledger?

    First, we have to create the CSV file:

  • Log into your QuickBooks Online client and open the Intuit QuickBooks Home page.
  • Ensure the client's QuickBooks Online file has the 'enable account codes' option ticked in the configuration.
  • From the navigation bar click Reports.
  • In All Reports under Accountant Reports, produce the Journal Report in Excel and click "Customize report". Uncheck the option to include all the Headers.
    Image Removed
  • In Excel add a new column called Amount, then populate this with the data from the Debit-Credit columns.
  • Populate the Date column for each row with data in it. All records having non zero Amount and Account must have have a Date.

    Info

    Excel has a function that can be used to get data populated correctly for the non-contiguous rows, such as the Date that is missing from some of the rows in the file.

  • Select the column range that needs to be filled for non-contiguous range of blank cells (for example B3:B2000).
  • Press F5 to show the Goto dialog.
  • Click Special then select Blanks and click OK.
  • With the non-contiguous range of blank cells selected, without selecting anything else, type = and the cell that is the start of the range and press Ctrl + Enter. In the example type =B3 here.
    Now the Excel file is ready to be converted and saved as a CSV.
  • Next, we'll need to update the MyobCsvMaps.xml file. The existing options for importing a CSV file Default MYOB Template or the QuickBooks 2014 Template will not support this File.  Therefore, you will need to add new Template in the MyobCsvMaps.xml file located in the Deploy Folder:

    1. In the Deploy folder, rename the existing MyobCsvMaps-System.xml to MyobCsvMaps-System.xml.old
      The location of the Deploy folder can be found at \\SERVER\CENTRAL or \\SERVER\MYOB\MYOBAE\AESQL\Central\Deploy (where SERVER is your server name).
    2. Save the MyobCsvMaps-System file pinned to this page to your Deploy folder.
      Note that this will overwrite any previous modifications that were made to the MyobCsvMaps.xml file.

    Finally, we'll import the CSV file:

  • Run the Import Client File wizard. See "How to import a client file" above.
  • In the Select Templatedrop-down step, select "QBOL".
  • Continue with the remaining steps in "How to import a client file".
  •  

     

    do I import data from another accounting software?

    If your client's accounting software has the option to export transactions in an MYE format, choose this option. This is an MYOB file type and requires no additional formatting.

    If you don't have this option, choose a .CSV or Excel file type. You may need to customise the exported file to match the format MYOB Ledger requires.

    If you're using...Choose file type...
    Xero.MYE
    QuickBooks Online.MYE
    Reckon.CSV

    If you're not sure how to export data from a 3rd party accounting software, check the product help for that software.

    UI Expand
    titleHow do I export transactions from a AccountRight, AccountEdge or Essentials?

    For information on how to export transactions from your client's file, click on the link in the Refer to column for the relevant product.

    ProductRefer to..
    AccountRightProvide information to your accountant
    AccountRight Classic (v19 or earler)Exporting information for use with AE/AO
    AccountEdgeProvide information to your accountant
    Essentials Accounting (including Connected Ledger)Export for accountants
    UI Expand
    titleHow do I import transactions if I'm not using an MYOB ledger?

    If your configured ledger is not an MYOB Ledger, you can still import transactions from within your configured ledger's software.

    For example, if you've configured a MAS ledger, import your data using the import function within the MAS ledger.

    The table below lists articles containing information on how to import transactions into your product.

    UI Text Box

    Journal transactions can't be imported into MYOB Essentials. For information on what you can import into Essentials, see Importing and exporting in MYOB Essentials.

    UI Expand
    titleWhat data can I import into MYOB Ledger?

    You can import:

    ItemMYECSVTXTQIFMore information
    Chart of Accounts

     

    Image Added

      Importing a chart of accounts into MYOB Ledger


    Journal transactions


    Image Added


    Image Added


    Image Added

     

    Importing an .MYE or .TXT file

    Importing a .CSV file

    Bank Statements 

     

     

    Image Added

    Importing a Bank Statement into MYOB Ledger
    HTML
    </span>
    HTML Wrap
    width15%
    classcol span_1_of_5
     
    HTML Wrap
    floatleft
    classcol span_1_of_5
    Panelbox
    namegreen
    titleRelated topics
    HTML Wrap
    classsidebarlinks

    Selecting account mapping options 

    Selecting account mapping

    Previewing mapped accounts

     

     

    Mapping imported accounts

    Importing an .MYE or .TXT file

    Importing a .CSV file

    Importing a Chart of Accounts

    Importing a Bank Statement into MYOB Ledger

    Using the QuickBooks 2014 template

    Importing transactions from QuickBooks Online