Skip to main content
All CollectionsIntegrations
Integrate your ERP data with .csv or .json files
Integrate your ERP data with .csv or .json files
Timon Ruban avatar
Written by Timon Ruban
Updated over 4 months ago

Introduction

This article outlines how the integration of your ERP data via .csv or .json files works.

We currently support integrating your internal part numbers (IPNs), inventory data and off-the-shelf parts from your ERP.

So far, the integration only supports one direction: data flowing from your ERP into Luminovo. For the other direction (pulling data from, you can use our REST API and webhooks (and you can export some select things in-app via .xlsx files like a costed BOM).

💡 If you are newly integrating your ERP with Luminovo, we recommend you use the new REST API for that. The process is documented here in detail. Going forward, the file-based process is only supported for backwards compatibility and does not support new features such as the CPN support.

Export your data into .csv or .json files and upload the files to our servers

You can synchronise your data with Luminovo by exporting your data into .csv and .json files and uploading the files to our server.

Guidelines for the .csv template - Internal part numbers

Filename

Please name your .csv as luminovo_tenantname_ipns.csv where tenantname is the first part of the URL you use to access Luminovo. So if you access Luminovo under https://ems.luminovo.ai please name your file luminovo_ems_ipns.csv.

.csv format

Please use either , or ; as a separator for the csv and quote all of your values. The file should use UTF-8 encoding.

"IPN";"Manufacturer";"MPN";"Description";... 
"0-1234#";"Texas Instruments";"LM358";"A description\\nwith line break";...

We follow the CSV format in RFC 4180. Especially quote characters within the value itself should be escaped using double quotes. For example, the value Wire 3/8" should appear in the file as ...,"Wire 3/8""",....

Columns

  • IPN: The internal part number you use to reference an article in your ERP => Required.

  • Manufacturer: The manufacturer of the part => Optional.

  • MPN: The manufacturer part number of the part. This will be taken together with the manufacturer to match your IPN to a part. The more accurate this information is the higher the likelihood we will be able to match the part. => Optional.

  • Description: A description of your part. => Optional.

  • Package: The part package (e.g. SOT23). For SMT packages like 0603 we will assume it is given in the imperial system (measured in thous/inches). Add it as 0603 - metric if it is given in the metric system (measured in mm). => Optional

What if one IPN has multiple part alternatives?

Often one IPN is matched to multiple approved parts (with different manufacturers and MPNs). In that case, simply add a row (with the same IPN, but possibly different Manufacturer , MPN or Description) for each part alternative to the .csv.

If an MPN as well as a manufacturer is provided, we will only match parts that correspond to both. That is, we will not match a part to an IPN in cases where we find a part with the right MPN but the manufacturer is different. If no manufacturer is provided we will match all parts with the corresponding MPN.

In the future, we also plan to automatically match generic parts (like manufacturer free resistors or capacitors) from the descriptions provided for your IPNs, so if your IPN represents a manufacturer-free part specify "Free" for Manufacturer , put the specification of the technical parameters into the Description and the package into the Package column.

What if my ERP doesn't have the Manufacturer and MPN available in its own field?

We often see EMS that don't have this information readily available in their ERP, but instead just have a long text description field that might contain one or more manufacturers and MPNs: in that case just append all of your relevant descriptions into one long string that you put into the Description column. If you have a "short" description and a "long" description of your IPN, you can add two rows with the same IPN and add the short description in the first row and the long description in the second.

If you only provide descriptions and no manufacturers and MPNs, we will not be able to match parts automatically. But Luminovo provides functionality to manually match parts and IPNs after your upload. You will be able to do a search on the descriptions you provided, just as you can probably search for your IPNs in your ERP today. The good thing: once you've matched the parts and IPN in Luminovo once, we will remember the match and use it for your next BOM.

What if I delete an IPN or remove or add a part to an existing IPN?

We assume the .csv that you upload reflects the up-to-date list of IPNs from your ERP. That means if you add a new row with a new MPN in a new upload, we will add that IPN. If you remove an IPN from the .csv we will delete the IPN in Luminovo. If you change the MPN, Manufacturer or Description provided for an IPN (e.g. because you added or removed an approved part), we will delete all automatic and manual matches made from the old part specification for that IPN and start the automatic matching of parts to IPNs anew.

Guidelines for the .csv template - Inventory

Filename

Please name your .csv as luminovo_tenantname_inventory.csv where tenantname is the first part of the URL you use to access Luminovo. So if you access Luminovo under https://ems.luminovo.ai please name your file luminovo_ems_inventory.csv.

.csv format

Please use either , or ; as a separator for the csv and quote all of your values. The file should use UTF-8 encoding.

"IPN";"Available stock";"Total stock";"Unit price (Amount)";... 
"0-1234#";"25";"2000";"2.32";...
"0-3456#";"50";"1000";"1.23";...

We follow the CSV format in RFC 4180. Especially quote characters within the value itself should be escaped using double quotes. For example, the value Wire 3/8" should appear in the file as ...,"Wire 3/8""",....

Columns

  • IPN: The internal part number you use to reference an article in your ERP. Required.

  • Available stock: The freely available stock in your inventory, that can be used for new projects. This is what will be used for generating solutions in Luminovo. Fractional stock are supported. => Optional (defaults to 0).

  • Total stock: The total stock in your inventory (including stock already assigned to a different project). This won't be used for generating solutions, but only displayed as an additional info in Luminovo. A scenario where this might be useful: you have a new prototype project where you need just 10 parts. You have no available stock, but 100,000 in stock assigned to another project. With this info, you'll know that you could maybe still get the 10 parts from your own inventory. Fractional stock are supported. => Optional.

  • Unit price (Amount): The price per part you want to use for calculating solutions. It is up to you to decide what you want to put here. Some like to treat their inventory as "free". Some use the last purchase price and so on. => Optional (defaults to 0).

  • Unit price (Currency): 3 letter currency code as defined by ISO-4217. Optional (defaults to EUR).

  • Standard lead time (Days): If you know this, you can provide a standard lead time (in days) that it takes a distributor to get new parts in stock. => Optional.

  • Packaging: The packaging of the parts. Needs to be one of: AmmoPack, Bag, Bulk, Reel, ReReel, Tape, Tray, Tube, Carton. => Optional.

  • Unit of measurement (Quantity): The unit of measurement for the quantities provided in Available stock and Total stock. If you provide nothing, this defaults to 1 piece. But if the part in your inventory is better measured in a different unit, you can set the unit of measurement here. For example, if your IPN refers to a cable roll, where each roll has 30 meters of cable, you could provide 30 and Meter for the Unit of measurement. If you specify your Available stock as 10, we will then know that you have 300 meters of cable available. => Optional (defaults to 1).

  • Unit of measurement (Unit): See Unit of measurement (quantity). Needs to be one of: Kilogram, Meter, Liter, Piece. => Optional (defaults to "Piece").

What if I have multiple inventories?

We currently only support one inventory (that means you can only have one row per IPN in the csv). You can either add your different inventories together in the available and total stock or choose the most important inventory you want to integrate with Luminovo.

What is the correct numbering format?

Please make sure to use only , or . for the decimal separator. A thousand separator is not supported. This is particularly relevant for columns such as (Unit price (Amount)) Below is an example:

✅ Supported number format

1234,56
1234.56

❌ Unsoported number format:

1.234,56
1,234.56

Guidelines for the .json template - Off-the-shelf parts

Filename

Please name your .json as luminovo_tenantname_parts.json where tenantname is the first part of the URL you use to access Luminovo. So if you access Luminovo under https://ems.luminovo.ai please name your file luminovo_ems_parts.json.

.json format

The file should use UTF-8 encoding. Especially quote characters within the value itself should be escaped using a backslash. For example, the value Wire 3/8" should appear in the file as "Wire 3/8\\"".

When you do not have a value for an optional key, omit the key/value pair entirely, instead of specifying it as "key": null.

Unless explicitly marked as Required all values are optional.

Unless explicitly marked otherwise, all values should be provided as strings.

The file is expected to contain an array of part objects with the following structure.

Part object structure

  • "mpn": The manufacturer part number. Required

  • "manufacturer": The manufacturer of the part. Required. The combination of mpn and manufacturer should be unique across the entire file.

  • "alias": Aliases for the MPN**.** An array of strings.

  • "description": A description for the part.

  • "part_type": The type of the part.

  • "se_id": If your part data is coming from Silicon Expert, you can provide their part ID here.

  • "crosses": Alternative parts for the part. Ignored. An array of objects with the following structure

    • "CrossPartNumber": the MPN for the alternative part

    • "CrossManufacturer": the manufacturer of the alternative part

    • "CrossType":

    • "Comment": A comment about the alternative

  • "package": The package for the part. ****An object with the following structure

    • "name": The package name (e.g. "0402"). For SMT packages like "0603" we will assume it is given in the imperial system (measured in thous/inches). Add it as "0603mm" if it is given in the metric system (measured in mm)

    • "mounting": The mounting (e.g. "SMT").

    • "pin_pitch": The pin pitch.

    • "length_in_mm": The length of the package in mm. Ignored

    • "width_in_mm": The width of the package in mm. Ignored

    • "height_in_mm": The height of the package in mm. Ignored

    • "thread_size": The size of the thread. Ignored

    • "number_of_pins": The number of pins. Can be a String or Number.

  • "compliance": Compliance information for the part**.** An object with the following structure

    • "eu_rohs": RoHS information for the part**.** If provided, it must be a value from RoHS compliance status (see below).

    • "aec_qualified": Not parsed yet.

    • "contains_svhc": If provided, it must be either "yes" or "no". See REACH compliance status for more information.

    • "svhc_exceed_threshold_limit": If provided, it must be either "yes" or "no". See REACH compliance status for more information.

  • "lifecycle": Not parsed yet.

    • "status": The status.

    • "estimated_eol_date": Year of estimated end of life.

    • "last_time_buy_date": The date the part can be bought the last time.

  • "datasheet_url": A URL for the part’s datasheet.

  • "part_type": The type of the part.

  • "technical_properties": The technical properties of the part. An object of the following structure

    • "resistance_value": We recognise values in the form of "x mΩ" or "x m" for milliohms, "x Ω" or "x" for ohms, "x kΩ" or "x k" for kilo ohms, "x MΩ" or "x M" for mega ohms and "x GΩ" or "x G" for gigaohms. Fractional values must be provided with a decimal point (e.g. 1.5).

    • "resistance_range": Ignored

    • "inductance": Ignored

    • "capacitance_value": We recognise values in the form of "x pF" for picofarads, "x nF" for nanofarads, "x uF" or "x µF" for microfarads, "x mF" for microfarads and "x F" for farads. Fractional values must be provided with a decimal point (e.g. 1.5).

    • "dielectric_type": We only support ceramic capacitors at the moment. We recognise the following types:

      "X8R" "C0G" "NP0" "X7R" "X5R" "X8L" "Y5V" "X6S" "Z5U" "X7S" "Y5U" "C0H" "Y5R" "Y5P" "Y5T" "U2J" "Z5V" "Z5P" "K4000" "X6T" "X7T" "Z5F"
    • "power_rating": We recognise values in the form of "x mW" for milliwatts, "x W" for watts, "x kW" for kilowatts and "x MW" for megawatts. Franctional values must be provided with a decimal point (e.g. 1.5).

    • "technology": Ignored

    • "temperature_coefficient": We recognise values in the form of x ppm/°C or xppm/°C where x is the temperature coefficient value. Franctional values must be provided with a decimal point (e.g. 1.5).

    • "tolerance": We recognise relative tolerances and absolute tolerances. Relative tolerances must include a percentage sign (e.g. "1%"). Absolute tolerances are only considered in relation to a given capacitance_value or resistance_value and must include units. For example: Given "tolerance": "5Ω" and "resistance_value": "50Ω" we compute a tolerance value of 10%. Franctional values must be provided with a decimal point (e.g. 1.5).

    • "voltage_rating": We recognise values in the form of "x mV" for millivolts, "x V" for volts, "x kV" for kilovolts and "x MV" for megavolts. Franctional values must be provided with a decimal point (e.g. 1.5).

RoHS compliance status

We lowercase the given value and perform the following mapping:

  • "yes" ⇒ Compliant

  • "no" ⇒ Non-Compliant

  • "yes with exemption" ⇒ Compliant with exemption

  • "not required" or "nr" ⇒ Not required

  • "unknown" ⇒ Unknown

All other values will result in an Unkown RoHS compliance status.

REACH compliance status

We calculate the REACH compliance status based on the values of the two keys "contains_svhc" and "svhc_exceed_threshold_limit". The following logic applies

  • If "svhc_exceed_threshold_limit": "Yes" ⇒ Non-Compliant (regardless of the value for "contains_svhc")

  • If "svhc_exceed_threshold_limit": "No" ⇒ Compliant (regardless of the value for "contains_svhc")

  • For other values of "svhc_exceed_threshold_limit" (or if not provided):

    • If "contains_svhc": "yes" ⇒ Unknown

    • If "contains_svhc": "no" ⇒ Compliant

    • Otherwise ⇒ Unknown

Have a look at our templates below to see examples.

To integrate your data, you need to follow these steps:

1. Export your data into a .csv or .json file that follows our template.

You might have to mix and match some of your data from different locations in your ERP to match the columns we need. Fortunately, most ERP systems support export functionality like this out-of-the-box. Contact your ERP expert or partner to set this up.

2. Upload your .csv or .json files to our servers.

Most of the steps outlined here should probably be done by an IT expert within your company. But don't worry: the steps are easy and all you need to know is how to run a command on the command line.

Step 2.1 Install AzCopy

To upload the files to our servers, you will use Microsoft's AzCopy command line tool (installation instructions can be found here). It is available for Windows, macOS and Linux. We will create an Azure account with the right privileges to upload the data to our servers.

Step 2.2 Request Credentials From Us

To login, you will obtain credentials of the following form from us:

{ 
"appId": "ed1cf692-a353-4654-a4e6-f7b208246a09",
"displayName": "tenantname-erp",
"password": "pjP.x5~eRvP-5y2dbn0m4D62SIsabOg9Jk",
"tenant": "b063570f-c267-43be-83fb-c30a28eab1a2"
}

Step 2.3 Set Environmental Variables

Before running the upload you will need to set the following environment variables in your script.

First of all, make sure to replace:

  • AZCOPY_SPA_APPLICATION_ID with your appId

  • AZCOPY_SPA_CLIENT_SECRET with your password

  • AZCOPY_TENANT_ID with your tenant

Then, depending on your system, open your command-line app:

On MacOs or Linux enter in the Terminal app:

export "AZCOPY_AUTO_LOGIN_TYPE=SPN"
export "AZCOPY_SPA_CLIENT_SECRET=YOUR_PASSWORD"
export "AZCOPY_SPA_APPLICATION_ID=YOUR_APP_ID"
export "AZCOPY_TENANT_ID=YOUR_TENANT_ID"

On Windows, open command prompt (CMD) and enter:

set "AZCOPY_AUTO_LOGIN_TYPE=SPN"
set "AZCOPY_SPA_CLIENT_SECRET=YOUR_PASSWORD"
set "AZCOPY_SPA_APPLICATION_ID=YOUR_APP_ID"
set "AZCOPY_TENANT_ID=YOUR_TENANT_ID"

In Windows PowerShell (for more advanced users) the setting of variables should look as follows:

$env:AZCOPY_AUTO_LOGIN_TYPE="SPN"
$env:AZCOPY_SPA_CLIENT_SECRET="YOUR_PASSWORD"
$env:AZCOPY_SPA_APPLICATION_ID="YOUR_APP_ID"
$env:AZCOPY_TENANT_ID="YOUR_TENANT_ID"

Step 2.4 Upload your files

To upload your .csv or .json files, all you need to do is execute the following command. Make sure you substitute tenantname (in the filename and at the end of the command) with the first part of the URL you use to login to Luminovo (usually your company name in lowercase letters).

  • For exampe, if you access Luminovo through https://amazing-ems.luminovo.com, replace tenantname-erp --> amazing-ems-erp

Be aware to use the full path to your files! Replace luminovo_tenantname_ipns.csv and luminovo_tenantname_inventory.csv with the full paths to your files.

After replacing the tenantname-erp and adjusting the paths to your files, in the command line interface execute:

azcopy copy luminovo_tenantname_ipns.csv "https://lumiquoteproderp.blob.core.windows.net/tenantname-erp"
azcopy copy luminovo_tenantname_inventory.csv "https://lumiquoteproderp.blob.core.windows.net/tenantname-erp"

3. Set up a daily export and upload job

Every night, starting at 3 am, we will check for newly uploaded files and sync your data into Luminovo's database. Ideally, you automate the export and upload steps and set up a job that uploads your data to Luminovo every night (before 3 am so that it lands on our servers before we run the sync). We suggest that you check with your ERP provider if your ERP supports scheduling an automated script, or otherwise you can set up an automated job yourself (e.g. via cron).

Common issues in the file format and recommended solutions

1. Column headers

  • The column header row is often missing in the .csv or the columns are named incorrectly.

    • The .csv must include a header row as indicated in the sample files.

    • The column headers should be named exactly as described in the templates. This also goes for capitalisation and spaces as well as special characters such as brackets (correct example would be: "Standard lead time (Days)")

2. UTF-8 encoding

  • The .csv must be UTF-8 encoded.

  • UTF-8 encoding should be selected when exporting the data from the ERP system.

  • Other encodings, such as ISO 8859-1/latin-1, will cause errors during the import if special characters are present in the file(s).

3. Using double quotes in values

For .csv files:

  • We often see that values are not escaped using double quotes.

  • We recommend to always use double quotes if the value includes a special character such as , or ;.

    • Examples:

      • E.g. the following value should look like this: "Manufacturer, Inc.".

      • If the value itself includes double quotes, it must be escaped by using double quotes. For example, the value Wire 3/8" should appear in the file as ...,"Wire 3/8""",....

      • The valueLCD 19" monitorshould appear in the file as...,"LCD 19"" monitor",....

For .json files:

  • If the value itself includes double quotes, it must be escaped by using a backslash \\ quotes. For example, the value Wire 3/8" should appear in the file as "Wire 3/8\\"".

4. Invalid values

  • Only English values are allowed in the columns "Packaging" and "Unit of measurement (Unit)" , e.g. Reel or Piece.

  • The column "Manufacturer" must only include the manufacturer name. Please avoid using distributor names in this column.

  • The column "Unit of measurement (Quantity)" is only to be filled in if the stock is measured in parts or multiples of the "Unit of measurement (Unit)" .

  • The column "Standard lead time (Days)"is only to be populated if a reliable value for the lead time is known.

✉️ Do you have questions related to your ERP integration?

Reach out to our Product Partnerships Manager, Igor Rhzin, under [email protected].

File templates

Did this answer your question?