BookmarkSubscribeRSS Feed
cau83
Pyrite | Level 9

I am trying to import a spreadsheet that is provided to me by someone else. I assume this is a query they run on a database or application with whatever formats/properties that come out of there. (NOTE: I have a meeting with them in a few hours, the plan was not to address this but one potential solution to the problem is to have them copy the text into another spreadsheet or otherwise alter the file).

I can import it okay if I do not check the Use a specific range of cells within the worksheet. However, I *THINK* I need to do that because the list they send me may vary in length each month and so I'm checking both that and the Expand row range as needed box. (I have the same error whether or not I check the latter).

When I check that I want to select the range, instead of pre-populating the top-left cell and lower-right cell boxes have errors and remain blank. I fill them in and press blank and get the following error:

egerror.JPG

I had not used the 'specific' range before, but, I just did it for another spreadsheet from a different user and did not get this error.

So, any clues as to how to address this error?

Alternately, any other workarounds so that I ensure I get all of the data in their spreadsheet (whether in SAS or Excel, I can change the process if necessary though the goal is to have it automated from my perspective).

5 REPLIES 5
ballardw
Super User

If the data change you expect is only more or fewer rows with the same columns I don't think you need the specific range option at all, especially since you said you could read one file without the option.

My headaches with these things usually come from the process changing the number of header rows, order or number of columns and presence of summary or footnote type rows in the spreadsheet. If those don't happen then whatever reads the first example should continue. Hopefully they'll tell when the business logic requires changes like changed columns later...

cau83
Pyrite | Level 9

you know, that makes sense. I was using the specific range option for the first time also because there were multiple reference tables on one excel sheet in another location.

I'm not sure I ever consciously knew that the import task would adjust, but I went and looked at the SAS code used and it's a data step so it should just go until it finds the final record.

ChrisHemedinger
Community Manager

Just because you see the DATA step used, don't assume that's what is reading the Excel sheet.  Actually, EG reads the Excel file to determine the columns and ranges available, and you can use the task to override what EG detects.  EG then reads the Excel file content and turns it into a text-based data source, which then a DATA step can process.

For more information, see:

Behind the scenes: importing Excel files using SAS Enterprise Guide - The SAS Dummy

Chris

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!
cau83
Pyrite | Level 9

@Chris@SAS,

We do not have SAS/ACCESS - if that's relevant to this discussion.

Do you know the source of the error I posted originally?

Can you confirm that if the spreadsheet has rows added to it later that the import task will bring them all in?

ChrisHemedinger
Community Manager

Yes, it should bring all of the rows in -- unless you specified a specific range.  You would specify a specific range only when there are completely blank rows in your sheet or if your table begins in a non-standard location (not cell A-1).

If you specify a specific range, and then check the box to "extend range as needed", then that might help the situation.  But it's still something that you should double-check to ensure you're capturing everything.

Chris

Check out SAS Innovate on-demand content! Watch the main stage sessions, keynotes, and over 20 technical breakout sessions!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1282 views
  • 6 likes
  • 3 in conversation