topic Re: ODS excel: Text data of XSD type 'DateTime' was expected in SAS Users Group in Israel

ODS excel: Text data of XSD type 'DateTime' was expected

Assaf_Attas — Sun, 27 Jun 2021 04:49:56 GMT

good morning all, has anyone encountered this irritating issue? excel files created by ODS EXCEL are opened correctly when opened in excel.. BUT.. When trying to import and excel workbook via Data>New Query>From File>From Workbook, I get the following error: Unable to Connect: Excel - Core Properties part: Text data of XSD type 'DateTime' was expected. after manually re-saving in excel (without changing anything), the error disappears.. but since automation is the goal here so this solution is not relevant. i've found that others encountered this but no solution presented. it is also reported (though i havent checked myself) that that problem occurs on if the file is generated before 11am ?!?!?!!? https://community.powerbi.com/t5/Power-Query/Unable-to-Connect-Excel-Core-Properties-part-Text-data-of-XSD/m-p/1699173 https://techcommunity.microsoft.com/t5/excel/new-query-import-error-unable-to-connect-excel-core-properties/m-p/1592839

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Sajid01 — Sun, 27 Jun 2021 10:30:47 GMT

Hello
Can you please share the SAS code used to generate the file?
If not does the file has xlsx extension or xls.?

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Assaf_Attas — Sun, 27 Jun 2021 11:01:03 GMT

basically this is the sas code i'm using:

it seems that the before / after 11AM issue i've mentioned earlier is indeed relevant.

ods excel file="\\some_location\some_file.xlsx" options(sheet_name='report1');
title;footnote;
proc print data=sashelp.class;run;

ods excel options(sheet_name='report2');
proc print data=sashelp.classfit;run;

ods excel close;

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Sajid01 — Sun, 27 Jun 2021 12:06:31 GMT

Hello
I did find the following difference on dates before and after saving. (I used Libre Office). The file could be imported into power bi after saving
Original
<dcterms:created xsi:type="dcterms:W3CDTF">2021-06-27T 7:37:29+00:00</dcterms:created>
After saving
<dcterms:modified xsi:type="dcterms:W3CDTF">2021-06-27T07:53:21Z</dcterms:modified>

I suggest that your team take up the case with SAS and Microsoft power bi team to resolve the issue.

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Sajid01 — Sun, 27 Jun 2021 12:16:46 GMT

As a postfix to my answer above, if the file can be opened in excel, it should be possible to open in power bi.
The issue to me looks more on power bi side and should be addressed by that team.

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Assaf_Attas — Sun, 27 Jun 2021 13:39:08 GMT

hi,

thank u very much for your assistance

it seems like u hit the spot and the "problem" is the TimeZone offset..

although the W3CDTF (iso 8601) allows 2 "legal" options, it seems like SAS adapted one approach while Microsoft adapted the other.

yet, remains the question as for the difference SAS register the time zone offset in different hours.

it should not matter at what time the file was generated..

for that reason, i believe it's more like a SAS issue.

https://www.w3.org/TR/NOTE-datetime

This profile defines two ways of handling time zone offsets:

Times are expressed in UTC (Coordinated Universal Time), with a special UTC designator ("Z").
Times are expressed in local time, together with a time zone offset in hours and minutes. A time zone offset of "+hh:mm" indicates that the date/time uses a local time zone which is "hh" hours and "mm" minutes ahead of UTC. A time zone offset of "-hh:mm" indicates that the date/time uses a local time zone which is "hh" hours and "mm" minutes behind UTC.

Re: ODS excel: Text data of XSD type 'DateTime' was expected

EyalGonen — Sun, 27 Jun 2021 14:02:36 GMT

Hi @Assaf_Attas

I searched and it looks like this is a known issue for SAS Technical Support so perhaps the best approach now is to open a support track about it and to hear what they say about it.

HTH,

Eyal

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Sajid01 — Sun, 27 Jun 2021 14:25:51 GMT

The silver lining in this issue is that the issue goes away if the file is opened and saved again.
While automating the process manual intervention is not some thing one wants to do.
SAS has provided an xls to xlsx conversion macro for a different issue. The macro can adapted for automated solution.
This is available here http://support.sas.com/kb/43/496.html.
This work around will help you in resolving the issue.

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Assaf_Attas — Mon, 28 Jun 2021 07:26:00 GMT

i've investigated further and this is definitely a SAS issue.

if generated before 10AM (a single digit HH):

<dcterms:created xsi:type="dcterms:W3CDTF">2021-06-28T 7:16:33+03:00</dcterms:created> - Not openable by Excel Power BI

if generated after 10AM (a two digit HH):

<dcterms:created xsi:type="dcterms:W3CDTF">2021-06-28T10:04:09+03:00</dcterms:created> - Openable by Excel Power BI

if i manually add a leading zero to HH in core.xml : 2021-06-28T 7:16:33+03:00 to 2021-06-28T07:16:33+03:00 - Openable by Excel Power BI

so the problem is that SAS doesn't comply with W3CDTF standard for hours 00:00AM till 09:59AM

@EyalGonen , please open a track for this.

Re: ODS excel: Text data of XSD type 'DateTime' was expected

EyalGonen — Mon, 28 Jun 2021 08:12:41 GMT

Hi @Assaf_Attas

I cannot open the track on your behalf. Please email support@sas.com to open a track and send in all the relevant info to replicate the issue including the SAS version and code + any additional findings you have found.

Eyal

Re: ODS excel: Text data of XSD type 'DateTime' was expected

Sajid01 — Mon, 28 Jun 2021 08:28:59 GMT

Hello
Did you try the workaround i suggested
It should solve tour issue.