SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to open an excel file with DataFlux Data Management Studio 2.5

Reply
Occasional Contributor
Posts: 6

How to open an excel file with DataFlux Data Management Studio 2.5

Hello, 

 

I am trying to open an excel spreadsheet in one of my nodes because it has a macro that will run on open to change a bunch of spreadsheets to csv files that will be used in the job.

 

I've tried using the open() and execute() function in an expression node but they don't open the file. The node will complete successfully but no open file.

 

Any help would be great! Thanks

SAS Super FREQ
Posts: 90

Re: How to open an excel file with DataFlux Data Management Studio 2.5

Hi,

 

Open() is a function that works on a file object that you have to create. See the "file functions" section in the Data Management Platform Expression Guide.


Execute() will potentially be an option for you but getting the command line syntax correct can sometimes be a challenge. Take a look at some of the examples in the Expression Guide and see if they provide some hints to help you out. I've seen things like quoting of paths to the executable sometimes be a problem and you'll have to figure out what parameters Excel is expecting and craft your execute() code to match.


Ron

SAS Employee
Posts: 36

Re: How to open an excel file with DataFlux Data Management Studio 2.5

As Ron indicates, yes it is possible, and the syntax can be tricky.  Here are a few things to try, and be aware of. 

 

- Use the execute function.  It is the one that you need to use to call some external program

- The command is running locally, so you have to be able to run commands on the host you are trying to execute the script on

- Check that you have permissions set, and that you can get to the code from the program

 

You can try this.  Create some simple program, and just call that.  Verify that your expression code is calling that program correctly.  I would put it in the same location as Excel is to make sure you can get to the directory that you are trying to call out to with your more complex call.

 

If you have verified that and things still are not working, then there is a handy microsoft tool called procmon:  https://technet.microsoft.com/en-us/sysinternals/bb795533.aspx

 

Its a handy way on a windows machine to see all files and processes being used.  It can help figure out what is happening on the host you are working on to see where things might be failing. 

 

Hope this helps,

 

Nancy 

Ask a Question
Discussion stats
  • 2 replies
  • 420 views
  • 0 likes
  • 3 in conversation