Type of Import Data into Sage 50

There are many ways through which a user can get their “import data into Sage 50” from other accounting software, but all of those processes require manual work to be done by the user and are more time and effort-consuming, making them not the first option anyone would want to choose.

Data can simply be imported into Sage 50 from another accounting software automatically, which will be discussed in-depth in this article.

Prepare your Data Before Importing into Sage 50

There are a few things that the user needs to take care of before they can start import data into Sage 50. Failure to adhere to the following things may result in a loss of data or information.

The first thing that needs to be kept in mind before the user can start the import process is to take a backup of their Sage 50 database. If there happens a mistake during the import process, the Sage 50 accounting system can easily be restored from a backup.

In addition to this, there are a few additional pointers that need to be kept in mind for a smooth import data into Sage 50. Some of these extremely helpful tips/pointers are mentioned below.

• Sage 50 requires the ‘Due Date’ field of an imported sales/purchase transaction to be imported as well. It is not capable of calculating the due date for the transaction.
• Some other data does not get imported:
  • G/L account links, custom field labels, shipping methods, payroll fields, etc.
  • Inventory beginning balances.
  • Job beginning balances.
  • Sales Tax information.
  • Payroll Tax Formulas.
  • Task Window Templates.
• Some fields also have specific values and information about whether they can be imported or not. Users should look at the field definition list for more information.
• The order in which the user imports the data is also extremely important. For example, the user cannot import the vendor list before the employee list, and so on. There is a certain order that needs to be followed, which is: Chart of accounts -> Employee List -> Vendor List -> Customer List -> Inventory Item List -> Phase List -> Cost Code List -> Job List.
• In some situations, Sage 50 does not allow duplicate reference numbers to be imported. For instance, there cannot be a duplicate invoice number for the same customer ID. In such a scenario, the user must enter those duplicate records manually.
• In some situations, Sage 50 does, in fact, allow duplicate reference numbers to be imported. For instance, payroll checks can have duplicate check numbers. A warning will show up regarding the same, but that does not imply that doing so is forbidden.

Tips to Import Data or Information into Sage 50 Accounts

Now that the user has taken care of all the things that need to be kept in mind while preparing their data for the import into Sage 50, we can actually begin the process to start the import in a smooth fashion. To import data into Sage 50, the user needs to just follow these steps, and their data will be imported hassle-free.

• First, the user needs to navigate to the ‘File’ option, which can be found in the menu bar.
• In the ‘File’ menu, the user then needs to choose ‘Select Import/Export.
• The user then needs to select the program area and the template that they want to use.
• After that has been done, the user needs to then press the ‘Import’ button.
• Sage 50 will then display the window for the template that the user selected. It will also be showing the ‘Fields’ tab.
• Now, the user will need to select the ‘Show’ checkbox for every field that is included in the import file.
• If the order of the fields listed in front of the user is not correct, the user must select the first field they wish to relocate, and then press the ‘Move’ button. They then need to select the row beneath the row to which they want to move the selected field too.
• Next, the user needs to go to the ‘Options’ tab. They need to then select the ‘Warn if Duplicate Reference’ check box, provided that they want Sage 50 to tell them every time a duplicate check number or receipt reference appears during the import process.
• Next, the user needs to specify the path of the import file. What this means is that they need to tell Sage 50 where the import file is stored on their computer. If it is stored on an external storage device, the user needs to open that device’s folder and give the import file to Sage 50.
• If the import file contains headings, the user needs to select ‘First Row Contains Headings’. This will help Sage 50 to ignore the first row and not confuse it with the rest of the data that is present in the file.
• The user can then save these changes to the template in order to use them later simply by pressing ‘Save’ and providing a name for the template. It must be kept in mind that the name of the template should be unique, the Sage 50 default name cannot be used.
• When all of the aforementioned steps have been completed, the user can simply select ’OK’, and the import process will commence at this point.

If your Data Import wizard validation getting any issues with your data, need to edit the data in your import file to correct the issue.

• First, close the file.
• After that again run the Data Import wizard.

What Type of Data or Information You Can Import into Sage 50 Accounts?

There are so many things that a user can import data into Sage 50, which makes things a lot easier for any user who is trying to import data in Sage 50 from another accounting software. Following are the types of information that a user can import data into Sage 50, with exceptions of items that cannot be imported mentioned as well.

