Error importing an excel spreadsheet

[cau83]

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:

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).

[ballardw]

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]

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]

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

[cau83]

@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]

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