Imported CSV results in missing items

[RobolabUACJ]

Code of Conduct

• I agree to follow this project's Code of Conduct

Is there an existing issue for this?

• I have searched the existing issues

GLPI Version

10.0.16

Plugin version

2.14.0

Bug description

I have a backup of a previous GLPI installation that I wanted to import into the newest GLPI in another machine.
I exported the old GLPI inventory as a CSV file with semicolon used as separator.

I used the gif in the main repository page to import the CSV into this new GLPI installation.
However, upon trying to import the CSV, I end up with a lot of items missing.
The original item list had 1508 items, while after the CSV import the list is only around 1.1k items, with a lot missing or others being duplicated.

I tried saving the CSV with commas as separators and changing the separator in the plugin too, tried some other import options but nothing works so far. I always end up with missing items, and sometimes it's different ones being missing on different imports.

Here's the original exported CSV from my older glpi install:
glpiOriginal.csv

I'm not sure what I could be missing to properly import all 1508 items properly, but any help towards making this import work would be greatly appreciated. It's worth mentioning that the import log shows no errors whatsoever.

Let me know if there's anything else needed.

Relevant log output

[2024-11-04 23:06:07] glpisqllog.ERROR: DBmysql::doQuery() in /var/www/localhost/htdocs/glpi/src/DBmysql.php line 403
  *** MySQL query error:
  SQL: SELECT *
                    FROM `glpi_peripherals` WHERE 1  AND ( `glpi_peripherals`.`entities_id` = '0'  )   AND `is_deleted` = '0'  AND `is_template` = '0'  AND `name`='Etiquetadora de CD's' AND `comment`='.'
  Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's' AND `comment`='.'' at line 2
  Backtrace :
  src/DBmysql.php:352                                DBmysql->doQuery()
  ...injection/inc/commoninjectionlib.class.php:1926 
  ...injection/inc/commoninjectionlib.class.php:1470 PluginDatainjectionCommonInjectionLib->dataAlreadyInDB()
  ...ainjection/inc/peripheralinjection.class.php:92 PluginDatainjectionCommonInjectionLib->processAddOrUpdate()
  plugins/datainjection/inc/engine.class.php:154     PluginDatainjectionPeripheralInjection->addOrUpdateObject()
  ...datainjection/inc/clientinjection.class.php:276 PluginDatainjectionEngine->injectLine()
  ...datainjection/inc/clientinjection.class.php:226 PluginDatainjectionClientInjection::processInjection()
  ...datainjection/front/clientinjection.form.php:45 PluginDatainjectionClientInjection::showInjectionForm()
  {"user":"2@samurai"} 
[2024-11-04 23:07:15] glpisqllog.ERROR: DBmysql::doQuery() in /var/www/localhost/htdocs/glpi/src/DBmysql.php line 403
  *** MySQL query error:
  SQL: SELECT *
                    FROM `glpi_peripherals` WHERE 1  AND ( `glpi_peripherals`.`entities_id` = '0'  )   AND `is_deleted` = '0'  AND `is_template` = '0'  AND `name`='NI Vision Development Module (1 libro y 3 CD's)' AND `comment`='Discos'
  Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's)' AND `comment`='Discos'' at line 2
  Backtrace :
  src/DBmysql.php:352                                DBmysql->doQuery()
  ...injection/inc/commoninjectionlib.class.php:1926 
  ...injection/inc/commoninjectionlib.class.php:1470 PluginDatainjectionCommonInjectionLib->dataAlreadyInDB()
  ...ainjection/inc/peripheralinjection.class.php:92 PluginDatainjectionCommonInjectionLib->processAddOrUpdate()
  plugins/datainjection/inc/engine.class.php:154     PluginDatainjectionPeripheralInjection->addOrUpdateObject()
  ...datainjection/inc/clientinjection.class.php:276 PluginDatainjectionEngine->injectLine()
  ...datainjection/inc/clientinjection.class.php:226 PluginDatainjectionClientInjection::processInjection()
  ...datainjection/front/clientinjection.form.php:45 PluginDatainjectionClientInjection::showInjectionForm()
  {"user":"2@samurai","mem_usage":"68.160\", 21.95Mio)"}

Page URL

No response

Steps To reproduce

1. Follow the import procedure detailed in the GIF image.
2. Use the provided CSV in the issue report for the Model creation
3. Use the provided CSV for the import
4. Imported items is below the number of the ones seen in the actual CSV.

Your GLPI setup information

Operating system: Linux gentoo 6.6.57-gentoo #​3 SMP PREEMPT_DYNAMIC Wed Oct 30 13:44:15 CST 2024 x86_64
PHP 8.2.24 apache2handler (Core, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, apache2handler, bcmath, bz2, calendar,
	ctype, curl, date, dba, dom, exif, fileinfo, filter, ftp, gd, gettext, gmp, hash, iconv, imap, intl, json, ldap, libxml,
	mbstring, mysqli, mysqlnd, openssl, pcre, pdo_mysql, pdo_pgsql, pdo_sqlite, pgsql, posix, random, session, sockets, sodium,
	sqlite3, standard, sysvmsg, sysvsem, sysvshm, tokenizer, xml, xmlreader, xmlwriter, xsl, zip, zlib)
Setup: max_execution_time="30" memory_limit="128M" post_max_size="8M" safe_mode="" session.save_handler="files"
	upload_max_filesize="2M" disable_functions="" 
Software: Apache (Apache Server at localhost Port 80
)
	Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:132.0) Gecko/20100101 Firefox/132.0
Server Software: Gentoo Linux mysql-8.0.36
	Server Version: 8.0.36
	Server SQL Mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
	Parameters: glpi@localhost/robolab
	Host info: Localhost via UNIX socket
	
