SPSS Statistics

 View Only
  • 1.  Convert Excel files to SPSS

    Posted Fri January 21, 2022 11:12 AM
    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


  • 2.  RE: Convert Excel files to SPSS

    Posted Fri January 21, 2022 11:22 AM
    Automation (e.g., Python scripting) would be the way to do this.

    If you know some Python, then roughly something like this:

    files = glob.glob( "/path/*.sav", recursive=False)
    for f in files:
      getfile="GET FILE '" + f + "'."
      spss.Submit(getfile)
      spss.Submit("SAVE TRANSLATE + commands.")

    My guess is that Jon Peck has something ready-made to do the trick that will appear here soon. If not, I will send you more precise code to do it.​

    ------------------------------
    Rick Marcantonio
    Quality Assurance
    IBM
    ------------------------------



  • 3.  RE: Convert Excel files to SPSS

    IBM Champion
    Posted Fri January 21, 2022 11:52 AM
    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


    --





  • 4.  RE: Convert Excel files to SPSS

    Posted Mon January 24, 2022 01:56 PM
    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
    ------------------------------



  • 5.  RE: Convert Excel files to SPSS

    IBM Champion
    Posted Mon January 24, 2022 02:07 PM
    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.

    --





  • 6.  RE: Convert Excel files to SPSS

    Posted Mon January 24, 2022 02:24 PM
    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
    ------------------------------



  • 7.  RE: Convert Excel files to SPSS

    IBM Champion
    Posted Mon January 24, 2022 03:15 PM
    I think you need to include the / separator as below.

    !quote(!concat(!unquote(!eval(!job_datadir)), "/", !unquote(!eval(!job_datafileroot)), ".sav"))
    !enddefine.

    --