Importing and Exporting Data

Importing Data

Sage One Accounting allows you to import certain company data. You can only import customer, supplier, item and asset data, excluding transactions.

To access the Import Data function, go to the Company Menu and select the Import Data option.

The following screen displays when you select to import data:

To import data, follow the next few steps:

  • Select the type of data that you want to import into your Sage One Accounting company. It is very important to select the correct type of data because if you select the incorrect type you might end up corrupting your data.
  • In the CSV File Date Format field, select the way your dates display in your file you are about to import. Sage One Accounting will convert the dates to the date format of your company, once it is imported.
  • In the CSV Import File field, click on the Browse button to find your file that you want to import. This file must be in a comma separated values (.csv) file format.
  • You create a .csv file in the following way:
    • Open the data you wish to import in a Spreadsheet application, for example Microsoft Excel. The process using Microsoft Excel is described here. If you are not using Microsoft Excel, please refer to the Help file of your Spreadsheet application for accurate steps to save data in .csv format.
    • Click on File…Save as.
    • The Save as screen displays.
    • In the File Name field, enter the name of your data file.
    • In the Save as type field, select the .csv file option from the drop down menu.
    • Click on Save.
  • If your data file contains column headings, check the My CSV import file contains column headings check box. If your data file does not contain headings, leave the box unticked.

Tip: It is much easier to map your data if it contains column headings. If your data does not have column headings, take a moment to insert headings by inserting a new row above your data whilst working in your spreadsheet application.

  • Click on the Next button.
  • The following screen displays:
  • You need to tell the system which field on the import file corresponds to which field in Sage One Accounting.
  • The first column displays the names of the fields in the system.
  • The second column displays fields in the import file.
  • Choose each field name from the Import File Field Name list. Not all fields have to be imported. To skip fields during the import, choose Do Not Import.
  • Click on the Next button. The following screen displays:
  • Sage One Accounting will let you know if your data was successfully imported. If you encountered errors, Sage One Accounting has an option to view a log file detailing the errors that were encountered.

When changes are made to an import file, these changes will override your existing data when imported.

Common CSV Import Issues

These are the most common issues experienced when importing CSV Imports:

  • 0 values on the import file
  • Decimal separator is set to , (comma) and not a . (full stop)
  • Double spaces in the import file
  • More than 100 characters in the description
  • Excel populates all the info into one column.
Fixing a CSV file when the all the data is populated in the first column

Highlight column A and select the ‘Data tab’ – then select ‘Text to Columns’.

The wizard will display where you need to select the Delimited option and then select Next.

Then select the delimiter your data contains and Next. In the screenshot below, the delimiter is a semicolon.

On the final screen, select the date format ‘DMY’ and click on the Finish button.

Save the changes made and import the data into Sage One Accounting again.

Fixing your computer’s Regional Settings

Please ensure your “Region and Language” settings in the “Control Panel” for Windows are set to English South Africa, United States, or United Kingdom with the details below.

To access the “Region and Language screen” on a Windows Vista or 7 computer, click on Start > Control Panel (view by large icons and not by category) > Region and Language.

If you are using a Windows 8 computer, Drag your mouse to the right-hand corner of the computer screen > the bar will appear from the right-hand side > select Settings > then Control Panel (view by large icons and not by category) > select Region.

The screen below will appear once you have completed the appropriate steps described above.

As highlighted above, make sure you have selected the appropriate language under “Format:” In this example, we have chosen English (United Kingdom).

Then select the “Additional settings…” option. The screen below displays.

Select the Numbers tab. Apply a full stop to the field “Decimal symbol:” and “List separator:” by typing a full stop into the field as highlighted in the screen above. Then click on “Apply”.

Click on the Currency tab.

Apply a full stop to the field “Decimal symbol:” by typing a full stop in to the field as highlighted above. Then click on “Apply” and then “OK”, and, on the following screen, “Apply” and “OK” again.

Thereafter, attempt to open Sage One.

All should be fine once you have completed the above.

However, if the error re-appears, you may reset the current Region and Language settings by selecting the “Reset” button as highlighted in the screen below.

Once complete, you may re-apply the settings as described above and then open Sage One.

If, however, this still does not resolve the error, you may restart the computer and then attempt the above again. If the error persists, change the language on the screen below to another English language. Try the language that was not used from the options of English South Africa, United States, and United Kingdom (as highlighted below) and then repeat the guidelines as described above.

Checking your System Preferences on a Mac

If you have CSV Import issues as per the topic above and you are using a Mac, you have to change your system preferences:

