I've never been able to get the ".sav" or ".csv" to work within the macro, I've always omitted it. One thing I have had issues with is that the folder it's in always becomes part of the file name. This is what I have for csv. I do have a bit of python where I could slap the '.sav' and get it to work, but I'd rather do it this way.
*process sav to csv.
SPSSINC PROCESS FILES INPUTDATA='C:\datab\e22\test\*.sav'
SYNTAX="c:\General Syntax 21-22\convertSavToCSV.sps" CONTINUEONERROR=YES
VIEWERFILE= "c:\datab\e21\convert.spv" CLOSEDATA=YES.
define !out () !quote(!concat(!unquote(!eval(!JOB_DATADIR)),
!unquote(!eval(!JOB_DATAFILEROOT)))) !enddefine.
GET FILE=JOB_INPUTFILE.
SAVE TRANSLATE OUTFILE=!out
/TYPE=CSV
/ENCODING='UTF8'
/MAP
/REPLACE
/FIELDNAMES
/CELLS=VALUES.
------------------------------
Art Jack
------------------------------
Original Message:
Sent: Mon January 24, 2022 02:06 PM
From: Jon Peck
Subject: Convert Excel files to SPSS
That macro definition is missing the ".sav" extension. It should be
define !out () !quote(!concat(!unquote(!eval(!JOB_OUTPUTDATADIR)), !unquote(!eval(!JOB_DATAFILEROOT)), ".sav")) !enddefine.
--
Original Message:
Sent: 1/24/2022 1:56:00 PM
From: Art Jack
Subject: RE: Convert Excel files to SPSS
XLSX to SAV
I wasn't able to get this to work. It's processing the files but saving the outputs as a mystery file, with no '.sav' format. I'm able to run something similar to convert 'sav' to 'csv' & 'sav' to 'xls'.
*process excel to sav.
SPSSINC PROCESS FILES INPUTDATA='c:\DataB\e22\Test\Files\*.xlsx'
SYNTAX="c:\General Syntax 21-22\convertXLSToSAV.sps" CONTINUEONERROR=YES
VIEWERFILE= "c:\datab\e21\convert.spv" CLOSEDATA=YES.
define !out () !quote(!concat(!unquote(!eval(!JOB_DATADIR)),
!unquote(!eval(!JOB_DATAFILEROOT)))) !enddefine.
GET DATA /TYPE=XLSX /FILE=JOB_INPUTFILE /SHEET=name 'Sheet1' /CELLRANGE=full /READNAMES=on /ASSUMEDSTRWIDTH=100.
save outfile=!OUT
/COMPRESSED.
execute.
------------------------------
Art Jack
Original Message:
Sent: Fri January 21, 2022 11:51 AM
From: Jon Peck
Subject: Convert Excel files to SPSS
As Rick, predicted, there is a solution for this. It requires the SPSSINC PROCESS FILES extension command, which can be installed via the Extensions > Extension Hub menu.
This command applies a syntax file you provide to the files selected by a wildcard expression or list of files. It defines a file handle for each file and then runs your syntax file on it.
The syntax help for the command has an example of converting Excel files to sav files. (Put your cursor on an instance of the command and press F1 to see it.)
Here is the syntax file you might use. It first defines a macro that generates the sav file name from the input Excel file name using the output data directory you specify to PROCESS FILES.
Then it opens the Excel file using the provided file handle, JOB_INPUTFILE, and writes the file out using the name and location constructed in the macro.
define !out () !quote(!concat(!unquote(!eval(!JOB_OUTPUTDATADIR)), !unquote(!eval(!JOB_DATAFILEROOT)), ".sav")) !enddefine. GET DATA /TYPE=XLSX /FILE=JOB_INPUTFILE /SHEET=name 'Sheet1' /CELLRANGE=full /READNAMES=on /ASSUMEDSTRWIDTH=100. SAVE OUTFILE=!out
--
Original Message:
Sent: 1/21/2022 11:12:00 AM
From: Art Jack
Subject: Convert Excel files to SPSS
Is there a way to convert all of the excel files in a folder to SPSS without pulling them in one by one? Thanks.
------------------------------
Art Jack
------------------------------
#SPSSStatistics