Help with DATETIME import from a CSV

Data Service
1

Hi, I’m struggling to get an upload working with the attached (anonymized) CSV.
Employee copy.csv (23.2 KB)

Note that when I attempt the upload this is just called “Employee” to match the destination table.

The import proceeds successfully but any row with a value in the dateStart field gets ignored. I get messages for each row like the following:
45 07:16:17 IMPORT_DATA Ignores rows with values [Anon, Anon, , Transport, 2023-29-4T08:00Z, 37387329, Yes, --, --, --, --, null, , -, 0.00, --, --, https://premiumboat.backendless.app/api/files/Images/Profile Images/headshot-placeholder.jpg, M/T/W/Th/F, 5pm, 8am - 5pm; M/T/W/Th/F, 8am, --, --, DC1D6755-10AE-4B1A-9DD4-0EBE43ABB77A, , Tue May 28 09:43:49 UTC 2024, Tue May 28 09:43:59 UTC 2024]. Invalid date format for value ‘2023-29-4T08:00Z’

and sometimes a separate error for rows that have no update date:
22 07:16:17 IMPORT_DATA Ignores rows with values [Manager, Test, n/a, --, Mon Jan 01 08:00:00 UTC 1900, null, null, --, --, --, --, null, Manager Test, -, 0.00, --, --, https://premiumboat.backendless.app/api/files/Images/Profile Images/headshot-placeholder.jpg, M/T/W/Th/F, 5pm, 8am - 5pm; M/T/W/Th/F, 8am, --, --, null, null, Thu May 09 04:21:02 UTC 2024, null]. Invalid date format for value ‘null’

I have tried multiple different datetime formats, ranging from what is shown here to exact replicas of the dates that were output when I exported the file. I can’t quite find what I’m doing wrong. Can you advise?

Thanks!

2

Hello, @James_Hereford.

We are investigating this issue thank you for your report.

Regards, Nikita.

3

Hi @James_Hereford,

While we work on fixing the issue on our side to parse such CSV files without errors and as the documentation page with valid date formats is currently unavailable, I’ll explain the errors encountered when importing your file to avoid blocking your work.

We do not currently support dates in the format where the day or month is indicated by a single digit and where the day comes before the month (specifically in this format),
i.e., “yyyy-dd-MTHH:mmZ” and “yyyy-d-MMTHH:mmZ” are not currently supported.
The valid format is “yyyy-MM-ddTHH:mmZ”.
For example, in your file, 2023-29-4T08:00Z is incorrect and should be 2023-04-29T08:00Z. Also, 2021-6-11T08:00Z is incorrect and should be 2021-11-06T08:00Z.

The "Invalid date format for value ‘null’" error occurred because some entries had null values in the 'update' column, which is also a DATETIME column.

In some entries, you used null values for the system columns 'objectId' and 'ownerId'. This should not be done as it makes it impossible to properly work with such records even if they are imported.

Here is an example of your file with the corrections mentioned above:
EmployeeFixed.csv (23.3 KB)

I hope this information is helpful, and in the meantime, we will work to make the import process in Backendless even better.

Regards,
Viktor

5

Thanks so much Viktor. I tried yyyy-MM-ddTHH:mmZ before the version that I sent over and had a similar error but perhaps I didn’t have something correct on my side. I will try again today with the changes that you recommended and see if I can get it to work.

Cheers,
James

1 Like