BookmarkSubscribeRSS Feed
Stever1
Calcite | Level 5

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!

7 REPLIES 7
RonAgresta
SAS Employee

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

Stever1
Calcite | Level 5
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!
SASKiwi
PROC Star

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

 

 

Stever1
Calcite | Level 5
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!
SASKiwi
PROC Star

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?

Stever1
Calcite | Level 5
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.
SASKiwi
PROC Star

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1290 views
  • 0 likes
  • 3 in conversation