Hello,
I’m having an issue importing an Excel file, the file is downloaded from an API and then imported to EasyMorph.
The first 10 rows are from a template (those are imported normally), and the rest is populated by the API, these are the rows that are not being imported.
The thing is that, if I open the file, change something and save, the import command works fine, but if I don’t open the file it does not work.
Could you please shed some light on this?
I really need this to work without opening the file.
Thank you!
Export.xlsx (11.1 KB)
Import_Test.morph (3.6 KB)
Hi @IATelematel and welcome to the Community!
Can you please elaborate on that. What does it mean? A screenshot would be helpful.
OK, we looked at the .XLSX file and it doesn’t have a correct XML tag for dimensions. Most probably, it was generated by a 3rd party tool, that didn’t generate it correctly. We can’t do much about it. I would recommend contacting the owners of the API that generates the file and resolving the issue with them. If needed, we can provide technical details.
Yes please! That would be great, thanks a lot!
Hello @IATelematel,
The issue is in the \xl\worksheets\sheet1.xml from the Excel workbook package.
It contains the following elemet:
<dimension ref="A1:AA11"/>
The value in the ref attribute should contain a range that covers all the data on a sheet. But in the case of the sheet in question, it covers only the first 11 rows.
When the file is opened and resaved in Excel, that attribute is assigned to the correct value:
<dimension ref="A1:AA43"/>
By the way, you can automate resaving of Excel files with the PowerShell action and the following script:
$excel = new-object -comobject excel.application
$workbook = $excel.workbooks.open("{PathToXlsxFile}")
$workbook.save()
$workbook.close()
$excel.quit()
But that script requires Excel to be installed locally. Also if you’ll need to run that project on an EasyMorph Server, you’ll have to configure the corresponding test to be executed under some user account instead of the default LOCAL SERVICE account.
Hello @andrew.rybka
This is great, thanks a lot for the support!
It’s good to have an alternative in case that the solution indicated above takes time to implement.
Thanks for the tip!
@IATelematel, It might be a good idea to advise the API owner to use EasyMorph for data preparation. At least, EasyMorph handles the dimension property correctly.
Hello @andrew.rybka and @dgudkov,
I’ve been trying to run this script for the last days and it keeps failing when I try to run it from the EasyMorph server.
I’m running it from other account (not the LOCAL SERVICE account) and it doesn’t work. The error says:
"action ‘PowerShell’, module “Main”, table “Tabla de productos”: Exception when calling “Open” with arguments ‘3’: 'Microsoft Excel cant obtain access to the file ‘MyFilePath’.
There may be several reasons:
• The file name or path does not exist.
• Another program is using the file.
• The book you are trying to save has the same name as another book that is currently open. ’
Could you please help me figure out what’s going on here?
Thanks!
The main process of EasyMorph Server runs as a Windows service. COM-automation (used in the PowerShell script) doesn’t work under Windows services. It’s a known issue in Windows.
If you have the Enterprise Edition of EasyMorph Server, you can add a new account (besides the Default one) that is now a Windows service account. Create a new space that runs under that new account, and run the task in that space. In this case, the project will be expected in a separate process. It may not have problems with COM-automation. Read the Server Admin Guide how to create new accounts and used them to run tasks in spaces.
Alternatively, run the project in EasyMorph Launcher.
Hello @dgudkov
I’ll try that, if it doesn’t work then I’ll try using the Launcher in our server.
Thank you very much!
Regards!
Hello @IATelematel,
One question, is ‘MyFilePath’ the name of the parameter you are using to insert the file path into the PowerShell script? Can you check that the file path is being inserted correctly into the PowerShell script through the parameter?
Regards,
Roberto
Hello @roberto
It is not a parameter, I replaced the full path with “MyFilePath” just to post in the forum.
Thanks anyway for the observation ;).
Regards.