Date/time import is broken

[hatelamers]

Steps to reproduce

1. Create a table "test" in NC with 2 columns "Date When" (type Date and Time, date and time) and "Number X" (type Number, 0 decimals)
2. Create a file test.csv

Date When;Number X
2024-04-08T12:35:10.460Z;234

3. Import test.csv into "test" table
4. Impor results displayed: Found columns: 2, Matching columns: 2, Inserted rows: 1, Value parsing errors: 0
5. "Date When" cell in "test" table is blank
6. Change data row in test.csv to

"2024-04-08T12:35:10.460Z";234

7. Proceed with P3-5: same result
8. Change data in row test.csv to

1712579710;234

9. Proceed with P3-5: same result
10. Change data in row test.csv to

"8 Apr 2024, 12:35:10";234

11. Proceed with P3-5: same result
12. Change data in row test.csv to

8 Apr 2024, 12:35:10;234

13. Proceed with P3-5: same result
14. Change data in row test.csv to

2024-04-08 12:35:10;234

15. Proceed with P3-5: same result
16. Create text.xlsx from test.csv (Excel displays correct values)
17. Import text.xlsx into "test" table
18. Impor results displayed: Found columns: 2, Matching columns: 2, Inserted rows: 1, Value parsing errors: 0
19. "Date When" cell in "test" table is blank

Expected behavior

Date-time values formatted in industrywide standard form (like ISO 8601, Unix epoch) are recognized and parsed correctly, unrecognized formats are reported as parsing error.

Actual behavior

Date-time values generated by any other program and formatting tool cannot be imported, moreover Tables - although apparently incapable of import - doesn't report any errors in the process. Currently the only parseable (and exportable) format "2024-04-08 12:35" is invalid by all means and useless in data exchange as for missing time zone information.

Tables app version

0.6.6

Browser

Firefox 115.9.1 LSR

Client operating system

Windows

Operating system

Linux 5.15.0-101-generic x86_64

Web server

Apache

PHP engine version

PHP 8.1

Database

MariaDB

Additional info

No response

[affenbirne]

I'm also having trouble with the import of date/time data. Like @hatelamers I tried different formats. My guess would be that certain formats are recognized as correct, but looking at the code ImportService.php it seems that it's expecting excel date format, which is probably not recognized as a date format but rather a number.

Also excel format doesn't make sense, cause the export format is "2024-04-08 12:35" as @hatelamers pointed out.

[manmetz]

Date import also not working here. For testing I always did the same three steps:

1. Create a table with two columns (VAL1, VAL2)
2. enter some random data (foo, bar)
3. export it to csv
4. modify some values in the csv
5. import the modified csv

Case 1 - VAL1 [TEXT], VAL2 [TEXT]

Works fine

Case 2 - VAL1 [TEXT], VAL2[DATE]

Trying to import, Preview dialogue appears and looks good, but then in then when actually trying to import I get "Fehler bei der Zeilenerstellung 2". Log file output:

{"reqId":"***","level":3,"time":"***","remoteAddr":"***","user":"***","app":"tables","method":"POST","url":"/apps/tables/importupload/table/9","message":"Error while creating new row for import.","userAgent":"Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:131.0) Gecko/20100101 Firefox/131.0","version":"30.0.0.14","exception":{"Exception":"TypeError","Message":"floor(): Argument #1 ($num) must be of type int|float, string given","Code":0,"Trace":[{"file":"/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php","line":224,"function":"floor"},{"file":"/var/www/nextcloud/apps/tables/lib/Service/ImportService.php","line":365,"function":"excelToDateTimeObject","class":"PhpOffice\\PhpSpreadsheet\\Shared\\Date","type":"::"},{"file":"/var/www/nextcloud/apps/tables/lib/Service/ImportService.php","line":298,"function":"createRow","class":"OCA\\Tables\\Service\\ImportService","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/nextcloud/apps/tables/lib/Service/ImportService.php","line":256,"function":"loop","class":"OCA\\Tables\\Service\\ImportService","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/ImportController.php","line":114,"function":"import","class":"OCA\\Tables\\Service\\ImportService","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/Errors.php","line":22,"function":"OCA\\Tables\\Controller\\{closure}","class":"OCA\\Tables\\Controller\\ImportController","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/ImportController.php","line":112,"function":"handleError","class":"OCA\\Tables\\Controller\\ImportController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":208,"function":"importUploadInTable","class":"OCA\\Tables\\Controller\\ImportController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":114,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":161,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1001,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":24,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php","Line":224,"message":"Error while creating new row for import.","exception":{},"CustomMessage":"Error while creating new row for import."}}

Case 2 - VAL1 [DATE], VAL2[TEXT]

In this case not even the Preview dialogue appears, but only a window stating "Importiere Daten aus datefoo.csv" and that's it. Log file output looks similiar to Case 2

{"reqId":"***","level":3,"time":"***","remoteAddr":"***","user":"***","app":"index","method":"POST","url":"/apps/tables/importupload-preview/table/8","message":"floor(): Argument #1 ($num) must be of type int|float, string given in file '/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php' line 224","userAgent":"Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:131.0) Gecko/20100101 Firefox/131.0","version":"30.0.0.14","exception":{"Exception":"Exception","Message":"floor(): Argument #1 ($num) must be of type int|float, string given in file '/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php' line 224","Code":0,"Trace":[{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":161,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1001,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":24,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","Line":146,"Previous":{"Exception":"TypeError","Message":"floor(): Argument #1 ($num) must be of type int|float, string given","Code":0,"Trace":[{"file":"/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php","line":224,"function":"floor"},{"file":"/var/www/nextcloud/apps/tables/lib/Service/ImportService.php","line":163,"function":"excelToDateTimeObject","class":"PhpOffice\\PhpSpreadsheet\\Shared\\Date","type":"::"},{"file":"/var/www/nextcloud/apps/tables/lib/Service/ImportService.php","line":97,"function":"getPreviewData","class":"OCA\\Tables\\Service\\ImportService","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/ImportController.php","line":98,"function":"previewImport","class":"OCA\\Tables\\Service\\ImportService","type":"->"},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/Errors.php","line":22,"function":"OCA\\Tables\\Controller\\{closure}","class":"OCA\\Tables\\Controller\\ImportController","type":"->","args":["*** sensitive parameters replaced ***"]},{"file":"/var/www/nextcloud/apps/tables/lib/Controller/ImportController.php","line":97,"function":"handleError","class":"OCA\\Tables\\Controller\\ImportController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":208,"function":"previewUploadImportTable","class":"OCA\\Tables\\Controller\\ImportController","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/Http/Dispatcher.php","line":114,"function":"executeController","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/AppFramework/App.php","line":161,"function":"dispatch","class":"OC\\AppFramework\\Http\\Dispatcher","type":"->"},{"file":"/var/www/nextcloud/lib/private/Route/Router.php","line":302,"function":"main","class":"OC\\AppFramework\\App","type":"::"},{"file":"/var/www/nextcloud/lib/base.php","line":1001,"function":"match","class":"OC\\Route\\Router","type":"->"},{"file":"/var/www/nextcloud/index.php","line":24,"function":"handleRequest","class":"OC","type":"::"}],"File":"/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php","Line":224},"message":"floor(): Argument #1 ($num) must be of type int|float, string given in file '/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php' line 224","exception":{},"CustomMessage":"floor(): Argument #1 ($num) must be of type int|float, string given in file '/var/www/nextcloud/apps/tables/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/Date.php' line 224"}}