On your Mac, select the Apple icon on the top right of the screen and click on the System Preferences option. The following screen will display:

In the system preferences screen, select the Language & Regional tab. The following screen will display:

Check that the Region is set to South Africa (Custom) and the preferred language it set to English.

Exporting Data

To access the Export Data function, go to the Company Menu and select Export Data.

The following screen displays when selecting to export your data.

In the Export Type field you will select the type of data you wish to export. As previously mentioned, you can only export customer, supplier, item and asset data files, this excludes any transactions.

In the Date Format field, select the way you want your dates to display in the exported file.

After you have made your selection, click on the Export Data button. Your data will be exported to a Comma Separated Values (.CSV) file which can be opened in most spreadsheet applications, for example, Microsoft Excel.

Customer Map Fields

The following table contains the customer mapping fields:

Customer Fields Content Requirements
Name Customer Name Maximum of 100 characters.
Category Customer Category Maximum of 100 characters. Sage One Accounting will create the category if it does not exist.
Opening Balance Customer’s Opening Balance This is a numeric field.
Opening Balance Date Opening Balance Date The date format used here must be the same as the Date Format specified in the File Information section, for example, dd/mm/yyyy.
Postal Address 1 Postal Address Maximum of 100 characters.
Postal Address 2 Postal Address Maximum of 100 characters.
Postal Address 3 Postal Address Maximum of 100 characters.
Postal Address 4 Postal Address Maximum of 100 characters.
Postal Address 5 – Postal Code Postal Address Maximum of 100 characters.
Delivery / Physical Address 1 Physical Address Maximum of 100 characters.
Delivery / Physical Address 2 Physical Address Maximum of 100 characters.
Delivery / Physical Address 3 Physical Address Maximum of 100 characters.
Delivery / Physical Address 4 Physical Address Maximum of 100 characters.
Delivery / Physical Address Postal Code Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 1 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 2 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 3 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 4 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 Postal Code – Line 5 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 1 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 2 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 3 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 4 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 Postal Code – Line 5 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 1 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 2 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 3 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 4 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 Postal Code – Line 5 Physical Address Maximum of 100 characters.
Text User Field 1 Text User Defined Field Maximum of 100 characters.
Text User Field 2 Text User Defined Field Maximum of 100 characters.
Text User Field 3 Text User Defined Field Maximum of 100 characters.
Numeric User Field 1 Numeric User Defined Field This is a numeric field.
Numeric User Field 2 Numeric User Defined Field This is a numeric field.
Numeric User Field 3 Numeric User Defined Field This is a numeric field.
Date User Field 1 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 2 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 3 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Yes/No User Field 1 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 2 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 3 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Credit Limit Customer Credit Limit This is a numeric field.
Contact Name Customer Contact Name Maximum of 100 characters.
Telephone Number Customer Telephone Number Maximum of 30 characters.
Fax Number Customer Fax Number Maximum of 30 characters.
Cell Number Customer Mobile Number Maximum of 30 characters.
Email Address Customer E-mail Address Maximum of 100 characters.
Web Address Customer Web Address Maximum of 50 characters.
VAT Reference Customer VAT Reference Number Maximum of 30 characters.
Active Specifies whether this customer is active or inactive. Active must be specified as true and Inactive must be specified as false. If this field is not imported, the customer will default to Active.
Accepts Electronic Invoices Specifies whether this customer accepts electronic invoices. Specify as true if the customer accepts electronic invoices and false if not. If this field is not imported, the customer will default to not accepting electronic invoices.
Auto Allocate Specifies whether invoices are auto allocated. Specify as true if the invoices are auto allocated and false if not. If this field is not imported, the customer will default to not to allocate invoices automatically.
Statement Distribution Specifies which method statements are distributed. A selection of Print, Email, Print and Email or None must display.
Enable Customer Zone Specifies whether this customer is enabled to view invoices on the customer zone. Specify as true if the customer is enabled to view and false if not. If this field is not imported, the customer will default to not viewing invoices on the customer zone.
Cash Sale Customer Specifies whether this customer is cash sale customer. Specify as true if the customer is a cash sale customer and false if not.
Sales Rep Displays name of the sales rep. Name of the sales rep will be imported. If field is blank, then no sales rep will be allocated.
Default Price List Specifies which price list to use. A selection of the different price lists.
Currency Specifies the currency of the customer. This field specifies the currency apostrophe for example ZAR for South African Rand. This field is only active when you have the Multi-Currency add-on module.

Supplier Map Fields

The following table contains the supplier mapping fields:

