Bypass or Ignore ERROR: Couldn't find range or sheet in spreadsheet for batch purposes

[Ksmit]

Hi,

I have a series of batch programs that terminate due to "ERROR: Couldn't find range or sheet in spreadsheet". What's happening is my xlsx import is seeking 6 potential tabs from the files in the FTP directory. If the file does not have the tab i get the error during import. 

Is there an OPTION to bypass this error?

I have tried the NOSYNTAXCHECK and NOERRORABEND prior to my data step and still get the error in log. 

[Kurt_Bremser]

Be happy that you were alerted to the fact that you received incomplete/inconsistent data. Do NOT try to suppress such ERRORs.

[TomKari]

The typical solution for this problem is to pre-check your incoming data to figure out exactly what it contains, and then adapt your software to only grab that data.

Tom

[Ksmit]

Thanks. So, I have a need for imported xlsx files to compile a flat file from data associated with 6 specific tabs (there is potentially 11 tabs worth of data).

I have set up the FTP directory and have the following code to apply memname as sheet name:

/*create library for xlsx file*/
libname indata xlsx "/basedata/sasuser1/********/&file_name";

/*run proc contents on library*/
proc contents data=indata._all_ out=work.contents noprint;
/*store first sheet name in macro variable*/
data _null_;
set contents (keep=memname);
call symput('sheet_name',memname);
run;


/*store results in datasets*/

data file_src_&i (drop=Tries ID);
length file_name $200. file_path $200.;
set indata."&sheet_name"n;
sheet="&sheet_name";
file_name = "&file_name";
file_path = "&folder_path";
file_count = &i;
run;
This code doesn't render an error, however, the contents table applies to only one file at a time and limits the memnames to only those in the one file.

What I ended up doing was writing the 6 data steps where the sheet name is called directly. See below example:
data file_PROCESSED_src_&i (drop=Tries ID);
length file_name $200. file_path $200.;
set indata.PROCESSED;
sheet='PROCESSED';
file_name = "&file_name";
file_path = "&folder_path";
file_count = &i;
run;

This works. It populates all the data associated with PROCESSED tabs, however, if the PROCESSED tab doesn't exist it renders the error, breaks the batch.

Any help is greatly apprecited.

[TomKari]

Here's some code that will hopefully help.

1. I switched a SQL call to the DICTIONARY table for your use of PROC CONTENTS. Probably not much difference, I'm just more familiar with it.

2. If I understand what you're trying to do, the key is to run a macro for as many occurences as you have worksheets.

Good luck!

   Tom

%let i = 1;
%let file_name = ABCD;
%let folder_path = DEFG;

/*create library for xlsx file*/
libname indata xlsx "C:\workingfolder\TestXL.xlsx";

/* Get the member names in the library */
proc sql noprint;
create table work.contents as
select memname from dictionary.members where libname="INDATA";
run;

%macro GetSheet(sheet_name, i);
data file_src_&i (drop=Tries ID);
length file_name $200. file_path $200.;
set indata."&sheet_name"n;
sheet="&sheet_name";
file_name = "&file_name";
file_path = "&folder_path";
file_count = &i;
run;
%mend;

data _null_;
set contents (keep=memname);
ExecuteLine = '%GetSheet('||strip(memname)||','||strip(put(_n_, best15.))||');';
call execute(ExecuteLine);
run;

[Ksmit]

Thanks Tom. I worked in your code and I don't get an error. What is happening though is the code is only puling 1 file into SAS, and not the complete file with all tabs. It applies the file_count (&I) to each separate sheet but it is the same data in each. So where sheet name = "A" I have the same data as sheet name "B" but file count is 1 in "A" and 2 in "B".

Thoughts?

[TomKari]

Hi, @Ksmit 

Nope, my mind is completely blank (as usual!)

Oddly enough, I had just copied the first sheet into the other two, so I wasn't even able to tell if that was happening with me! But I've modified the data, and the code seems to be working fine in my environment.

Here's a few thoughts.

Here's a modifed version of the last "data _null_" step, where it keeps the results. In my case, the "LinesToSubmit" dataset looks like this

SASSHEET1 %GetSheet(SASSHEET1,1);
SASSHEET2 %GetSheet(SASSHEET2,2);
SASSHEET3 %GetSheet(SASSHEET3,3);

which is exactly what I would expect.

data LinesToSubmit;
set contents (keep=memname);
ExecuteLine = '%GetSheet('||strip(memname)||','||strip(put(_n_, best15.))||');';
call execute(ExecuteLine);
run;

See what it looks like in your case.

Also, I'm attaching the Excel file that I'm using. If you're comfortable opening it, you can take a look and see if I've misunderstood something that you're trying to do.

Can't wait to hear,

   Tom

[Ksmit]

Tom,
This works like a charm for one file. Where I am struggling is that I have multiple files to bring in and run through this process. I'm thinking I need to add a %do loop to the process build but am coming up short with the syntax/logic.

