Aug 17 2020 05:42 AM - edited Aug 17 2020 07:04 AM
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
Aug 19 2020 03:47 AM
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
Aug 19 2020 03:23 PM
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.
Aug 21 2020 07:17 AM
@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.
Aug 22 2020 01:23 PM
Another possible workaround is PowerShell script which opens and saves any xlsx file in folder based on task scheduler.