• You can import Audit trail transactions but can’t import foreign bank transactions, sales receipt, purchase payment, sales discount, purchase discount, CIS etc.
• You can import Bank accounts as nominal records.
• You can import Customer records but can’t import customer delivery addresses.
• You can import Fixed asset records.
• You can import Nominal accounts and standard budgets but can’t import advanced budget information.
• You can import Price lists from within the price list record.
• You can import Product records but can’t import Bill of materials and Quantity in stock.
• You can import Project records.
• You can import Project-only transactions.
• You can import Stock take.
• You can import Stock transactions but can’t import Movement out and Movement in.
• You can import Supplier records but can’t import supplier delivery addresses.

What Type of Data or Information You Can’t Import into Sage 50 Accounts?

There are so many things that a user can’t import data or information into Sage 50. Following are the types of information that a user can not import data into Sage 50 accounts software.

You can’t import Invoice and credits module.
You can’t import Purchase orders module.
You can’t import Sales orders.
You can’t import Quotations.
You can’t import Subcontractors records.
You can’t import CIS subcontractor transaction.

Tips to Resolve the Most Common Import Errors Sage

Here we discuss various kinds of errors that arise when importing data into Sage 50 and how any user can resolve them through some very easy-to-follow steps.

Here are some of the most common errors that arise while importing data into Sage 50.

The first thing that could go wrong and might seem a bit tricky at first is that the import file that the user is trying to use is itself corrupt. To fix this, the user just needs to copy and paste the data of that import file into a new template file of type CSV which can be downloaded from the relevant import page.

What this error message basically means is that the reference cell that has been provided by the user might not be unique. Duplicate references could be present in the import file. It could also imply that there is another record present in the file that is also using this as a reference. To fix this, the user just needs to enter a reference that is unique and then proceed to attempt to try the import once again.

This error message shines a light on the fact that the VAT number for the customer in question is not a valid VAT number. The user can easily fix this by entering a valid VAT number without the country code being entered as well. The country code is at the start of the code and before the VAT number.

Another thing that needs to be kept in mind when working with VAT numbers is that if the VAT number begins with a 0, the CSV file does not remove the zero. If by chance the zero is removed, the user needs to right-click on the cell in question and click ‘Format Cells’. After that, the user needs to select ‘Text’ from the ‘Category’ option list and then hit ‘OK’.

This error message means that the user needs to enable their foreign currency transactions if they wish to import foreign currencies. To enable their foreign currency transactions, the user needs to.

• First, the user needs to go to ‘Settings’. 
• Under Settings, they need to go to ‘Business Settings. In the Financial Settings, the user needs to click on ‘Currencies’.
• Then the user needs to select ‘Enable Foreign Currencies’.
• To enable up-to-date currency exchange rates, the user can select ‘Use Live Exchange Rates.
• To add a new currency, the user can simply select a new currency that they wish to add from the dropdown list that contains the names of all the currencies. 
• The user can then hit ‘Save’ and foreign currency transactions would have been enabled successfully at this point.

If the user has already enabled foreign currency transactions, the user can import the currencies of their contacts. The only thing that needs to be kept in mind is that if the currency column has been left blank, the software will import it as the user’s default currency. So the currency should be correct with respect to the country that the user is working with. For example, for American transactions, the currency should be USD and nothing else.

If the user has specified a default nominal ledger account in the import file, the account has to have already been present in the user’s service. To fix this error, the user needs to either create a nominal ledger account or change the account in the CSV to an already existing account.

When the user is importing customer or supplier opening balances, they need to make sure that the reference for that customer or supplier in the CSV file matches the reference in Accounting. If the contact already exists, the user needs to enter the correct reference in the CSV file. If the user has not created the contact, they must do so before the opening balance for that customer or supplier can be imported.

The correct date format that is accepted is dd/mm/yy. All the dates should be in this format before the file can be imported. If the format gets changed in Excel, the user needs to right-click on the cell in question and click ‘Format Cells’. After that, the user needs to select ‘Text’ from the ‘Category’ option list and then hit ‘OK’.

If the user gets this error message, they should first check the file that they are trying to import. What this means is that they should make sure that they are importing the correct file.

