Read in all Excel files in a directory and grab specific info from each one

[Walternate]

Hi all,

I need a code snippet which will:

For each Excel file in a directory, I need:

             1. The value in column A . This value repeats throughout the Excel spreadsheet, but doesn't start until the 3rd row:

Excel files:

Row     Value

1           Title

2           More Title

3           Data value

4           Same data value repeats until end of file

              2. I also need the NUMBER which is in the title of each Excel file. It's always in the same place, eg:

           excel_file_name_##(##)_moretitle.xlsx

             3. What I need is an output Excel file which contains, for each Excel file, the value from Column A and the # in the title:

Row 1 of output file: ColA                                                 ColB              

Row 2 of output file: "Value in ColA of input file"              Title# associated with input file                (for file 1 and etc. for all other Excel files)

[Reeza]

Haven't tried it in a while but this code imports all excel files in a directory. Figure out how to do steps 3 for each file and then the rest is pretty trivial.

If you can create your code for one file we can help automate the rest as  well. 

https://github.com/statgeek/SAS-Tutorials/blob/master/Import_all_files_one_type

%*Creates a list of all files in the DIR directory with the specified extension (EXT);
%macro list_files(dir,ext);
	%local filrf rc did memcnt name i;
	%let rc=%sysfunc(filename(filrf,&dir));
	%let did=%sysfunc(dopen(&filrf));

	%if &did eq 0 %then
		%do;
			%put Directory &dir cannot be open or does not exist;

			%return;
		%end;

	%do i = 1 %to %sysfunc(dnum(&did));
		%let name=%qsysfunc(dread(&did,&i));

		%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then
			%do;
				%put &dir\&name;
				%let file_name =  %qscan(&name,1,.);
				%put &file_name;

				data _tmp;
					length dir $512 name $100;
					dir=symget("dir");
					name=symget("name");
					path = catx('\',dir,name);
					the_name = substr(name,1,find(name,'.')-1);
				run;

				proc append base=list data=_tmp force;
				run;

				quit;

				proc sql;
					drop table _tmp;
				quit;

			%end;
		%else %if %qscan(&name,2,.) = %then
			%do;
				%list_files(&dir\&name,&ext)
			%end;
	%end;

	%let rc=%sysfunc(dclose(&did));
	%let rc=%sysfunc(filename(filrf));
%mend list_files;

%*Macro to import a single file, using the path, filename and an output dataset name must be specified;
%macro import_file(path, file_name, dataset_name );

	proc import 
		datafile="&path.\&file_name."
		dbms=xlsx
		out=temp replace;
                range="Sheet1!A1:B3";
	run;
      * do processing here as needed to manipulate file;
      *this isn't really macro code, just file manipulation;
       data &dataset_name;
       set temp;
       filename = "&file_name"; 
       run;
       
%mend;

*Create the list of files, in this case all XLSX files;
%list_files(c:\_localData\temp, xlsx);

%*Call macro once for each entry in the list table created from the %list_files() macro;
data _null_;
	set list;
	string = catt('%import_file(', dir, ', ',  name,', ', catt('test', put(_n_, z2.)), ');');
	call execute (string);
run;