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

How do I use an excel workbook as a data source without a named range?

Reply
Occasional Contributor
Posts: 6

How do I use an excel workbook as a data source without a named range?

Hello, 

 

I have some excel workbooks that I'd like to use as a datasource in a DataFlux 2.5 data job. I found a way to do it by creating an ODBC connection to the workbooks but I have to go into each one and make a named range for DataFlux to read. The problem is, each time the workbook is updated the data gets entered into a new sheet named "Current" and the old sheet is renamed with the date. This means I'd have to go in each time the workbook is updated and create a new named range. Is there a way to have the whole workbook as a datasource without creating a named range?

 

I appreciate any help!

SAS Super FREQ
Posts: 90

Re: How do I use an excel workbook as a data source without a named range?

Hi,

 

I'm not aware of any option in SAS Data Management Studio ("DataFlux") to do what you have described. Named ranges are a requirement of the ODBC driver. Have you explored any macro options in MS Excel that might automatically create or copy a named range when the new sheet is created?

 

Ron

Occasional Contributor
Posts: 6

Re: How do I use an excel workbook as a data source without a named range?

Thanks for the reply Ron. I didn't realize a named range was a requirement for the ODBC driver. I have a macro like you said but I was going to use it as one of my backup plans. I was trying to avoid messing with the workbook since I'm not the owner. I'll have to pick whether to use a macro or save as csv files. Thanks for the help!
Super User
Posts: 3,100

Re: How do I use an excel workbook as a data source without a named range?

How about creating a CSV file of the Current sheet and importing that instead?

 

 

Occasional Contributor
Posts: 6

Re: How do I use an excel workbook as a data source without a named range?

That's how I am currently doing it. I was just trying to avoid using macros or going out and saving as a csv each time the workbook is updated. Thanks for the help!
Super User
Posts: 3,100

Re: How do I use an excel workbook as a data source without a named range?

You have more options if you were to import your workbook using SAS and SAS/ACCESS to PC Files instead of Dataflux. Is this a possibility?

Occasional Contributor
Posts: 6

Re: How do I use an excel workbook as a data source without a named range?

I think the only SAS tool I have access to is DataFlux (I don't know for sure, I am somewhat new to the company and SAS). But I think I'm going to stick with your suggestion about creating the CSV and importing that way.
Super User
Posts: 3,100

Re: How do I use an excel workbook as a data source without a named range?

OK. Perhaps you could ask a SAS / Dataflux Administrator what SAS products are available at your company in case you want to try other options in the future. 

Ask a Question
Discussion stats
  • 7 replies
  • 415 views
  • 0 likes
  • 3 in conversation