The contact CSV file has a size limit of 0.5MB, and the file cannot occupy more space than this. The user needs to make sure that the file size is within the permitted range before they import the file. One way around having too large files is to split the file into two smaller-sized files and then import them separately. The imports will not be overwritten by any subsequent imports.

You can also find help with click on resolving data import errors.

List of the Import Data Errors into Sage 50 Cloud Accounts

To import data into the software, use the Sage 50cloud Accounts import wizard. When you import data, most problems you encounter refer to a particular line and field. This makes it simple to identify the problem in your import file.

If a transaction has an American date format you may get this error, when importing data into Sage Accounts. Sage 50 imports the file information, looks for the file in the location you specified, and converts the file as much as it can to the Sage 50 format. Sage 50 notifies you of the issue and the file’s line number where it happened if there is a problem with the data in the file.

Cause

When you are importing data into Sage Accounts, In case a transaction has an American date format, you will receive the following file import error that is mentioned below:

*** You can’t read the file, as it is open in another application***

This issue will occur in case the date field is populated with nil or 0 or in case the date is incorrectly entered, such as 31 April.

Resolution

As a workaround, change the file’s date format, check that it has headers, then import it again. If the same notice continues to show and there are no problems with the date format, the Options .ini file can be deleted to resolve the issue.

When an import occurs, the data directory’s Accdata folder and the Import Maps folder are both generated. The Import Maps folder has subfolders that are related to the import type being used, like Audit Trail Transactions. The mapped fields are stored in Options.ini, which is created in the subfolder. This document could be deleted.

• You have to click Help on the menu bar and then you have to click About and in the Program Details area, make a note of the Data Directory.
• Before ACCDATA from the data directory that is noted in step 1, you have to browse to the folder in Windows Explorer. For example, in case your Data Directory is c:\sagedata\ACCDATA, browse to c:\sagedata.
• After that, you have to open the Import Maps folder and for the type of import, you have to open the folder. For example- Open this folder if you imported Audit Trail Transactions.
• Then you have to right-click on Options.ini and then you have to click Delete and then click Yes.
• Again try to import the file.

Save the file as a CSV file and then try to import again at this point, if this issue still occurs.

Cause

When you try to import a transaction this message appears that includes a Country of VAT value but with a tax code that is not flagged to OSS/IOSS reporting.

Resolution

Edit the relevant line on the import file to fix this error as mentioned below and then import the file again.

• Remove the Country of VAT value if the transaction is not an OSS/IOSS transaction.
• Change the tax code to one that is properly identified for OSS/IOSS reporting if the transaction requires OSS/IOSS reporting.

Cause

When you try to import a transaction with a non-EU country code in the Country of VAT field, you will see this message.

Resolution

Change the appropriate line on the import file as shown below to correct this problem, then import the file again.

• Remove the Country of VAT value if the transaction is not an OSS/IOSS transaction.
• Change the Nation of VAT Value to the appropriate EU country code if the transaction is subject to OSS/IOSS reporting.

You can get a Disk or File access error message when importing data into Sage Accounts using the File import option.

Resolution

• When the import file is damaged and Sage Accounts is unable to read the data, this notice appears
• The import must be filed once more after creating the import file in a different location.

You can get a Disk or File error when importing data into Sage Accounts using the File import option.

Resolution

When the import file is corrupt and the information can’t be read by Sage Accounts, at that time this message appears. 

You should restore a backup before importing. Then again you must create the import file in a new location and then try to import again.

Cause

When you try to import a bank transaction whose transaction type is inconsistent with the bank account type, this notice will display. Trying to import a cash payment into a visa bank account is one example.

Remove this line from the import file or change the transaction type to match the bank account type, then import the file again to fix this error.

Resolution

If you want to check the bank account type, then on the navigation bar you have to click Bank accounts, then:

• You have to double-click the relevant bank account and then check the A/C Type.

To use for each bank type the bank transactions are as follows:

• Cheque Account- Bank Receipt (BR) Bank Payment (BP)
• Cash Account- Cash Receipt (CR) Cash Payment (CP)
• Credit Card Account- Visa Receipt (VR) Visa Payment (VP)

Cause

For the following reason, this message may appear:

• When you are trying to import foreign banking transactions.

Resolution

Importing foreign currency bank transactions into Sage50cloud Accounts isn’t possible, right now. You have to post these transactions manually.

