topic SAS EG Data Import Error with XLSX format in Administration and Deployment

SAS EG Data Import Error with XLSX format

japsas100 — Tue, 15 Jun 2021 14:20:17 GMT

Hello,

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

Re: SAS EG Data Import Error with XLSX format

ballardw — Tue, 15 Jun 2021 14:46:34 GMT

@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).

Re: SAS EG Data Import Error with XLSX format

japsas100 — Tue, 15 Jun 2021 15:18:13 GMT

@ballardw

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

Re: SAS EG Data Import Error with XLSX format

Sajid01 — Tue, 15 Jun 2021 15:43:20 GMT

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

Re: SAS EG Data Import Error with XLSX format

ballardw — Tue, 15 Jun 2021 15:49:34 GMT

@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.

Re: SAS EG Data Import Error with XLSX format

japsas100 — Tue, 15 Jun 2021 16:14:48 GMT

@ballardw @Sajid01

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

Re: SAS EG Data Import Error with XLSX format

Sajid01 — Tue, 15 Jun 2021 16:19:56 GMT

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

Re: SAS EG Data Import Error with XLSX format

japsas100 — Tue, 15 Jun 2021 17:34:01 GMT

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

Re: SAS EG Data Import Error with XLSX format

Sajid01 — Tue, 15 Jun 2021 18:08:07 GMT

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?

Re: SAS EG Data Import Error with XLSX format

SASKiwi — Tue, 15 Jun 2021 23:19:48 GMT

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?

Re: SAS EG Data Import Error with XLSX format

Kurt_Bremser — Wed, 16 Jun 2021 06:34:12 GMT

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

Re: SAS EG Data Import Error with XLSX format

Sajid01 — Wed, 16 Jun 2021 15:19:04 GMT

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

Re: SAS EG Data Import Error with XLSX format

japsas100 — Wed, 16 Jun 2021 15:43:44 GMT

@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?

Re: SAS EG Data Import Error with XLSX format

SASKiwi — Wed, 16 Jun 2021 20:24:47 GMT

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.