Imports

Introduction

To bulk-import existing/legacy data into Werpos, several options are possible. This page describes the preferred method, using the built-in import module. Other solutions for bulk-imports are described in this page Mass imports.

Installation

This module is included with the Werpos distribution, so there is no need to install it.

Configuration

To use this module, you must first enable it using an administrator account, via the menu option “Home – Setup – Modules“.

Choose the tab where the module is listed. Then click on “Activate“.

The module is now activated.

If an icon appears at end of the line for the module, click on it to access the setup page specific to the module.

See also page Setup Other, to modify the separator and character set (charset) for import/export CSV files.

Usage

Importing legacy data will likely require many attempts/modifications of the source file to produce a successful import. It is recommended to use LibreOffice/OpenOffice to “build” a single source spreadsheet (.ods) and repeatedly Save As a CSV for each modification/correction, as the character set handling/csv structure definition is far superior to Excel for multibyte (acccented) text strings.

While most fields are logical in their content, address data for provinces (StateID) is not. This field cannot be imported using the name of the province (which may not be unique between countries, i.e.”Florida”) but must match the rowid integer in table llx_c_departements. The value in the file field should be prefixed with “id:” to ensure that the import check is forced to match the rowid. To match your provinces to the rowid, one method would be to export the table as a CSV using a SQL editor, then use it with the spreadsheet VLOOKUP function to return the rowid. This will still require manual checking as VLOOKUP will only return the first match in the case of duplicate province names.

You may find you have countries in your legacy data for which regions/provinces have not yet been added to Werpos. You can use the myqsl queries in the installation file as examples to add the new regions/provinces before you can import your legacy data.

To import a file:

• Go to the Tools menu – New import.
• Step 1: Choose one of the predefined datasets to match your data. Select it by clicking on the button at the end of the line.
• Step 2: Select the file format CSV/Excel (.xlsx) by clicking on the button at the end of the line. Note: You can download an empty/example file which contains information/hints about the allowed field values: “Download a blank sample file”.
• Step 3: Browse and Select the file to upload from your computer to the Werpos server. Once the file is transferred, click on the button at the end of the line to move to the next step.
• Step 4: Map the file fields to the dataset fields. The left column lists the fields found in the uploaded file. The right column lists the available fields in Werpos. You can move the fields in the left column up or down by a drag and drop from the arrow, to align each file field with the correct Werpos field. If a field in the input file is to be ignored/not imported, it should be moved down under the “Fields source file not imported.”

Note: At the end of the line, hovering over the question mark shows information on the data format/value expected by Werpos. Once the mapping is complete, you may save the mapping as a profile for future use. Click “Next Step” to continue.

• Step 5: Run the import simulation. Check the field separator and string delimiter matches your file layout. If the file has column headers these must be ignored by using “Limit Range”.

Click on “Run Import Simulation” to start. The simulation will check the values in the fields and display an error report. If there is no error, you can click on “Start import final” to insert the data. The imported data is added to existing data.

• All data is imported into Werpos with an additional field of “import_key” that corresponds to the date of the import to aid in resolving problems associated with a particular import. With a SQL editor it is relatively easy to find the imported lines in question.

Example of Use Cases

UCMI01:
Situation	I have an excel file with 3000 products. I want to import them into Werpos.
Actions	Go into menu Tools -> Import assistant -> New import

UCMI02:
Situation	I have an text file with 1000 prospects. I want to import them into Werpos.
Actions	Go into menu Tools -> Import assistant -> New import