Cause

For the following reasons this message may appear:

• The import file’s nominal code is three digits or less.
• Characters other than numbers can be found in the nominal code on the import file.

Resolution

For the nominal code enter the leading zeros. For example, in case you enter 50 then change it to read 0050.

Reason

• The import file’s customer/supplier account reference is nine characters or longer.
• The import file’s customer/supplier account reference contains non-alpha-numeric characters.

Solution

• Change the reference to be no more than eight characters long. For example, joebloggs should be changed to joeblogg in your reference.
• Remove or swap out the problematic characters for purely alphanumeric versions.

Cause

One of the following file import errors may appear while importing data into Sage Accounts:

Account Reference: Reference to Non-Existing Sales Ledger Account Error in Record X

Account Reference Error: Reference to Invalid Purchase Ledger Account in Record X

When you try to import a record or transaction that makes reference to a non-existent customer or supplier account, this message appears.

Resolution

Remove this line from the import file or make a record for the customer or supplier within the business, then import the file again to fix this error.

You can encounter the following issue when importing data into Sage 50cloud Accounts v28.1.

Resolution

The issue can be resolved in the latest version of Sage 50cloud Accounts. By using the download and install guide you can upgrade this.

Follow the below workaround to resolve this.

Import files that include a zero-value transaction will not be imported, when you are importing transactions into Sage 50cloud Accounts v28.1.

Before you import into Sage 50cloud Accounts you will need to remove zero-value transactions from the file to solve this issue.

Journal transactions in Sage 50cloud Accounts must have a matching debit and credit net amount, and all transaction elements must be posted on the same day.

Reasons

• You are trying to import an unbalanced journal transaction. You might import a journal credit but not a journal debit, for example.
• The journals in the import file are not grouped together even though you are attempting to import a journal that balances.
• In the journal, you are attempting to import balances, but you are using the tax amount field to do it.
• In a journal, one line will have a different date than the others.

Solution

• In case the journal doesn’t balance, you must either:
  • Change the affected transaction’s transaction type. Use BP or BR, for example, if you are trying to enter a bank transaction.
  • Then delete the affected transaction
  • Use the opposing journal type to enter the balance. If you’ve already submitted a journal debit for 100.00, for example, enter a journal credit for 100.00 on the same day.
• On the import file, all related journal transactions must be grouped together. Verify the file to make sure all relevant journal debits and credits are in one place and are not separated by other transactions.
• The net amount values for a journal import must be equal. Move the value to the net column if you are using the tax amount to make up a portion of the balance.

For example, you have a journal credit of 120.00 and a journal debit of 100.00 with a tax amount of 20.00. You should change the journal debit to a net sum of 120.00 since the import only imports the net amount.

• You must change the dates to match if the journal transaction is balanced by another transaction on a different date.

Cause

One of the following file import errors may appear while importing data into Sage Accounts: Cost Code in error in record x Reference to a project cost code that doesn’t exist

When you try to import a record or transaction that makes reference to an invalid project cost code, this message will get displayed.

Resolution

To fix this error, either you have to:

• From the import file, delete this line.
• On the import file, change the project cost code.
• You have to create the project cost code.

Try and import the file again once completed.

Resolution

When the date of the transaction is invalid this error appears. Use the correct date format of DD/MM/YYYY to fix the error.

Reason

• Sage Accounts does not have the nominal code listed on the import file.
• Nominal codes are absent from the import file and are not set as defaults in the Customer, Supplier, or Product Defaults fields.

Solution

• Update the nominal code and then import the file once more.
• Click Settings, followed by Customer, Supplier, or Product Defaults, on the menu bar, and then type the necessary nominal code.

You can get the following error, when importing data into Sage Accounts: Error in record x: Exchange Rate Importing foreign currency is an illegal transaction type.

Resolution

This message shows when you are trying to import an incorrect transaction in a foreign currency.

You can’t import the following foreign currency transaction types:

• SA, PA, SP, PR, SD, PD, JD, and JC. BP, BR, VP, VR, CP, CR, and SR.

Only transactions with the SI, SC, PI, and PC codes can be imported in a foreign currency.

Remove the invalid transaction from the import file to fix the error.