Supplier Fields Content Requirements
Name Supplier Name Maximum of 100 characters.
Category Supplier Category Maximum of 100 characters. Sage One Accounting will create the category if it does not exist.
Opening Balance Supplier’s Opening Balance This is a numeric field.
Opening Balance Date Opening Balance Date The date format used here must be the same as the format as the data you are importing, for example, dd/mm/yyyy.
Postal Address 1 Postal Address Maximum of 100 characters.
Postal Address 2 Postal Address Maximum of 100 characters.
Postal Address 3 Postal Address Maximum of 100 characters.
Postal Address 4 Postal Address Maximum of 100 characters.
Postal Address Postal Code Postal Address Maximum of 100 characters.
Delivery / Physical Address 1 Physical Address Maximum of 100 characters.
Delivery / Physical Address 2 Physical Address Maximum of 100 characters.
Delivery / Physical Address 3 Physical Address Maximum of 100 characters.
Delivery / Physical Address 4 Physical Address Maximum of 100 characters.
Delivery / Physical Address Postal Code Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 1 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 2 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 3 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 – Line 4 Physical Address Maximum of 100 characters.
Additional Delivery Address 1 Postal Code – Line 5 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 1 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 2 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 3 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 – Line 4 Physical Address Maximum of 100 characters.
Additional Delivery Address 2 Postal Code – Line 5 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 1 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 2 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 3 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 – Line 4 Physical Address Maximum of 100 characters.
Additional Delivery Address 3 Postal Code – Line 5 Physical Address Maximum of 100 characters.
Text User Field 1 Text User Defined Field Maximum of 100 characters.
Text User Field 2 Text User Defined Field Maximum of 100 characters.
Text User Field 3 Text User Defined Field Maximum of 100 characters.
Numeric User Field 1 Numeric User Defined Field This is a numeric field.
Numeric User Field 2 Numeric User Defined Field This is a numeric field.
Numeric User Field 3 Numeric User Defined Field This is a numeric field.
Date User Field 1 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 2 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 3 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Yes/No User Field 1 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 2 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 3 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Credit Limit Supplier Credit Limit This is a numeric field.
Contact Name Supplier Contact Name Maximum of 100 characters.
Telephone Number Supplier Telephone Number Maximum of 30 characters.
Fax Number Supplier Fax Number Maximum of 30 characters.
Cell Number Supplier Mobile Number Maximum of 30 characters.
Email Address Supplier E-mail Address Maximum of 100 characters.
Web Address Supplier Web Address Maximum of 50 characters.
VAT Reference Supplier VAT Reference Number Maximum of 30 characters.
Active Specifies whether this supplier is active or inactive. Active must be specified as true and Inactive must be specified as false. If this field is not imported, the supplier will default to Active.
Auto Allocate Specifies whether invoices are auto allocated. Specify as true if the invoices are auto allocated and false if not. If this field is not imported, the supplier will default to not to allocate invoices automatically.
Currency Specifies the currency of the supplier. This field specifies the currency apostrophe for example ZAR for South African Rand. This field is only active when you have the Multi-Currency add-on module.

Item Map Fields

The following table contains the Item mapping fields:

Item Fields Content Requirements
Code Item Code Maximum of 100 characters.
Description Item Description Maximum of 100 characters.
Category Item Category Maximum of 100 characters. Sage One Accounting will create the category if it does not exist.
Active Specifies whether this item is active or inactive. Active must be specified as true and Inactive must be specified as false. If this field is not imported, the item will default to Active.
Unit Specifies the type of unit per item. This field will specify the type of unit this item will display as.
Price Lists Allocation of prices. If you have multiple price lists you will be able to map the exclusive and inclusive prices here as well.
Price List Inclusive The Inclusive Price will be automatically calculated if an Exclusive Price is being imported. VAT is calculated using the VAT Type Sales setting or, if not specified, the default VAT Type in Sage One Accounting.
Price List Exclusive The Exclusive Price will be automatically calculated if an Inclusive Price is being imported. VAT is calculated using the VAT Type Sales setting or, if not specified, the default VAT Type in Sage One Accounting.
Physical Specifies whether the item is a physical item or service item. Physical items maintain quantities, service items do not. Set physical items as true and set service items as false. If this field is not specified, Sage One Accounting assumes the item to be a physical item.
Text User Field 1 Text User Defined Field Maximum of 100 characters.
Text User Field 2 Text User Defined Field Maximum of 100 characters.
Text User Field 3 Text User Defined Field Maximum of 100 characters.
Numeric User Field 1 Numeric User Defined Field This is a numeric field.
Numeric User Field 2 Numeric User Defined Field This is a numeric field.
Numeric User Field 3 Numeric User Defined Field This is a numeric field.
Date User Field 1 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 2 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 3 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Yes/No User Field 1 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 2 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 3 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Tax Type Sales Tax Used for Sales Description used for VAT types as set up under Company Setup in Sage One Accounting.
The defaults are:
1. Standard Rated
2. Zero Rated
3. Exempt
Tax Type Purchases Tax Used for Purchases Description used for VAT types as set up under Company Setup in Sage One Accounting.
The defaults are:
1. Standard Rated
2. Zero Rated
3. Exempt
Cost Enter the current item cost. This is a numeric field.
Quantity Enter the quantity currently on hand to specify the opening quantity. This is a numeric field.
Sales Account Specifies the sales account per item. This field will specify the type of sales account per item.
Purchases Account Specifies the purchases account per item. This field will specify the type of purchases account per item.

