topic Re: How do I use an excel workbook as a data source without a named range? in SAS Data Management

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

Stever1 — Fri, 29 Jul 2016 15:31:23 GMT

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!

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

RonAgresta — Sun, 31 Jul 2016 19:12:43 GMT

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

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

SASKiwi — Sun, 31 Jul 2016 19:49:14 GMT

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

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

Stever1 — Mon, 01 Aug 2016 12:04:33 GMT

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!

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

Stever1 — Mon, 01 Aug 2016 12:06:34 GMT

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!

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

SASKiwi — Mon, 01 Aug 2016 19:43:16 GMT

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?

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

Stever1 — Mon, 01 Aug 2016 19:50:01 GMT

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.

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

SASKiwi — Tue, 02 Aug 2016 05:34:14 GMT

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.