I added path from contents. This is same as file_name so it labels each Line with the file_name:
/* Get the member names in the library */
proc sql noprint;
create table work.contents as
select memname, substr(path,29,200) as path from dictionary.members where libname="INDATA";
run;

This call of the macro:
data submittedlines;
set contents (keep=memname path);
ExecuteLine = '%GetSheet('||strip(path)||','||strip(memname)||','||strip(put(_n_, best15.))||');';
call execute(ExecuteLine);
run;
Brings back all Tabs within last file in directory (# 11 of 11 files in the directory ).



This code:
data submittedlines;
set contents (keep=memname path);
ExecuteLine = '%GetSheet('||strip(path)||','||strip(memname)||');';
call execute(ExecuteLine);
run;
Brings back the last populated tab but processes through all 11 files in directory.

My thoughts are that I need to loop the whole process once per file count. Any suggestions?

Kyle


/*clear libref*/
libname indata clear;

[TomKari]

Okay, now it's becoming clearer. If I understand correctly, you want to do this process, but for multiple Excel workbooks. So we need to repeat the line

     libname indata xlsx "C:\workingfolder\TestXL.xlsx";

for multiple Windows files. Please correct me if I'm wrong.

First question; how do you know the names of all of the workbooks that you want to retrieve data from?

Tom

[Ksmit]

You are correct.The filenames are pulled from the defined directory with the following data step:

data all_files;
infile ftp_dir;
input;
file_name = _INFILE_;
file_path = strip(file_name);
if find(file_name,".xlsx") > 0 and find(file_name,"~") = 0;
run;

[TomKari]

When I saw you're first post, I heard a little voice in my head saying "Don't reply! Don't reply!". 😂

Okay, here's an attempt to loop through multiple workbooks, and then to loop through multiple spreadsheets within the workbooks. Give it a spin, and hopefully it'll give you some thoughts that will put you on the right path.

Good luck!

   Tom

data all_files;
length file_name file_path $500;
file_path = "C:\workingfolder";
input file_name;
cards;
TestXL1.xlsx
TestXL2.xlsx
TestXL3.xlsx
run;

%macro GetFile(folder_path, file_name, i);

%macro GetSheet(folder_path, file_name, sheet_name, i, j);
data file_src_&i._&j.;
length file_name $200. file_path $200.;
set indata."&sheet_name"n;
sheet="&sheet_name";
file_name = "&file_name";
file_path = "&folder_path";
file_count1 = &i;
file_count2 = &j;
run;
%mend;

/*create library for xlsx file*/
libname indata xlsx "&folder_path.\&file_name.";

/* Get the member names in the library */
proc sql noprint;
create table work.contents as
select memname from dictionary.members where libname="INDATA";
quit;

data SheetsToSubmit;
length ExecuteLine $500;
set contents (keep=memname);
ExecuteLine = '%GetSheet(' || strip("&folder_path.") || ',' || strip("&file_name.") || ',' || strip(memname) || ',' || strip("&i.") || ',' || strip(put(_n_, best15.)) || ');';
call execute(ExecuteLine);
run;
%mend;

data BooksToSubmit;
length ExecuteLine $500;
set all_files;
ExecuteLine = '%GetFile(' || strip(file_path) || ',' || strip(file_name) || ',' || strip(put(_n_, best15.)) || ');';
call execute(ExecuteLine);
run;

[Ksmit · Accepted solution]

Thanks Tom. This helped me spot where my issue lied. Due to not all files having relavant tab names and those with tab_names often having zero obs was seeing unsynchronised file names based on load iteration. File name associated with table and that associated with contents weren't matching when the file didnt have the proper tab so it would break with "data doesn't exist" error.

My Solution was to do nested due loops within a macro theat calls the Tab name : %macro alias(PROCESSED) and added this code based on obs count:
/*Get the member names in the library */
proc contents data=indata._all_ out=work.contents noprint;run;

/*accounts for tab iterations with null observations*/
data _null_;
call symput('obscnt',0);
set work.contents;
where memname in ("&tab_name");
call symput('obscnt',_n_);
stop;
run;

%If &obscnt=0 %then %do;
%put 'There are no records in a dataset.';
%end;
%else %do;

proc sql noprint;
create table work.contents_with_file_name as
select distinct memname, "&file_name" LENGTH=200 as file_name_contents from work.contents
where memname in ("&tab_name");
;quit;

%if &i = 1 %then %do;
data append_contents;
set contents_with_file_name;
run;
%end;
%else %do;
data append_contents;
set append_contents contents_with_file_name;
run;
%end;

THis got me to where I was able to compile datasets without null values into the final output for that single tab name. I followed by calling the macro separate times based on the subsequent tab names.

Thanks so much for your help as it pointed me in the right direction.

[TomKari]

Very pleased that you got to where you needed to go!

Tom