Enterprise Guide Import Data Task Not Working with Windows Scheduler Anymore

[elwayfan446]

I wanted to see if anyone else has noticed this issue before I open a ticket with my company (since this will be a long process with them).

I have recently received a new laptop and have installed SAS EG 8.2 on it.  It is the same version I had on my old laptop.  I have several process flows in EG projects scheduled to run via scheduler that all ran fine on my old machine.  Now that I have a new machine, I have had trouble with the process flows putting out the correct data.  I finally narrowed it down to the fact that the import data tasks are not pulling in the Excel files when they run.  The excel files are uploaded to the server with the copy task, then the import data task imports it into a SAS dataset. 

A couple of notes:

• I have verified the other tasks are running correctly
• If I open the projects and run the process flows manually the import data tasks work as they should
• I have tried to create new import data tasks that point to the files and they don't work either
• My new computer has Excel 365 installed, my old computer had both Excel 2016 and Excel 365 so I am not sure if it was still working on my old machine because the server was reading the excel 2016 files differently than the excel 365 files.  I don't really have a way to test this.

If anyone has had similar issues and has a resolution I would appreciate it.

[ChrisHemedinger]

If the Excel files are uploaded with Copy Files, and then you use the Import Data task to import...then the file is likely being downloaded (behind the scenes) for the Import Data task to read/generate code and complete the import.  A round trip.

Check the Import Task settings to ensure the Performance options are set to not create a cleansed version of data (which reads the Excel and creates a temp CSV). 

Ideally, if you are moving the Excel files with Copy Files, you would simply use PROC IMPORT or LIBNAME XLSX to convert the Excel to SAS data -- since it's all there on the server already.  Not as friendly as the Import Data task, but fewer moving parts.

One more simple check to ensure things are running in the proper order -- use custom User Links to force the tasks in your process flow to run in the proper sequence.  Sometimes being explicit about this especially with the Copy Files task is needed to ensure things run in the right order.

[elwayfan446]

Thanks @ChrisHemedinger .

I am not able to find the setting to turn off files being cleansed.  I know it is enabled because I notice the submission status that the file is being cleansed when I run it manually.  I'd like to try that first before changing my import process to a program rather than the task, just for time's sake.

In regards to your custom links recommendation, I currently modify the VBA that is generated for the scheduler to run an Ordered List within the process flow to ensure the tasks are running in the correct order.  It has always worked in the past.  I am assuming that it is working now but no easy way to verify that I know of.

[ChrisHemedinger]

Ah right, that Performance/cleanse option is available only for CSV/text, not Excel.

[elwayfan446]

I am guessing it automatically cleanses the excel file since I can see that as a status while I manually run the task.

My next step will be to create a program to do the import from the file on the server to see if that works.  I will report back.

[elwayfan446]

Taking a look at the code behind the import data task, it appears it would take a extensive program to pull in the excel file and format it like the import data task does so that it doesn't create the temp file.  The temp file is getting created in the WORK library on the server so I am still confused on why this would be a problem all of the sudden when it was working fine before I got this new laptop.  I can manually run the process flow as a work around but it is much easier to have the user run it from a task without having to open EG.  Frustrating.

[elwayfan446]

I am still working on this today.  Gonna see if converting my excel files to csv and then turning off the cleansing option helps.  If anyone else stops by and has suggestions please let me know.  Thanks everyone.

[ChrisHemedinger]

If the file is a true Excel (XLSX) file, then the code should be simple.  

proc import
 datafile = '/path-to-excel/myfile.xlsx'
 out = work.myfile
 dbms='XLSX'
 replace;
run;

The code EG generates reflects a behind-the-scenes conversion to CSV and DATA step to read that. But EG does that work, not the SAS session. 

[elwayfan446]

Correct but I was concerned with all of the formatting you can do with the import data task that I would have to program into the import or into code to scrub it afterwards. I didn't want to have to recode all of it.
That being said, I converted the initial excel file to a .csv and tried the process. I unchecked the cleansing option and it *appears* to be working. I will do further testing today but if this works it is going to be a huge relief.

[elwayfan446]

@ChrisHemedinger 

Well, it appears that I was wrong.  Even a .csv file won't upload with the vbs script that the EG scheduler creates.  If I run that script manually, the upload task is skipped (or does not work) in the process flow.  I have to open EG and that process flow and run either the ordered list or the task manually to get it to work.  If I run it, although and extra step, it isn't necessarily a big deal.  For an end user with no SAS experience, running from the scheduler was a perfect option. I also tried extracting the code from the import tasks to see if just running that code and not the task would work.  No luck.

If I try to run the proc import method, how would I format it as it comes in like I can with the task?  For example, converting the source formats to informats, etc.?  That is the most beneficial feature for me.  That is why I tried extracting the code because the code had all of that in it.

[elwayfan446]

For clarification, the csv file uploads fine but it isn't importing to the dataset.  Sorry for any confusion.

[ChrisHemedinger]

Just to confirm, you have a Copy Files task that uploads the CSV to the SAS session, then an Import Data task that is meant to convert it to a SAS data set? Is the Import Data task reading the file from the location where the Copy Files task sent it?

If you would rather work with Excel files, you can use a simpler PROC IMPORT step to read the XLSX file with DBMS=XLSX. And then use DATA step after the import to change the field properties as needed -- formats, convert char to date (if needed), etc.  It amounts to the same work that EG does for you in the Import Data task, but it gives you more control (and of course, more responsibility to maintain it).

[elwayfan446 · Accepted solution]

After much playing with this, I was able to fix this by making sure all files I was importing were CSV files.  I rebuilt the import tasks to pull from these new files and they have been importing correctly from the scheduler for about a week.  There must be some issue with the way the import task handles excel 365 workbooks that is preventing the import task from working with them.

I just wanted to report back and let everyone know I appreciate the help.

[SASKiwi]

Please mark your post as answered then.

[ChrisHemedinger]

Thanks for following up @elwayfan446 ! Glad it's solved, a little dismayed it proved to be so much work for you though...