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.

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:

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:

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.

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

There are three cases:

  1. If a row is red, then there is something wrong with it. If you put your mouse on the exclamation mark, 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 red lines will not be processed when you press Import.
  2. If a row is yellow, 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 line is yellow because the system already contains an article with article number A1.
  3. If a row is green, it means that it is okay to process and that the data is new. In the above example, the third line is green because the system does not yet contain an article with article number A2.

Step 6: Finish

Press Import to finish. Afterwards, if there were errors, the line will turn red. If there were no errors, the line will turn gray.

Example import files

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

Basic article data

Click here to download the 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 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 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 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 example file. Each line must contain the article number, the quantity, and the location.

The example file contains the following:

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 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: