| HTML |
|---|
<span data-swiftype-index="true"> |
| HTML Wrap |
|---|
|
In order to import a client file, make sure that the 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, 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. There are also two links on this screen: ui-expand title How to import Importing an MYE client file| Excerpt |
|---|
| UI Text Box |
|---|
| | When you import an MYE file, the system identifies any or TXT fileAn MYE or TXT file contains details about the client’s ledger being imported, chart of accounts and journal transactions.
When you import these files, Client Accounting identifies accounts that need to be added to the chart of accounts, and adds all the journal transactions as journals to the ledger. If your | UI Text Box |
|---|
| To import an MYE file that exceeds the system limit for journal entries ( 16,000 ), an error message will display. To address this issue, journal entries, decrease the number of transactions and consolidate prior-period data.Open the client’s | | UI Expand |
|---|
| title | How to import a TXT client file |
|---|
| | UI Text Box |
|---|
| 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. |
- Open the client’s Client Accounting > Trial Balance or Workpapers tab.
- From the TASKS bar, click Manage client file imports. The Manage Client File Imports window appears.
 Image Removed - Under Choose file type, select .TXT.
- In the Select file to import field, click the ellipses button (
 Image Removed) and browse to the location of the .TXT file you want to import. - Select the .TXT file and click Open. The location and name of the import file displays in the Select file to import field.
- Click Next. A message displays with details of the client’s name, start date and end date.
- Click Yes. The system validates the file format.
Continue to Select account mapping options. |
| UI Expand |
|---|
| title | How to import a CSV client file |
|---|
| | UI Text Box |
|---|
| 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. |
- Open the client’s Client Accounting > Trial Balance or Workpapers tab.
- From the TASKS bar, click Manage client file imports. The Manage Client File Imports window appears.
Image Removed - Under Choose file type, select .CSV.
- In the Select file to import field, click the ellipses button (Image Removed) and browse to the location of the .CSV file you want to import.
- Select the type of mapping to use from the Select template dropdownIf you want to map the client’s chart of accounts to the ledger chart of accounts, tick The chart of accounts in the file being imported is different to the chart in the ledger – I want to specify how to map the accounts to the ledger checkbox.
Do not tick this checkbox if the client’s chart of accounts is the same as your chart of accounts. - If you want to include consolidated transactions so that you have one transaction per account on a monthly/annual basis, tick the Consolidate imported transactions into single monthly or annual transactions for each account checkbox.
- Click Next. The Map Accounts window appears.
- Select the accounts on the Practice ledger chart that correspond to each account on the accountant's chart.
- If required, adjust the ledger accounts to align the balances with the equivalent accountant's accounts.
- If you want to import all transactions in the file:
- Do not tick the Consolidate imported transactions into single monthly or annual transactions for each account checkbox.
- Click Next.
Click Next. The Import Wizard closes. - Click Finish. The accounts are posted to the ledger.
|
Importing a CSV fileA CSV file contains either a chart of accounts, or chart of accounts and transactions. When importing a CSV file, an XML configuration file in the Deploy folder controls the mapping between the CSV file columns and ledger fields. | UI Text Box |
|---|
| The CSV file you're going to import needs to have no more than 38,000 journal entries. If you've got more, either: - separate the CSV file into multiple files and import each file separately, or
- consolidate prior period data to decrease the total entries.
|
| UI Text Box |
|---|
| You can watch us stepping through this procedure in the Import prior year balances chapter in the Adding data to MYOB Ledger video. |
| UI Expand |
|---|
| title | To import a CSV client file |
|---|
| title- Go to the Client Accounting > Trial Balance (Workpapers) tab.
- On the TASKS bar, click Import client file or Manage client file imports depending on your version. The import wizard's Select File window appears.
 Image Added - Select the .CSV radio button.
