tables icon indicating copy to clipboard operation
tables copied to clipboard
verified publisher icon nextcloud

Date/time import is broken

Open hatelamers opened this issue 1 year ago • 2 comments

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
  1. Import test.csv into "test" table
  2. Impor results displayed: Found columns: 2, Matching columns: 2, Inserted rows: 1, Value parsing errors: 0
  3. "Date When" cell in "test" table is blank
  4. Change data row in test.csv to
"2024-04-08T12:35:10.460Z";234
  1. Proceed with P3-5: same result
  2. Change data in row test.csv to
1712579710;234
  1. Proceed with P3-5: same result
  2. Change data in row test.csv to
"8 Apr 2024, 12:35:10";234
  1. Proceed with P3-5: same result
  2. Change data in row test.csv to
8 Apr 2024, 12:35:10;234
  1. Proceed with P3-5: same result
  2. Change data in row test.csv to
2024-04-08 12:35:10;234
  1. Proceed with P3-5: same result
  2. Create text.xlsx from test.csv (Excel displays correct values)
  3. Import text.xlsx 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

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

hatelamers avatar Apr 08 '24 13:04 hatelamers

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.

affenbirne avatar Aug 07 '24 15:08 affenbirne

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"}}

manmetz avatar Oct 15 '24 13:10 manmetz