Importing data from Excel

Table of contents

Introduction

Different kinds of data can be imported into Ongoing from Excel:

The system supports both Excel files (.xlsx and .xls) and CSV files (.csv, encoded in ISO-8859-1). This guide gives a general overview of how the import process works, and also gives specific examples for each kind of import.

Video

The following video shows how to import articles from an Excel file.

Basic procedure

Importing always follows the same basic procedure, regardless of which type of data is being imported.

Step 1: Go to the correct import page

Go to Registers ⇒ Import and click on the kind of thing you want to import:

Import menu under the Registers tab.

Step 2: Download the template file

Each kind of import has its own template. If you don't already have the file, you can download it by clicking on "Excel template" in each import page:

Circled Excel and CSV templates.

Step 3: Enter data into the template

Open the template file in Excel and enter data. Save the file when you are done.

Step 4: Upload the file

In each import page, there should be a button called "Choose file", "Select file", or something similar depending on your language. Click on it. A new window should appear where you can select your file.

Step 5: Verify that the system has interpreted the file correctly

First verify that the system has identified the columns correctly. The system will try to guess the columns by using their names. If the system cannot identify a certain column, it will be shown as red.

In the example below, a file has been uploaded where one of the column names was called "articcle number". Due to the misspelling, the system was unable to match this to a known column. You can either fix this in the file and upload the file again, or you can manually select the correct column directly in the import page.

Misspelled column name cases the row to become red in color.

After the columns have been verified, verify that there are no errors in the data rows. The rows will have different statuses depending on what is going on:

Status of all the data rows before pressing the import button.

There are three cases:

  1. If a row has the information icon as its status, then there is something wrong with it. If you put your mouse on the icon, you will see a message which explains what is wrong. In the above example, the error is because article number has not been filled in. Note that rows with this status will not be processed when you press Import.
  2. If a row has status "Updated", it means that there is existing data in the system which will be updated if you process the file. In the above example, the second row has status "Updated" because the system already contains an article with article number 123123.
  3. If a row has status "New", it means that it is okay to process and that the data is new. In the above example, the third row has status "New" because the system does not yet contain an article with article number 111111.

Step 6: Finish

Press Import to finish. Afterwards, if there were errors, the row will have the info icon as its status and the row was not imported. If there were no errors, the status of the row will change to "Imported".

Status of all the data rows after pressing the import button.

Example import files

Here we provide some simple example files for each type of import.

Basic article data

Click here to download the article import example file. Each line in the file contains the article data for one specific article. In this example file, there are two articles:

To import the file:

Orders

Click here to download the order import example file. Each line in the import file represents one order line. Note that data which belongs to the order header must be repeated for each line. This means that e.g. the delivery address must be repeated for each line. You can have several different orders in the same file.

In the example there are two orders in total and five order lines in total:

To import the file:

Purchase orders

Click here to download the purchase order import example file. Each line in the import file represents one purchase order line. Note that data which belongs to the purchase order header must be repeated for each line.

In the example there are two purchase orders in total and five purchase order lines in total:

To import the file:

Customers

Click here to download the customer import example file. Each line in the import file represents one customer. There must be a unique customer number for each line.

In the example there are two customers:

To import the file:

Goods reception

Note: If you do several goods receptions using Excel, the effect is cumulative. That is, if you use the import to first perform a goods reception of 5 pieces of article A1, and then do another goods reception for 3 pieces of A1, then the system will contain 8 pieces of A1.

Click here to download the goods reception import example file. Each line must contain the article number, the quantity, and the location.

The example file contains the following:

To import the file:

Suppliers

Click here to download the supplier import example file. Each line in the import file represents one supplier. In most cases, you will want to assigne each supplier a unique number in the "supplier number" column.

In the example there are two suppliers:

To import the file:

Kit articles

Kit articles (or structure articles) are articles which are defined in terms of other articles. We have a guide which explains in more detail how to work with kit articles

When you import kit articles from Excel, you are importing the kit article definitions. That is, you are telling the system which sub-articles each kit article is comprised of.

Click here to download the kit article import example file. Each line binds one sub article to one kit article. The articles involved must already have been created in the system, for example through the basic article data import. Enter the identifying data for the kit article and the sub article, and the amount of the sub article to be required for each instance of the kit article. Leave the other fields blank.

The example file contains the following:

To import the file: