All forms that contain lists, summaries, balances, journals etc. can be exported and imported between Uniconta and Excel. There are many reasons that an import or export might be performed. Exporting is useful, for example, to transfer transactions, balances, past due lists etc. into Excel for further processing. Importing is useful, for example, for importing transactions to be posted to journals etc.
Export to Excel
Data can be copied to Excel by performing an export. There are two ways of doing this:
- Export to an open Excel worksheet by copying highlighted lines only.
- Export to a new Excel file by copying all lines in the list form.
Example: Exporting a balances to Excel
Generate the data you would like to export so that it appears as a list on the screen.
Click Ctrl+A (Select All) to copy all lines or;
Hold Ctrl while selecting individual lines. Selected lines will appear in yellow (if using Uniconta’s blue color scheme).
Once all desired lines have been selected:
Click Ctrl+C (Copy) to copy to the clipboard or click on the “Copy to Clipboard” icon on the top right-hand side of the Uniconta screen, as shown below.
Export to an open Excel worksheet
Copy the selected lines only. Open a worksheet in Excel where the data is to be pasted. The data will be pasted into the worksheet where the cursor is placed upon clicking Ctrl+V (Paste from clipboard) or right click and select “Paste”.
Export to a new Excel file
Copy all lines in the form. Click on the “Export to Excel” icon on the top right-hand side of the Uniconta screen.
This will open the “Save as” dialogue box. Choose file location and name. Click “Save”.
The Excel file will not open automatically. The file can now be opened in Excel by going to the location it was saved.
Column headings are important in Excel
Column headings are automatically copied from Uniconta to Excel using the method described above. This is useful to see which fields the columns and data came from. This is especially important if the data is to be imported back in to Uniconta, so that the data is returned the correct columns in Uniconta.
Note the column headings in this example:
Import from Excel
List form data, such as transactions and journals, can be imported into Uniconta from Excel. In order to do this, the column headings in the Excel worksheet must match the field names/column headings in Uniconta vice versa.
The best way to get the correct column headings, is to export a list form from Uniconta into Excel first. This way the user automatically gets the whole set up from Uniconta into Excel with the correct column headings.
Of course, this requires that the Excel worksheet imported to Uniconta is the same list form (accounting journals are imported to an journal list, inventory imported to an inventory list and orders to an order list etc.)
Follow these steps:
- Copy all data to be imported from the Excel worksheet, including the column headings.
- Open the list form in Uniconta, where the data is to be pasted.
- Select the first line in the area where the data is to be pasted.
- Click Ctrl+V (Paste) or use the Excel paste button.
Update from Excel
When inserting data from Excel using the top right-hand menu buttons, users can now choose to ‘update’ the data.
That is, the lines are not inserted, but merge with the line they match in the current screen. Users can then press ‘save’ and their data is updated using the ‘Update from Excel’ function.
The function has two options: The ‘Update line to line’ method should be used when the user has opened a Uniconta screen, copied all lines from that screen into Excel and then edited that data in Excel. The user can then copy the data back to the same Uniconta screen that is still open and still includes the same transactions that were copied over to Excel. This way, Uniconta can assume that line 1 in the screenshot is equal to line 1 in Excel and so on.
The other copy/paste method, ‘Update with key match’, is based on ‘keys’. That is to say, there are one or more fields that uniquely identify the line. In main directories, this is the number field (item number, account number, etc). The unique field may not be visible in other Uniconta screens. In the general journals and order lines, the unique field is ‘line number’ and this field should be dragged into the Layout menu and copied over to Excel.
When copying back to Uniconta, the system uses this field to identify the entry which needs to be updated with data from Excel. The price lists uses several key fields to identify the unique line (item number, item group, discount group, and quantity).
Errors when importing data to Uniconta, usually occur for one following reasons:
- The data does not match the Uniconta format or the values are not valid. For example, the wrong account number may be copied.
- The user chooses the wrong list form to paste into.
- The user has created an empty line in the list form where the data is to be pasted.
- The user has not marked a line where the data is to be copied to.