If you accidentally referenced a charity fund in your import file or are using Sage 50cloud Essentials, which does not include a charity feature, you should remove the reference from the file to fix the error. Simply activate the Charity/Non-Profit choices in your company preferences as an option.

• First, you have to click Settings and then you have to click Company Preferences.
• Then you have to click Parameters and then you have to select the Enable Charity/Non-Profit options check box in the Others section.
• Lastly, you have to click OK.

Now, you can try to import again.

Resolution

Correct or delete this line from the import file and then import the file again to fix this error.

Resolution

When you are trying to import a transaction type that cannot be imported, this message will get displayed.

Only the following transaction types are available for import are mentioned below:

SA, PA, SP, PR, JD, JC, BP, BR, VP, VR, CP, CR, SI, PI, SC, PC, and so on.

You can remove the transaction or replace it with a valid transaction type to correct the problem. Don’t leave a blank line in the file if you remove the transaction.

Receiving this message means when you are trying to import an IntraStat Duty that contains characters that aren’t numbers.

Resolution

To fix this error you have to make sure the IntraStat duty codes you are trying to import are one or two digits long and only contain numbers or remove this line from the import file.

Resolution

When you import a stock take and the data has a negative value in the Actual field, this notice will occur. Before importing the file again you can make the necessary changes or remove the problematic information.

Reason

Sage Accounts does not have the nominal code listed on the import file.

Solution

You can amend the nominal code and then import the file once more.

Cause

Using the File Import option, you cannot import transactions with a T18 tax code for postponed accounting. This is so that a credit note can be created automatically when using T18, which cannot be done when the transaction is imported.

Resolution

This is advised to process this transaction independently in your Sage 50cloud Accounts program to solve this issue.

Reason

• The product code that is on the import file contains space.
• A product has a blank product code

Solution

• In the product, the code removes any spaces.
• Make sure that each product that you are going to import has a valid product code.

Resolution

When you attempt to import a product that doesn’t exist this message will display on your screen. Remove this line from the import file or create the stock code within the company, then import the file again to fix this error.

Cause

When you try to import stock transactions to a product that is classified as a service or non-stock item, you will receive this message.

Solution

If you want to fix this error then you have to remove the information that refers to non-stock items or services.

Cause

One of the following file import errors may appear while importing data into Sage Accounts:

• ‘Error in record x: Account Reference: Reference to non-existent Sales Ledger account’
• ‘Error in record x: Account Reference: Reference to non-existent Purchase Ledger account’
• ‘Error in record x: Supplier A/C Ref: Reference to non-existent Purchase Ledger account’

When you are trying to import a record or transaction that makes reference to a non-existent customer or supplier account you will receive this message.

Resolution

If you want to fix this error then remove this line from the import file or make a record for the customer or supplier within the company.

When you are importing a stock take and allocating quantity is higher than the actual value that you are importing then in that case this message appears.
Before importing the file again you have to reduce or remove the allocations on the stock record for fixing this error.

Resolution

When the data is unable to be read then this message is displayed. Mainly this is caused by currency symbols such as £.

You have to remove all currency symbols from the import file to fix this error.

Note: On an Excel document, the currency symbols may be hidden. If so, rename the file with a.CSV extension and open it once more. Before saving the file you have to remove all currency symbols.

Cause

When importing a transaction with the Postponed Accounting tax code of T18, you will get this message.

Resolution

If you are facing this issue then you can resolve this error by changing the tax code on your import file and then try again. In the tax code checker, you can get help to look out which tax code to use.

Reason

• Due to being corrupt Sage Accounts cannot read the import file.
• On the computer that holds the Sage Accounts data, there is not enough disk space.

Solution

• Before importing you should restore a backup, then you can create the import file again in a new location and then again try to re-import.
• Restore a backup before importing. On the drive, you must check where your data is stored, if needed then you should free some disk space or you can reduce the size of the import file.

Cause

When the maximum number of characters in any one field is exceeded this message will appear.

Note:  In any one field the maximum number of characters is 255.

Solution

To make sure that the number of characters in any one field do not exceed 255 then you can check the import file to fix the error.

When the specified field’s opening quote mark, “,” is missing its closing quotation mark, “,” this message is displayed. Edit the import file in Notepad to add the missing quote, then you can import the file again to fix the error.

If you still have an issue?

Due to a record being across multiple lines within Excel, you can still face this issue.