- Click the Select file to import field's ellipsis [...] button, and browse to and open the CSV.
In the Select template dropdown, select the type of mapping template that matches the client’s source file. | UI Text Box |
|---|
| | Choose the template that matches the client’s source file. If The default options are Default Template and Quickbooks 2014 Template. If other options are listed, they have been customised for your site. If you’re not sure which one to selectuse, consult your contact IT administrator or contact MYOB Support. |
Click Next. The 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.Click Next. The system validates the file format. Continue to Selecting account mapping options.
| | UI Expand |
|---|
import wizard's Select account mapping options window window appears. If you want to map the client’s chart of accounts to the ledger chart of accounts, select The chart of accounts in the file being imported is different to the chart in the ledger – I want to specify how to map the accounts to the ledger checkbox. | UI Text Box |
|---|
| | Do not select this checkbox if the client’s chart of accounts is the same as your chart of accounts. |
- If you want consolidated transactions so that you have one transaction per account on a monthly/annual basis, select the Consolidate imported transactions into single monthly or annual transactions for each account checkbox and select the Monthly or Annually radio button.
- Click Next. The Map Accounts window appears. Some accounts are mapped by the system, but you need to map any unmapped accounts indicated by a question mark [?] icon.
- If any accounts with a question mark [?] icon, click the blank Map to cell and select the account to map to.
Click Next. The Preview window appears. - Click Finish. A message asks if you want to post all the file data to the ledger.
- Click Yes. The accounts are posted to the ledger and an import summary message appears.
- Click OK.
|
Using the default MYOB templateTo 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. | UI Expand |
|---|
| title | Mandatory and optional ledger fields |
|---|
| | Column name | Maps to | Mandatory/optional |
|---|
| account code | AccountCode | Mandatory | | account name | AccountName | Optional | | account type | AccountType
| Optional Note: The following values are valid:
Income, Other Income, Liability, Equity, Expense, Other Expense, Assets, Cost of Sales, Uncategorised | | account type group | AccountTypeGroup | Optional | | tax code | TaxCode
| Optional
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 reference | TransactionReferenceId
| Optional | | transaction description | TransactionDescription
| Optional | | transaction date | TransactionDate
| Optional (mandatory if TransactionAmount is mapped)
The following formats are both valid: | | transaction amount | TransactionAmount | 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 (+, -) |
| | UI Expand |
|---|
title |
Using the QuickBooks Transaction detail templateThe provided QuickBooks Transaction detail template works with the following versions of QuickBooks (Windows and Mac): - QuickBooks 2016
- QuickBooks 2015
QuickBooks 2014 | UI Text Box |
|---|
| We only support a single amount field, and not separate debit and credit fields. |
| UI Expand |
|---|
| title | Generating To generate a CSV file from QuickBooks |
|---|
| In QuickBooks, perform the following: - Select Edit > Preferences from the menu bar.
The Preferences window displays. - Select Accounting in the left-hand toolbar, and then select the Company Preferences tab.
- 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.
- 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. - Click Hide header in the toolbar at the top of the main window.
Select the range of dates and the sort order from the toolbar. | UI Text Box |
|---|
| 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. |
- Windows: Select Excel > Create new worksheet in the toolbar.
Mac: Click Export in the toolbar.
The Send Report to Excel window displays. - Select the Create a comma separated values (.csv) file option, and click Export.
The Create Disk File window displays. - Type a file name and choose the location to save your .CSV file.
- Click Save.
You can now import the CSV file into AE/AO Ledger.
| | UI Expand |
|---|
title |
Importing transactions from Quickbooks Online into AE/AO LedgerIn order to 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. | UI Expand |
|---|
| title | 1. 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.
- 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 |
|---|
| UI Text Box |
|---|
| 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 |
| UI Expand |
|---|
| title | 2. To update the MyobCsvMaps.xml file |
|---|
| . | | UI Text Box |
|---|
| | 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:- 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). - 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 |
| UI Expand |
|---|
| title | 3. To 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 for importing a client file".
|
| HTML Wrap |
|---|
| width | 15% |
|---|
| class | col span_1_of_5 |
|---|
| | |
| HTML Wrap |
|---|
| float | left |
|---|
| class | col span_1_of_5 |
|---|
| | Panelbox |
|---|
| name | green |
|---|
| title | Related topics |
|---|
| |
|
|
Yes
No
Thanks for your feedback.