Importing .xlsx product template

Using my Dolibarr PM modules
1

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 ↩︎

2

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.

3

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
4

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.

5

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

6

Hi Greg,

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

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.

7

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.

8

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.
9

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

10

Hello,

What do you have in this column tobatch?

11

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.

12

What are the other 49 columns filled with?

13

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

14

Hi Greg,

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

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.

15

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

16

Very good. Congrats! Glad I could help.

Oliver