SAS EG Data Import Error with XLSX format

[japsas100]

Hello,

We are getting below error while importing the xlsx format file. However we can import data from xls format file. Please advise?

[ballardw]

---

@japsas100 wrote:

Hello,

We are getting below error while importing the xlsx format file. However we can import data from xls format file. Please advise?

---

First, pick a file format. XLS and XLSX are not the same and have different restrictions/options.

Second the method you attempted should be shared. Please either indicate all of the options selected if using a menu driven approach or code you wrote for the task. EG often places generated code into the log and it would help to copy that and paste it into a text box opened on the forum with the </> icon.

This sort of message is typical when using an approach that works for one of the file formats with the wrong file extension (file type).

[japsas100]

@ballardw 

This is existing code, working fine without Citrix server. But we are running same code with new Citrix its throw above error

[Sajid01]

Hello @japsas100 
You will have to downloads and install that driver as mentioned in the message. That is the requirement.

Perhaps you don't have MS Access / Excel installed locally OR are using Office 365 in the new environment

[ballardw]

---

@japsas100 wrote:

@ballardw 

This is existing code, working fine without Citrix server. But we are running same code with new Citrix its throw above error

---

A change in environment is an important bit of information that should have been in the first post.

New server typically means that not everything installed on an old server was installed or configured the same. With Office products involved that also brings up if the server is even running the same operating system and can have Office installed.

This likely means that you need to get your IT people involved to get the appropriate product or drivers installed.

[japsas100]

@ballardw @Sajid01 

this is not the new server. we have just added the Citrix layer in order to access the current and working environment.

[Sajid01]

Hello @japsas100 
Can you please help us under stand what this layer  Citrix does?

[japsas100]

Its means now we are going to connect sas application via Citrix gateway instead of directly with ssh

[Sajid01]

Please have a look at this. https://support.sas.com/kb/55/630.html 
The issue what you have mentioned typically occurs when there are changes on your machines like reinstall / upgrade of OS or migration to office 365. etc.
Did you use ssh to connect to SAS server using SAS EG?

[SASKiwi]

If you are now running SAS EG via Citrix, then it means EG would have been installed as an application on the gateway along with supporting apps like MS Office.

What version and bitness of EG was installed on Citrix? Is it exactly the same as the non-Citrix EG version and bitness? 

[japsas100]

@Kurt_Bremser @SASKiwi 

We are using SAS EG version is 7.15 64 bit and MS Office 365 Excel 32 bit outside Citrix (Working Fine)

SAS EG version is 7.12 64 bit and MS Excel 2016 ,32 bit with Cirtix(not working)

Please advise?

[SASKiwi]

First off I'd try installing the 64-bit MS ACE driver on Citrix - you need the same bitness as EG. If that doesn't help, you could install the same versions of EG and MS Office on Citrix that you have on PCs to see if that helps. I suggest you also open a track with SAS Tech Support as it is worth consulting them prior to making any changes.  

[Kurt_Bremser]

Apart from saving the spreadsheets to csv files and reading those with a data step (which is the most consistent and fail-proof method to read Excel data into SAS), the best method nowadays when using EG is this:

• use XLSX as the file format
• transfer the .xlsx file to the server with the Copy Files task built into EG
• use PROC IMPORT with DBMS=XLSX or LIBNAME XLSX to read the file

[Sajid01]

Hello @japsas100 
Were you able to resolve your issue?
My experience has been installing the drivers as suggested in the error message.