# Importing .xlsx product template

**URL:** <https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138>\
**Category:** PM modules\
**Created:** [December 12, 2022, 5:33am UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138 "2022-12-12T05:33:46Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [December 12, 2022, 5:33am UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/1 "2022-12-12T05:33:46Z")

</div>

Ive gone through the 5 stages of importing an .xlsx products template with all the information required for the new products listing. I started out by downloading the dolibarr product template then I swapped in a couple of rows with product and description and amount and so on. Then I imported that file and everything goes well until I get to the preview import where it tell’s me of a number of errors (see below).

**2 errors found**

- Line 1

> Field **3** : ‘_Type (p.fk\_product\_type)__’ does not match regex rule **\[1\]$**  
> Field **4** : 'For sale (p.tosell)_’ does not match regex rule **\[2\]$**  
> Field **5** : ‘_For purchase (p.tobuy)_\*’ does not match regex rule **\[3\]$**  
> Field **9** : ‘**Country code (p.fk\_country)**’ is not a value found in field **code** of **Countries**  
> Field **19** : ‘**Weight unit (p.weight\_units)**’ is not a value found in field **scale** of **Measuring Units**  
> Field **21** : ‘**Length unit (p.length\_units)**’ is not a value found in field **scale** of **Measuring Units**  
> Field **23** : ‘**Width unit (p.width\_units)**’ is not a value found in field **scale** of **Measuring Units**  
> Field **25** : ‘**Height unit (p.height\_units)**’ is not a value found in field **scale** of **Measuring Units**  
> Field **27** : ‘**Surface unit (p.surface\_units)**’ is not a value found in field **scale** of **Measuring Units**  
> Field **29** : ‘**Volume unit (p.volume\_units)**’ is not a value found in field **scale** of **Measuring Units**  
> Field **31** : ‘**Nature of product (raw/manufactured) (p.finished)**’ is not a value found in field **code** of **Nature of product**  
> Field **31** : ‘**Nature of product (raw/manufactured) (p.finished)**’ does not match regex rule **\[4\]$**  
> Field **36** : ‘**PriceBaseType (p.price\_base\_type)**’ does not match regex rule **HT TTC**  
> Field **38** : ‘**Creation date (p.datec)**’ does not match regex rule **\[5\][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]$**  
> Field **40** : ‘**Default warehouse (p.fk\_default\_warehouse)**’ is not a **Warehouse** existing ref

- Line 2

> Field **3** : ‘**0 (product) / 1 (service)**’ does not match regex rule **\[6\]$**  
> Field **4** : ‘**0 (not for sale to customer, eg. raw material) / 1 (for sale)**’ does not match regex rule **\[7\]$**  
> Field **5** : ‘**0 (not for purchase from supplier, eg. virtual product) / 1 (for purchase)**’ does not match regex rule **\[8\]$**  
> Field **31** : ‘**0 (raw material) / 1 (finished goods), matches field “code” in dictionary table “llxlt\_c\_product\_nature”**’ is not a value found in field **code** of **Nature of product**  
> Field **31** : ‘**0 (raw material) / 1 (finished goods), matches field “code” in dictionary table “llxlt\_c\_product\_nature”**’ does not match regex rule **\[9\]$**

* * *

1. 0 1 

2. 0 1 

3. 0 1 

4. 0 1 

5. 0-9 

6. 0 1 

7. 0 1 

8. 0 1 

9. 0 1

---

<div class="post-metadata">

**Author:** ![priojk](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/priojk/32/6121_2.png) [@priojk](https://www.dolibarr.org/forum/u/priojk)\
**Post date:** [December 12, 2022, 7:18am UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/2 "2022-12-12T07:18:37Z")

</div>

The error messages are a bit technical, but they say that the data you provide in the mentioned fields do not match the expected format. E.g. Field 3: It must have a value of 0 or 1 and nothing else. So the first step would be to check, if the entries are correct and do not contain additional symbols in the fields like quotation marks etc.

---

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [December 12, 2022, 9:10pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/3 "2022-12-12T21:10:01Z")

</div>

I agree it does lead you to believe that there’s code that needs to be input in order for things to parse properly or import properly. I’ve done both ways with just a 0 in one column and maybe 1 in another. Doesn’t matter I get the same results. There is no other data in the cell besides the 0 and or the 1 and I still get the same regex errors. It’s really confusing why. I export a produit xlsx template out with all the desired headers then modify that template with data input 0’s and 1’s included and then import, you would think it would transition smoothly but something is not right. Here’s a sample of the first few lines and columns that I’m trying to import.

| Ref.\* (p.ref) | Label\* (p.label) | Type\* (p.fk\_product\_type) | For sale\* (p.tosell) | For purchase\* (p.tobuy) |
| --- | --- | --- | --- | --- |
| 316\_SS\_BARS | GASKET | 0 | 0 | 1 |
| A055\_NBR\_CHANNEL | GASKET | 0 | 0 | 1 |

---

<div class="post-metadata">

**Author:** ![ThinkOli](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@ThinkOli](https://www.dolibarr.org/forum/u/ThinkOli)\
**Post date:** [December 19, 2022, 11:46am UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/4 "2022-12-19T11:46:15Z")

</div>

Did you try with .csv instead of .xlsx? I gave up on importing Excel. Since I have been using .csv solely, I have been able to get all issues resolved.

---

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [December 19, 2022, 1:58pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/5 "2022-12-19T13:58:17Z")

</div>

I can try again with CSV I don’t remember my first few attempts. Will post my findings.

---

<div class="post-metadata">

**Author:** ![ThinkOli](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@ThinkOli](https://www.dolibarr.org/forum/u/ThinkOli)\
**Post date:** [December 21, 2022, 4:01pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/6 "2022-12-21T16:01:59Z")

</div>

Hi Greg,

I took your data put it into the sample file and generated a .csv file from it:

```auto
Nummer* (p.ref);Bezeichnung* (p.label);Typ* (p.fk_product_type);Zu verkaufen* (p.tosell);zum Einkaufen* (p.tobuy);Beschreibung (p.description);Öffentliche URL (p.url);Zolltarifnummer (p.customcode);Länderkennung (p.fk_country);Buchhaltungscode (Verkauf) (p.accountancy_code_sell);Buchungscode (Verkauf innerhalb der Gemeinschaft) (p.accountancy_code_sell_intra);Kontierungscode (Verkauf Export) (p.accountancy_code_sell_export);Rechnungscode (Einkauf) (p.accountancy_code_buy);Buchungscode (Einkäufe innerhalb der Gemeinschaft) (p.accountancy_code_buy_intra);Buchungscode (Einkauf Import) (p.accountancy_code_buy_export);Anmerkung (öffentlich) (p.note_public);Anmerkung (privat) (p.note);Weight (p.weight);Einheit Gewicht (p.weight_units);Length (p.length);Längeneinheit (p.length_units);Width (p.width);Breiteneinheit (p.width_units);Height (p.height);Höheneinheit (p.height_units);Surface (p.surface);Flächeneinheit (p.surface_units);Volume (p.volume);Einheit Volumen (p.volume_units);Dauer (p.duration);Art des Produkts (roh / hergestellt) (p.finished);Verkaufspreis (ohne Steuern) (p.price);Mindest-Verkaufspreis (p.price_min);Verkaufspreis (inkl. USt.) (p.price_ttc);Mindest-Verkaufspreis (inkl. MwSt.) (p.price_min_ttc);PriceBaseType (p.price_base_type);Steuersatz (p.tva_tx);Erstellungsdatum (p.datec);CostPrice (p.cost_price);Standardlager (p.fk_default_warehouse);Chargen-/Serien-Nr. benutzen (p.tobatch);Mindestbestand vor Warnung (p.seuil_stock_alerte);Gewichteter Warenwert (p.pmp);Gewünschter Lagerbestand (p.desiredstock);Barcode (p.barcode);ME (extra.unit);Bestellmenge (extra.order_volume);Lieferantenartikelnummer (extra.supplier_code);MPN (extra.manufacturer_partnumber);Hersteller (extra.manufacturer);Lieferant (extra.supplier)
ref:PREF123456;Product name in default language;0 (product) / 1 (service);0 (not for sale to customer/ eg. raw material) / 1 (for sale);0 (not for purchase from supplier/ eg. virtual product) / 1 (for purchase);Product description in default language;link to product (no https);customs code;FR;;;;;;;a public note (free text);a private note (free text);;kg;;m;;m;;m;;m2;;m3;eg. 365d/12m/1y;"0 (raw material) / 1 (finished goods)/ matches field ""code"" in dictionary table ""llx_c_product_nature""";100;100;110;110;HT (show/use price excl. tax) / TTC (show/use price incl. tax);10;21.12.2022;90;;0 (don't use) / 1 (use batch) / 2 (use serial number);;0;;;ME;Bestellmenge;Lieferantenartikelnummer;MPN;Hersteller;Lieferant
316_SS_BARS;GASKET;0;0;1;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;0;;0;;;;;;;;
A055_NBR_CHANNEL;GASKET;0;0;1;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;0;;0;;;;;;;;

```

Delimiter “;”, skip 2 headlines (i.e. start at line 3)

I had to add information to column 41 (tobatch) to specify that no variants are to be used and 43 (pmp) to provide the cost. This is information that is not flagged mandatory, but nevertheless required for the import.  
With that, I had no issues importing such csv - no errors reported.

---

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [January 5, 2023, 9:35pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/7 "2023-01-05T21:35:10Z")

</div>

I’m having similar issues with importing an .csv file. I see now from your example that it’s better to reference the import file as a comma-delimited in notepad. However I continue to get upwards of 100’s of errors starting on line one which is the header line. I recently exported a csv file out of the database and subsequently import that file to my database to see if I get the same errors while running the import simulator. I do get errors on that also. Any ideas would be appreciated.

---

<div class="post-metadata">

**Author:** ![ThinkOli](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@ThinkOli](https://www.dolibarr.org/forum/u/ThinkOli)\
**Post date:** [January 5, 2023, 11:02pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/8 "2023-01-05T23:02:07Z")

</div>

Well, one step after the other:

- Errors on the header line means you missed to skip it as I had suggested. (That already was obvious on the errors you listed above for line 1 and 2.)  
 → Step 5: increase the counter from 1 to 3 if your data starts in line 3
- You try to import products.  
 → Select ‘Product’ as the key on the line below.  
With that fixed, please report further errors you might get. So far, I see only handling issues, no real content/technical issues yet.

---

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [January 6, 2023, 4:54am UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/9 "2023-01-06T04:54:57Z")

</div>

Ok it’s looking like I’m getting places. I’ve started the data import on line 3 as you say and now the error message I get is this:

620 errors found

- Line 3

> Data truncated for column ‘tobatch’ at row 1

- Line 4

> Data truncated for column ‘tobatch’ at row 1

- Line 5

> Data truncated for column ‘tobatch’ at row 1

- Line 6

> Data truncated for column ‘tobatch’ at row 1

- Line 7

> Data truncated for column ‘tobatch’ at row 1

- Line 8

> Data truncated for column ‘tobatch’ at row 1

- Line 9

> Data truncated for column ‘tobatch’ at row 1

---

<div class="post-metadata">

**Author:** ![ksar](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/ksar/32/514_2.png) [@ksar](https://www.dolibarr.org/forum/u/ksar)\
**Post date:** [January 6, 2023, 8:30am UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/10 "2023-01-06T08:30:33Z")

</div>

Hello,

What do you have in this column tobatch?

---

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [January 6, 2023, 2:23pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/11 "2023-01-06T14:23:35Z")

</div>

I have a reference column with text.

Ref.\* (p.ref)  
ref:PREF123456789123456789123456789123456789  
A085\_EPDM\_CHANNEL  
A085\_EPDM\_RUBBER\_RINGS  
A085\_EPDM\_FULL  
A085\_NBR\_FULL  
A085\_EPDM\_OEM\_CHANNEL  
A085\_EPDM\_OEM\_FULL  
A10B\_EPDM\_'A’_FLOW  
A10B\_EPDM_’B’_FLOW  
A10B\_NBR_’A’FLOW

I am starting from line 3 "A085\_EPDM\_CHANNEL. I added extra character in row 2 thinking that that would force a longer text string.

---

<div class="post-metadata">

**Author:** ![ThinkOli](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@ThinkOli](https://www.dolibarr.org/forum/u/ThinkOli)\
**Post date:** [January 6, 2023, 6:07pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/12 "2023-01-06T18:07:48Z")

</div>

What are the other 49 columns filled with?

---

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [January 6, 2023, 6:40pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/13 "2023-01-06T18:40:39Z")

</div>

The other columns are filled as such:

A085-EPDM-CHANNEL,GASKET,0,0,1,US,m,m,m,m2,m3,100,100,110,110,HT,5,0`,50,0,100,, A085-EPDM-RUBBER_RINGS,GASKET,0,0,1,,,,US,,,,,,,,,,,,m,,m,,m,,m2,,m3,,,100,100,110,110,HT,5,,,,0`,50,0,100,  
A085-EPDM-FULL,GASKET,0,0,1,US,m,m,m,m2,m3,100,100,110,110,HT,5,0`,50,0,100,, A085_NBR_FULL,GASKET,0,0,1,,,,US,,,,,,,,,,,,m,,m,,m,,m2,,m3,,,100,100,110,110,HT,5,,,,0`,50,0,100,  
A085\_EPDM\_OEM\_CHANNEL,GASKET,0,0,1,US,m,m,m,m2,m3,100,100,110,110,HT,5,0`,50,0,100,, A085_EPDM_OEM_FULL,GASKET,0,0,1,,,,US,,,,,,,,,,,,m,,m,,m,,m2,,m3,,,100,100,110,110,HT,5,,,,0`,50,0,100,  
A10B\_EPDM\_'A’_FLOW,GASKET,0,0,1,US,m,m,m,m2,m3,100,100,110,110,HT,5,0`,50,0,100,, A10B_EPDM_'B'_FLOW,GASKET,0,0,1,,,,US,,,,,,,,,,,,m,,m,,m,,m2,,m3,,,100,100,110,110,HT,5,,,,0`,50,0,100,  
A10B\_NBR_’A’FLOW,GASKET,0,0,1,US,m,m,m,m2,m3,100,100,110,110,HT,5,0`,50,0,100,, A10B_NBR'B'_FLOW,GASKET,0,0,1,,,,US,,,,,,,,,,,,m,,m,,m,,m2,,m3,,,100,100,110,110,HT,5,,,,0`,50,0,100,

Thank’s Greg

---

<div class="post-metadata">

**Author:** ![ThinkOli](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@ThinkOli](https://www.dolibarr.org/forum/u/ThinkOli)\
**Post date:** [January 8, 2023, 5:34pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/14 "2023-01-08T17:34:35Z")

</div>

Hi Greg,

you have a lot of extra information that you should add later, when everything else is clean. So, this imported without errors:

```auto
A085-EPDM-CHANNEL,GASKET,0,0,1,US,,,,,,,,,,HT,,,,,,,,,,,,,,,,,,,,,,,,,0,,0,,
A085-EPDM-FULL,GASKET,0,0,1,US,,,,,,,,,,HT,,,,,,,,,,,,,,,,,,,,,,,,,0,,0,,
A085_EPDM_OEM_CHANNEL,GASKET,0,0,1,US,,,,,,,,,,HT,,,,,,,,,,,,,,,,,,,,,,,,,0,,0,,
A10B_EPDM_'A’FLOW,GASKET,0,0,1,US,,,,,,,,,,HT,,,,,,,,,,,,,,,,,,,,,,,,,0,,0,,
A10B_NBR’A’FLOW,GASKET,0,0,1,US,,,,,,,,,,HT,,,,,,,,,,,,,,,,,,,,,,,,,0,,0,,

```

Which has (AS I SUGGESTED BEFORE) the _p.tobatch_ (41) and _p.pmp_ (43) fields filled. When you have that right and it imports correctly you can start adding further information like weight, height, length, notes, cost, whatsoever.

Good luck! Try importing my data first - without any deviation.

---

<div class="post-metadata">

**Author:** ![bobgregmac](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/b/e47c2d/32.png) [@bobgregmac](https://www.dolibarr.org/forum/u/bobgregmac)\
**Post date:** [January 8, 2023, 10:40pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/15 "2023-01-08T22:40:04Z")

</div>

I did as you suggested and was able to import all the data. I’m very happy you stuck with me on this one. I first made a .csv file with your populated fields and tried importing it. I received an error telling me that field 45 could not be null. I corrected that error and then tried again with no error’s. I then transpose all the other lines in my original .csv file to the one that I created with your help and while running an import simulation found a bunch of duplicates. I deleted those duplicate lines from the .csv and after that everthing imported fine.

Thanks,  
Greg

---

<div class="post-metadata">

**Author:** ![ThinkOli](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/t/8797f3/32.png) [@ThinkOli](https://www.dolibarr.org/forum/u/ThinkOli)\
**Post date:** [January 9, 2023, 6:29pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/16 "2023-01-09T18:29:27Z")

</div>

Very good. Congrats! Glad I could help.

Oliver

---

<div class="post-metadata">

**Author:** ![Regiofixx](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/r/71e660/32.png) [@Regiofixx](https://www.dolibarr.org/forum/u/Regiofixx)\
**Post date:** [September 29, 2025, 4:04pm UTC](https://www.dolibarr.org/forum/t/importing-xlsx-product-template/23138/17 "2025-09-29T16:04:06Z")

</div>

Hello Dolibarr community,

I’m new here and have Dolibarr running on a Windows mini computer for extensive testing.

I don’t understand the automatic item numbering:  
If I manually add an item/product, Dolibarr automatically creates a sequential item number. Great!

If I import the product list from Excel, do I have to enter an article number myself or does Dolibarr do it automatically?

I don’t understand how; I couldn’t find anything here.

I look forward to your help. Thank you.