For example:

• 19 June 2022
• Meetings
• service invoice 

This needs to appear as follows, service Invoice meetings on 19 June 2022. This issue can be fixed by selecting all of the data in Excel, choosing Wrap text, and then finding and updating any entries that appear across multiple lines.

Resolution

This message shows when the computer’s storage space for Sage Accounts data is insufficient. Check the amount of free disk space on the drive where your data is stored to resolve this issue. If necessary, make some disk space available or reduce the import file’s size.

This message will display if the data in the relevant field is inaccurate. Say a letter occurs in a field that is supposed to be numeric. If an Excel line break has been entered, it can also be seen. Edit the import file to change the specified field to resolve this problem.

Few reasons why you get this error:

• Hidden line breaks

Sometimes it is challenging to see if an excel spreadsheet contains a concealed line break.

• Exchange rate set to zero

When the exchange rate field is filled with 0, this error happens. For base currency transactions, the exchange rate field must be filled in with 1.

• Negative values

Negative value transactions cannot be imported, although they can be manually entered.

When there are more than 1755 characters in a single line, this message is displayed. Check the import file to make sure that no line has more characters than 1755 in order to resolve the issue.

Resolution

When a closing quote isn’t followed by a comma then this message appears.

You have to edit the import file to add the missing comma then you have to try the import again to fix this issue.

Cause

When you attempt to import a transaction with erroneous data in the OSS Report Type field, this notice will occur. For Union OSS, this value should be 1, for Non-Union OSS, 2, or for IOSS, 3, respectively.

Resolution

Change the appropriate line on the import file as shown below to correct this problem, then import the file again

• Remove the OSS Report Type value if the transaction is not an OSS/IOSS transaction.
• Change the OSS Report Type value to 1 for Union OSS, 2 for Non-Union OSS, or 3 for IOSS if the transaction is subject to OSS/IOSS reporting.

Cause

When you try to import a transaction but the OSS Report Type field’s number is not 1, 2, or 3, you see this notice.

Resolution

Change the appropriate line on the import file as shown below to fix this error, then import the file again.

• Remove the OSS Report Type value if the transaction is not an OSS/IOSS transaction.
• Change the OSS Report Type value to 1 for Union OSS, 2 for Non-Union OSS, or 3 for IOSS if the transaction is subject to OSS/IOSS reporting.

Resolution

When the data in the specified field is out of range, this message appears. For example, departments can only be between 0 and 999, therefore if you try to import 1000 in the department field, it would fail.

Edit the import file to change the specified field, then you have to try importing the file again.

By double quotes any field in the import file can be enclosed. For one of the following reasons this message displays:

• A quote appears in the middle of a field rather than at the beginning.
• There is no opening quote.

To correct the quotes you have to edit the import file and then try to import again, to fix this error.

Resolution

When you try to import a transaction into one of the following Control accounts—other than a journal—this message displays:

• Account for Debtors’ Control: 1100 by default.
• Control Account for Creditors: by default, 2100.
• Sales Tax Control Account, which is set at 2200 by default.
• Purchase Tax Control Account: by default 2201.
• When the supplier is not a subcontractor, the CIS Control Account is used.

Change the nominal code on the transaction before you import your data to fix this error.

By double quotes any filed in the import file can be enclosed. This message displayed in the screen for the following reasons:

• A quote appears in the middle of a field rather than from the start
• Opening quote is not available.
• Too many quotes are used.

To fix this error you have to edit the import file to change the quotations, then retry the import.

Resolution

For the following reasons this message may appear.

• In another application the file is already open

You have to close the relevant application and then try to import again to solve the issue.

• Formatting issues with the Excel file

Use the Comma-separated (*.csv) import option in Excel to import a CSV (MS-DOS) (*.csv) file.

 If the problem still exists, try importing the first row from the import into a another Excel sheet as a test to see if the issue still exists. If it works, it may indicate that the formatting of the original Excel sheet has issues. For data blocks, try the same method until you identify the issue.

Reason

• File extension should be in uppercase, such as test.CSV.
• The folder should contain symbols
• Another application already has the file open.

Solution

• Using the lower case rename the file extension.
• Then you can do is to rename the folder or you can move the file to a folder that doesn’t contain symbols.
• You can close the relevant application.