Asset Map Fields

The following table contains the asset mapping fields:

Asset Fields Content Requirements
Description Asset Description Maximum of 500 characters.
Category Asset Category Maximum of 100 characters. Sage One Accounting will create the category if it does not exist.
Location Asset Location Maximum of 100 characters. Sage One Accounting will create the location if it does not exist.
Date Purchased Date the Asset was Bought The date format used here must be the same as the Date Format specified in the File Information section.
Serial Number Asset Serial Number Maximum of 100 characters.
Bought From Who the Asset was Bought From Maximum of 100 characters.
Text User Field 1 Text User Defined Field Maximum of 100 characters.
Text User Field 2 Text User Defined Field Maximum of 100 characters.
Text User Field 3 Text User Defined Field Maximum of 100 characters.
Numeric User Field 1 Numeric User Defined Field This is a numeric field.
Numeric User Field 2 Numeric User Defined Field This is a numeric field.
Numeric User Field 3 Numeric User Defined Field This is a numeric field.
Date User Field 1 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 2 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Date User Field 3 Date User Defined Field The date format used here must be the same as the Date Format specified in the File Information section.
Yes/No User Field 1 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 2 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Yes/No User Field 3 Yes / No User Defined Field Yes must be specified as true and No must be specified as false.
Purchase Price Purchase Price of the Asset This is a numeric field.
Current Value Current Value of the Asset This is a numeric field.
Replacement Value Replacement Value of the Asset This is a numeric field.

Time Tracking Tasks Map Fields

The following table contains the Time Tracking Tasks mapping fields:

Task Fields Content Requirements
Task Name Task Name Maximum of 100 characters.
Description Task Description Maximum of 8000 characters.
Task Rate Specifies the Task Rate to be used This is a numeric field.
Billable Task Specifies whether this Task is Billable or Non-billable Billable – Must be specified as true and Non-billable must be specified as false. If this field is not imported, the Task will default to Billable
Default Task Specifies if the Task is a default Task To make a Task default, specify as true and non-default Tasks must be specified as false. If this field is not imported , the Task will be set as a default task.
Active Specifies whether the Task is active or inactive Active Must be specified as true and Inactive must be specified as false. If this field is not imported, the Task will default to Active.

Time Tracking Projects Map Fields

The following table contains the Time Tracking Projects mapping fields:

Project Fields Content Requirements
Project Active Specifies whether the Project is active or inactive Active must be specified as true and Inactive must be specified as false. If this field is not imported, the Project will default to Active.
Billable Specifies whether this Project is Billable or Non-billable Billable – Must be specified as true and Non-billable must be specified as false. If this field is not imported, the Project will default to Billable
Customer Customer Name Customer must be an existing customer in Sage One.
Project Name Project Name Maximum of 100 characters.
Description Project Description Maximum of 8000 characters.
Start Date Project Start Date The date format used here must be the same as the Date Format specified in the File Information section, for example, dd/mm/yyyy.
End Date Project End Date The date format used here must be the same as the Date Format specified in the File Information section, for example, dd/mm/yyyy.
Budget Based On Specifies whether the budget will be based on Project or Task Can either be Project or Task.
Budgeted Hours The number of Budgeted hours This is a numeric field.
Project Budget The Budgeted amount This is a numeric field.
Billing Method Specifies the Billing Method for the project Can be Task Rate per Hour, Project Rate per Hour, User Rate per Hour or Fixed Cost.
Rate Specifies the Rate to be used This is a numeric field.
Invoice Lines Specifies the default setting for the project at the time of invoicing Can be Task Detailed, Grouped or Single Line.
Purchase Order No. An optional field to include for your reference Maximum of 100 characters.