EG: Import xlsx

[fja]

Dear community!

When using the EG task to import xlsx a data step is generated that refers to an intermediary file with delimited characters. Does anyone know how this is done technically? 

The generated code does unfortunately not show how this file is produced starting from the xlsx file. 

Yes of course one could use the simpler methods that are normally recommended, i.e. libname with the xlsx engine and proc import. I am particularly interested in the approach EG takes as I am trying to track down some transcoding issues when importing the xlsx.

So ... does anyone happen to know?

Cheers

fja

[Cynthia_sas]

Hi:
This is a behind the scenes (internal to EG) process that is not under your control. In the end, the issue with transcoding is going to be with the data and the encoding settings on the data, system, server, SAS session, etc. Rather than trying to reverse engineer how EG does it, try some other approaches that may help you determine the actual root cause.
For example, when you use the XLSX engine or PROC IMPORT, do you have the same transcoding errors? Have you examined the data to see what the original encoding for the data file or the XLSX file was? Have you tried checking the ENCODING= option as a system option and within your code. If you can't get to the bottom of the transcoding problems on your own, then it's time to open a case with SAS Tech Support for more directed help in debugging and solving the issue.
Cynthia

[fja]

Hello!

Thank you for very much for your reply Cynthia. And appologies for the late reply .. I have fallen ill in between, this is why. This thread has gone cold meanwhile but let me answer to your post to show appreciation for your contribution.

I have traced back the "culprit" enty, it is a "č" character (a cech c with an inverted accent circonflex).  Encoding option is set to latin1 on the system (so I cannot change that). The xls is formated/saved with encoding "windows latin1", changing that to utf-8 or anything different does not change the behaviour.

Using libname xslx does result in the same transcoding errors as before.

We have not managed to come up with a resoluton for this behaviour/warnings so we decided to alter the data source. Not nice but a quick way out. Nevertheless I was courious if someone had some idea I could have play with. Please let me know. 🙂

As Cynthia has suggested one actually needed to go through this step by step with the tech support. But as we have that workaround in place I won't waste their time. 🙂 

Cheers to all contributers ... and a Happy New Year to you all! 🙂

--fja 

[SASKiwi]

Talk to your SAS administrator. It is entirely possible to create a new SAS server instance that has the UTF-8 encoding setting for any SAS sessions using this server.  It is quick and easy to set one of these up, but administrator permissions are required. Then, you just point your EG connection profile to the new server instance.

What OS does your SAS server run on? If it is Windows you could use the EXCEL LIBNAME engine to see if that copes better with encoding issue. If your SAS server runs on Unix, then you can always install the SAS PC Files Server and use the PCFILES LIBNAME engine as another alternative.

[Tom]

What I have heard is that EG now has an option to NOT do that transformation.

Instead there is a way to tell to just upload the file and generate PROC IMPORT code to read it.

[fja]

Hello Tom!

Thank you for your reply. Yes, there is a tick option at the last step of the import wizard of EG 8.2. Alas its behaviour/the generated code is pretty much unimpressed by having it set. But thank you for pointing this out.

--Fja

[ChrisHemedinger]

If you don't mind watching a little video, this segment covers how EG Import Data works and how you can control it.

[fja]

Hello Chris!

Thank you for that pointer ... and the nice and well crafted presentation, of course. Alas, the procedure sketched out there seems only be valid vor csv files. Working with EG 8.2 and and xlsx file to import I do not get the options offered that you have mentioned in your video.

Could it be, that this is just implemented for csv and not for xlsx data sources?

--fja