PHP version (8.2.24) is supported.PHP version (8.2.24) is supported.
Sessions configuration is OK.Sessions configuration is OK.
Allocated memory is sufficient.Allocated memory is sufficient.
mysqli extension is installed.mysqli extension is installed.
Following extensions are installed: dom, fileinfo, filter, libxml, json, simplexml, xmlreader, xmlwriter.Following extensions are installed: dom, fileinfo, filter, libxml, json, simplexml, xmlreader, xmlwriter.
curl extension is installed.curl extension is installed.
gd extension is installed.gd extension is installed.
intl extension is installed.intl extension is installed.
zlib extension is installed.zlib extension is installed.
The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.The constant SODIUM_CRYPTO_AEAD_XCHACHA20POLY1305_IETF_NPUBBYTES is present.
Database engine version (8.0.36) is supported.Database engine version (8.0.36) is supported.
No files from previous GLPI version detected.No files from previous GLPI version detected.
The log file has been created successfully.The log file has been created successfully.
Write access to /var/www/localhost/htdocs/glpi/files/_cache has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_cron has been validated. Write access to /var/www/localhost/htdocs/glpi/files has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_dumps has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_graphs has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_lock has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_pictures has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_plugins has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_rss has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_sessions has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_tmp has been validated. Write access to /var/www/localhost/htdocs/glpi/files/_uploads has been validated.Write access to /var/www/localhost/htdocs/glpi/files/_cache has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_cron has been validated.
Write access to /var/www/localhost/htdocs/glpi/files has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_dumps has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_graphs has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_lock has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_pictures has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_plugins has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_rss has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_sessions has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_tmp has been validated.
Write access to /var/www/localhost/htdocs/glpi/files/_uploads has been validated.

Web server root directory configuration is not safe as it permits access to non-public files. See installation documentation for more details.Web server root directory configuration is not safe as it permits access to non-public files. See installation documentation for more details.
The following directories should be placed outside "/var/www/localhost/htdocs/glpi": ‣ "/var/www/localhost/htdocs/glpi/files" ("GLPI_VAR_DIR") You can ignore this suggestion if your web server root directory is "/var/www/localhost/htdocs/glpi/public".The following directories should be placed outside "/var/www/localhost/htdocs/glpi":
‣ "/var/www/localhost/htdocs/glpi/files" ("GLPI_VAR_DIR")
You can ignore this suggestion if your web server root directory is "/var/www/localhost/htdocs/glpi/public".
PHP directive "session.cookie_httponly" should be set to "on" to prevent client-side script to access cookie values.PHP directive "session.cookie_httponly" should be set to "on" to prevent client-side script to access cookie values.
OS and PHP are relying on 64 bits integers.OS and PHP are relying on 64 bits integers.
exif extension is installed.exif extension is installed.
ldap extension is installed.ldap extension is installed.
openssl extension is installed.openssl extension is installed.
Following extensions are installed: bz2, Phar, zip.Following extensions are installed: bz2, Phar, zip.
Zend OPcache extension is installed.Zend OPcache extension is installed.
Following extensions are installed: ctype, iconv, mbstring, sodium.Following extensions are installed: ctype, iconv, mbstring, sodium.
Write access to /var/www/localhost/htdocs/glpi/marketplace has been validated.Write access to /var/www/localhost/htdocs/glpi/marketplace has been validated.
Access to timezone database (mysql) is not allowed.Access to timezone database (mysql) is not allowed.

Anything else?

No response

[stonebuzz]

Hi @RobolabUACJ

You have a quote into name CD's

AND `name`='Etiquetadora de CD's' 

for a quick workaround, you can remove this quote (adadpt the name) the import should work fine

[RobolabUACJ]

Thank you for the reply, @stonebuzz
I removed the quotes from that instance, as well as all other instances that still have ' and " into them.
glpi2025.csv

However, it seems like the import is still only adding around 1.1k items, instead of the full 1508 from the list.
This time, there's no log at all within the _log folder outside of the normal cron.log.
Is there something else I could be missing?

[stonebuzz]

can you redo a test by doing two imports rather than one (on a large volume of data)?

[RobolabUACJ]

Just to be sure, you mean separating the main CSV into two CSV files and import the two separately?

[stonebuzz]

yes =)

[RobolabUACJ]

Tried splitting the items in half, with only 755 in one file and 753 in the other.
The first 755 import only added 640 items into the inventory, further attempts don't add any new item.

[RobolabUACJ]

I think I possibly found out why many items are missing after importing.
After manually reviewing almost all of 1508 original items against the imported ones, I noticed that some instances of one item that gets repeated several times and changes only the Inventory/Asset number, almost all but one of those entries get ignored.

Let's say I have Item X, and that item gets repeated 10 times, while those 10 same items only change in the Inventory/Asset number. All items get ignored when importing the CSV file, except for one, which tends to be either the first one or the last one from the same item.

Here's an example:

In the original CSV file you can see I have several Drivers GWESC25A listed, each one with an Inventory/Asset number of GWE-01 all the way up to GWE-12. However, in the imported CSV I only got GWE-12, 03, 08 and 09. It seems, from what I gathered that those four got imported since those specific ones had changed in either Locations or Comments.

If the same item has the same entries in all columns except for the Inventory/Asset number, they get ignored.
You can see that with the Driver ICS100 entry, which have the same data in all columns except I/A Number in the original file, and the import CSV only has the very last entry listed (DW-09).

[stonebuzz]

Can you provide the model configuration (first tab) and the configured mapping?

[RobolabUACJ]

Sure thing, here's both the Model config and the Mapping: