New Query import error: Unable to Connect: Excel - Core Properties part: Text data of XSD type 'Date

Highlighted
New Contributor

Hi,

 

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.

 

The file I try to import is an automatically generated file. When I manually generate the file via the original software (SAS), the workbook can be imported without problems. Thus, it has something to do with the scheduler that automatically generates the file. Does anyone recognize this error and know a solution for it?

 

Ruben

 

4 Replies
Highlighted

Hi @RubenPloeg 

 

Might be some kind of file property issues,  you can try open the file and save it manually as xlsx.

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

 

Highlighted

@RubenPloeg 

As @Faraz Shaikh mentioned SAS generates slightly different from standard one XML-schema which is not critical for Excel itself (thus open and save in Excel fixes an issue) but cause an error in Power Query. Not sure how to handle that directly in PQ, if you have any sample file we may try to play with it.

Highlighted

@Sergei Baklan Thanks for the comment. Indeed, when I make a copy of the file it does work so there is a workaround. But for automation sake it would be nice to have it working via powerquery.

 

Unfortunately, I cannot give you a sample of the file. The strange this is that after some trials and error runs, we saw that when the scheduler is triggered early morning (7AM) the imported gives the error. However, when we trigger it again at say 11AM, the importer works, even though we haven't made a copy of some sort. The whole process is the same. Therefore, I am quite lost to what the cause could be, because if it is a difference in SAS vs. normal xlsx then it should happen all the time.

 

Highlighted

@RubenPloeg 

Another possible workaround is PowerShell script which opens and saves any xlsx file in folder based on task scheduler.