Date field in import-export entity content with different formats

[enfJoao]

I'm submitting a

[x] bug report
[x] not sure

...about

[x] Content Types or data management
[x] other / unknown

Current Behavior / Expected Behavior

I have an entity that I export-import monthly.
It contains two date fields. One always imports, one always fails import (the second one only requires minimal manual fix within 2sxc for a couple items).
Today I exported the entity again to xml and saw a potential issue:

The entities are exported with a different format (and probably expects a different format on import too, so it fails).
The configuration is exactly the same for both.

I manage the xml in excel, if it matters.

Your environment

• 2sxc version(s): 18.02

[enfJoao]

It's confirmed.
If I use the format Y-m-d\TH:i:s for the import xml, the data in the field is actually imported.
The other date fields in the export will work fine with default date formats.

[iJungleboy]

I don't fully understand the issue.

The [] placeholder is kind of a "null" placeholder. Is this causing the problem?

Are you saying that the rows with the [] in the date field are causing problems? Just on that field, or the entire row? What is the result?

[enfJoao]

Could be. But the issue is in the format of the last two rows.
No othe fields/columns are affected. This issue is just related to the date format in the second column.

[iJungleboy]

ok, got it. thanks @enfJoao for clarifying

[iJungleboy]

could you also include the xml snippet in this issue, so we can better see what's really there?

[enfJoao]

Unfortunately I cannot.

But I did take a look at it and it seems the dates share the same format. The [] null values in the second column (end date) makes excel treat the filled values as text instead of dates, apparently. So 2sxc fails to import those (with no error whatsoever).
The first column (start date) is converted to a standard excel date format and then imported in 2sxc.

Any way to improve excel management of these dates? A better app for xml editing perhaps? I do need this monthly.

[iJungleboy]

So it appears that both columns use the same formatting.

Do things only break, if you've edited them in Excel? So is Excel changing something if you open/save?

Or is a simple reimport (without editing) also breaking?

[enfJoao]

Here's a simple app with two date fields, two rows, first with both dates filled, second with one date filled, one empty.

ContentConfig.json

{"_":{"V":1},"ContentType":{"Id":"5fe6a11b-20ce-4ff6-bb31-6f8a44126856","Name":"SimpleTwoDatesEntity","Scope":"Default","Metadata":[],"Attributes":[{"Name":"DateOne","Type":"DateTime","InputType":"datetime-default","IsTitle":true,"Metadata":[{"Id":39873,"Version":1,"Guid":"6dc24110-2ed6-4dd7-a4ca-885718db95ef","Type":{"Id":"@All","Name":"@All"},"Attributes":{"String":{"InputType":{"*":"datetime-default"},"Name":{"*":"DateOne"}},"Entity":{"Errors":{"*":[]},"Formulas":{"*":[]},"Warnings":{"*":[]}},"Boolean":{"VisibleInEditUI":{"*":true}}},"Owner":"dnn:userid=1"}]},{"Name":"DateTwo","Type":"DateTime","InputType":"datetime-default","IsTitle":false,"Metadata":[{"Id":39874,"Version":1,"Guid":"80328376-18b8-4a37-8db4-3c72a27963ed","Type":{"Id":"@All","Name":"@All"},"Attributes":{"String":{"InputType":{"*":"datetime-default"},"Name":{"*":"DateTwo"}},"Entity":{"Errors":{"*":[]},"Formulas":{"*":[]},"Warnings":{"*":[]}},"Boolean":{"VisibleInEditUI":{"*":true}}},"Owner":"dnn:userid=1"}]}]},"Entities":[]}

SxcOriginalExport.xml
This is the entity as exported with no changes.

<SexyContentData>
  <Entity Type="SimpleTwoDatesEntity">
    <Guid>c9015920-2482-484f-a767-2577fbcdb3ff</Guid>
    <Language></Language>
    <DateOne>2024-10-25T00:00:00</DateOne>
    <DateTwo>2024-10-25T00:00:00</DateTwo>
  </Entity>
  <Entity Type="SimpleTwoDatesEntity">
    <Guid>119714b7-2d6b-44d0-aa9c-c540f5ad1e86</Guid>
    <Language></Language>
    <DateOne>2024-10-25T00:00:00</DateOne>
    <DateTwo>[]</DateTwo>
  </Entity>
</SexyContentData>

As soon as I open this file with excel, it shows this:

Different format for the dates, caused by the empty/null value.

SxcExcelSavedNoEdit.xml
This is the above file opened with excel, saved as xml, no changes. Minor changes in the date format, but I can import it and nothing changes.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<SexyContentData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<Entity Type="SimpleTwoDatesEntity">
		<Guid>c9015920-2482-484f-a767-2577fbcdb3ff</Guid>
		<DateOne>2024-10-25T00:00:00.000</DateOne>
		<DateTwo>2024-10-25T00:00:00</DateTwo>
	</Entity>
	<Entity Type="SimpleTwoDatesEntity">
		<Guid>119714b7-2d6b-44d0-aa9c-c540f5ad1e86</Guid>
		<DateOne>2024-10-25T00:00:00.000</DateOne>
		<DateTwo>[]</DateTwo>
	</Entity>
</SexyContentData>

SxcExcelAddedTwoRows.xml
This is the same file with two added rows (copy-paste from the other fields). Excel is unable to keep the original format.

<SexyContentData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<Entity Type="SimpleTwoDatesEntity">
		<Guid>c9015920-2482-484f-a767-2577fbcdb3ff</Guid>
		<DateOne>2024-10-25T00:00:00.000</DateOne>
		<DateTwo>2024-10-25T00:00:00</DateTwo>
	</Entity>
	<Entity Type="SimpleTwoDatesEntity">
		<Guid>119714b7-2d6b-44d0-aa9c-c540f5ad1e86</Guid>
		<DateOne>2024-10-25T00:00:00.000</DateOne>
		<DateTwo>[]</DateTwo>
	</Entity>
	<Entity Type="SimpleTwoDatesEntity">
		<Guid>47a8038d-64f2-4ea8-8c77-63b8f681bf2b</Guid>
		<DateOne>2024-10-24T00:00:00.000</DateOne>
		<DateTwo>45592</DateTwo>
	</Entity>
	<Entity Type="SimpleTwoDatesEntity">
		<Guid>2460c2ee-3550-423a-a721-319400339a1d</Guid>
		<DateOne>2024-10-24T00:00:00.000</DateOne>
		<DateTwo>45592</DateTwo>
	</Entity>
</SexyContentData>

2sxcApp_DatesInExcelManipulation_0.0.18.zip

ContentData.zip

[iJungleboy]

Thanks.
Not sure if this is something 2sxc can fix, as it sounds like an Excel issue. But maybe we can do something...?

[enfJoao]

There have been many issues in the past where the recommended solution was export - change - import. Change a data type is one of them.

If changing data in excel is not reliable, then you can recommend a better xml editor that works with 2sxc readable data formats.