How can I read in multiple excel files in SPSS usi

Question

I'm trying to write a Macro to import 15 files, all in the same format. The name format is the "monyy PSF Extract". So I can use the code below to read in the 1 file for Nov11. I've tried to find a way of using the macro to read in also the other 14 files. I can't seem to make it work. I'm new to SPSS - I knew how to do this in SAS. I also want to set the dataset created to be named monyy. I will also want to rename some variables as original_name_monyy.

Can someone help me on this please?It is drivign me nuts!

define !XLSFILE() !quote(!con("S:\Credit Risk\Credit Risk\Elisabeth\",!unquote(!eval(!cq)), ".xlsx")) !enddefine.
define !cq(mon = !DEFAULT ("Nov11") !token(1) /name = !DEFAULT ("PSF Extract") !TOKENS(2)) !quo(!con(!unq(!mon),!unq(" "), !unq(!name))) !enddefine.


/* import xlsx file */.
GET DATA
  /TYPE=XLSX
  /FILE=!XLSFILE
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.

EXECUTE.

DATASET NAME  test WINDOW=FRONT.

Answer 1

You can pass a list of file name pre-fixes in a macro call and loop through loading the files. Below is how I would approach this. It is a bit restrictive in that you need to pass the list of months, but it is fairly trivial a task.

*******************************************************.
DEFINE !XLSFILE(location = !TOKENS(1)
                /names = !CMDEND).

!DO !monthfile !IN (!names)

!LET !XLSFILE = !QUOTE(!CONCAT(!UNQUOTE(!location),!monthfile," PSF Extract.xlsx"))

/* import xlsx file */.
GET DATA
  /TYPE=XLSX
  /FILE=!XLSFILE
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
*Name dataset.
dataset name !monthfile.

**PLACE RENAME COMMANDS HERE.
*Example changing [XVAR1] and [XVAR2] to [XVAR1_monyy] and [XVAR2_monyy].
rename variables (XVAR1 = !CONCAT("XVAR1","_",!monthfile))
(XVAR2 = !CONCAT("XVAR2","_",!monthfile)).

!DOEND.

*now do whatever you want with the datasets, eg add files them together.
!ENDDEFINE.
*******************************************************.

*call the macro.
set mprint on.
!XLSFILE location = "S:\Credit Risk\Credit Risk\Elisabeth\"
names = Jan11 Feb11 Mar11 Apr11 May11 Jun11 Jul11 Aug11 Sep11 Oct11 Nov11 Dec11. 

Answer 2

An alternative would be to use Python programmability with a wildcard expression to pick up all the files in a particular location with a specified name pattern. More flexible and avoids the need to struggle with macros. You can find out more by download the Programmaing and Data Management book from the Books and Articles section of the SPSS Community site at www.ibm.com/developerworks/spssdevcentral