Import/Export (CSV and Excel)

With Import/Export you read in data from CSV or Excel files and output freely compiled data as CSV. The layout and terms are familiar from JTL-Ameise (JTL's import/export tool): the area tree on the left, file, mapping, default values and settings on the right, and Dry run and Start import at the bottom.

#How to open import and export

• gear → Import/Export – the Import … and Export … buttons are at the top right. The page itself shows the Log of all runs and the automatic supplier stock.
• In the Items area via the menu: Import data …, Export data … and Import/export log.
• In lists (e.g. items) via More → Import … or Export … – the matching area is then preselected.
• Via quick search Ctrl+K: "Import data …" or "Export data …".

Import and export each open in their own window ("Import data" and "Export data").

#Before you start

In addition, each area needs its specific permission – e.g. "Edit items" for items or "Carry out stocktaking" for stock. Only what the role is allowed to view is exported. Areas without permission are greyed out in the tree with a padlock.

#Areas

Greyed-out entries marked "coming soon" (e.g. attributes, images, marketplaces) are planned.

Shipping data is for shipments created outside allverto – with third-party software or via a freight forwarder without an interface. Per row: Order number (or External order reference), Carrier (DHL, DPD, GLS, UPS, Hermes, Deutsche Post, DHL Express, FedEx or freight forwarder), Tracking number, optionally Shipping date, Weight (kg), Shipping product and Book as shipped (yes/no). If not specified, allverto books the order as shipped: stock is booked out, and the shop or marketplace receives the tracking number. Tracking numbers already stored are skipped. You additionally need Edit orders and Create shipping labels.

Variations: Per row Parent item number, Child item number, Variation and Variation value (e.g. Size = 500 g), optionally Surcharge (gross). Rows with the same parent and child form one child item. The parent item must already exist; if it is still a single item, allverto turns it into a parent item (like "Make parent item" – so only without stock). Missing variations, values and child items are created, an existing single item is assigned as a child. Every child item needs a value for each variation of the parent, and each combination exists only once. A surcharge applies to the value for all child items. Rows without a child item number only create variations and values on the parent.

Bills of materials: Per row Bill of materials (item number), Component (item number) and Quantity. All rows of a bill of materials replace its previous components – like the "Bill of materials" tab of the item. Bills of materials cannot be nested.

Purchase orders: One line item per row with Order reference, Supplier (or fixed in the Supplier tab), Item number, EAN/GTIN or Supplier item number and Quantity, optionally Purchase price (net), Delivery date, Warehouse and Note. Without a purchase price, the purchase price of the supplier item with price scales applies. allverto creates the purchase order as a draft; an order reference that already exists for the supplier is skipped. You need the Create purchase orders right for this.

#Step by step: importing

1. Open Import data … and choose the area on the left, e.g. Prices.
2. Import file: drag the file into the field or select it – CSV, TXT or Excel (.xlsx), at most 50 MB. allverto detects the format and columns itself and shows a preview. If needed, you can change:
   • CSV: Delimiter, Encoding, Quote character, Escape character,
   • Excel: Worksheet,
   • always: Decimal separator, Header in row or Data from row, and the Header row present switch.
3. Map database fields: choose the Column in import file for each field. allverto pre-assigns matching column names; Automatic repeats the suggestion. Fields marked Required must be filled; the field marked for matching identifies existing records. If something is missing, the row is marked red.
4. Default values (only for new records): values for fields that aren't in the file or are empty there, e.g. tax class or manufacturer. More fields via Add field ….
5. Settings → Import options:
   • Import new records and Update existing switches,
   • Identify by – the matching field,
   • Only update when contents change switch,
   • Ignore empty cells (field stays unchanged) switch,
   • for items, prices and manufacturers: the Automatically create missing manufacturers, product groups, responsible persons and categories switch.
6. Click Dry run. Every row is checked, nothing is saved. You can see how many records would be created or changed, how many are skipped and which rows have errors.
7. If everything looks right, click Start import and confirm.

The import runs in the background. Keep running in the background closes the progress popup so you can keep working; a running import can also be cancelled there.

#Special settings per area

Stock is recorded in the stock journal like any other stock movement. For stocktake postings the role needs "Carry out stocktaking", for corrections "Post stock movements".

#Templates

With Save template you save the format, column mapping and options as an Import template (for exports: fields, order, filters and format as an Export template). Next time you select it at the top under Map database fields or under Export template – ideal for a supplier's monthly price list.

Via Documentation at the bottom of the window you can see all fields of the area with explanations.

#Step by step: exporting

1. Open Export data … and choose the area on the left.
2. Export file: Delimiter, Encoding, Quote character, Escape character and the Include header row switch.
3. Number separators: Decimal separator, Thousands separator and Decimal places.
4. Choose an Export template (optional). For items, Supplier rule (purchase price and supplier) determines which supplier is output (default: preferred supplier).
5. Export filter: use Add condition to narrow things down (e.g. field "contains", "is", "greater than", "is empty"). For customers you can use a Customer list filter preset; where available, there is the Also export inactive switch.
6. Map database fields: double-click fields on the left to add them. On the right under These fields will be exported you sort with Move up / Move down, insert an empty Free column, remove fields with Delete or Deselect all, and change the Header name.
7. Click Start export. The export runs in the background; the download starts as soon as it is finished.

#Log

Under gear → Import/Export, Log tab, every run is listed with Time, Type (dry run, import, export), Area, Status (Waiting, Running, Finished, Failed, Cancelled), File, Rows, Created, Changed, Skipped, Errors and By (person or "Automatic: …").

Right-click a run: Details, Download log (import) or Download file (export), and Cancel for running processes. Files are available for download for 30 days.

The log only shows runs for areas your role may also import or export – e.g. customer imports only with the customer permissions. For these runs you can view details and download the result log or export file. Cancel appears for your own runs and for automatic fetches; runs started by colleagues can only be cancelled by someone who also has the Carry out migration from JTL-Wawi (take over data) permission.

#Fetching supplier stock automatically

Many suppliers provide their stock as a CSV or Excel file. allverto fetches it on a schedule and reads it in with a saved import template. The stock is then shown on the supplier item and in the export.

1. Import a sample file from the supplier once in the Supplier stock area and save the mapping with Save template.
2. Open gear → Import/Export, Automatic supplier stock tab, and click New fetch.
3. Fill in: Supplier, Name, Source (Web address (https), FTP or FTPS (FTP with TLS)), Schedule (hourly to daily), depending on the source File address or Server, Port and File path, plus User and Password, and the Import template (supplier stock).
4. Leave the Active (fetch on schedule) switch on and save.

The list shows Last fetch, Next fetch and the Result. Right-click for Edit, Fetch now and Delete. Every fetch also appears in the log.

#Frequently asked questions

#Umlauts and special characters are displayed incorrectly.

Change Encoding from "Automatic" to Windows (ANSI, Windows-1252) or UTF-8. Files from older Excel versions are usually Windows-1252.

#Prices arrive multiplied by 100.

The Decimal separator doesn't match the file. Choose Comma (1.234,56) or Dot (1,234.56) to match your file and do a new dry run.

#Why can't I create new items via prices?

The Prices area only changes existing items. You create new items via Item master data – there you can include the sales price and other fields right away.