SPSS Statistics

SPSS Statistics

Your hub for statistical analysis, data management, and data documentation. Connect, learn, and share with your peers! 

 View Only
  • 1.  Importing from xlsx

    Posted Thu October 10, 2024 03:06 PM

    I am on SPSS V30 on Windows 11 Version 23H2.

    I am 'pretty sure' this worked just fine earlier today, but I cannot import or open xlsx files any longer.

    There must be something that I do wrong. What jumps to your eye?

    Thanks a lot

    Jos Blykers

    DATASET ACTIVATE DataSet3. 
    GET DATA 
      /TYPE=XLSX 
      /FILE='C:\Users\005705624\Box\My Files 624 005705\Documents\2011 AOLC\AOLC\AOLC Analytics '+ 
        'Project\Survey Consolidation\Baseline 2012-2023- rev 5-20-24 Jane ter.xlsx' 
      /SHEET=name 'Data' 
      /CELLRANGE=FULL 
      /READNAMES=ON 
      /LEADINGSPACES IGNORE=YES 
      /DATATYPEMIN PERCENTAGE=95.0 
      /HIDDEN IGNORE=YES. 
     
    >Error.  Command name: GET DATA 
    >(2063) Error reading information for sheet. 
    >* Sheet Name: "Data" 
    >Execution of this command stops. 
    EXECUTE. 
     
    >Error # 105.  Command name: EXECUTE 
    >This command is not valid before a working file has been defined. 
    >Execution of this command stops. 
    DATASET NAME DataSet8 WINDOW=FRONT.



    ------------------------------
    Jos Blykers
    ------------------------------


  • 2.  RE: Importing from xlsx

    Posted Thu October 10, 2024 03:34 PM
    Did the sheet name change?  I'd try /sheet = index 1 or whatever tab you need.

    Sent from my iPhone





  • 3.  RE: Importing from xlsx

    Posted Fri October 11, 2024 09:16 AM
    Edited by Curtis Browning Fri October 11, 2024 09:16 AM

    Hi @Jos Blykers,

    It sounds like there may be one or more file handles open on that Excel workbook. Does the problem fix itself after a reboot? Also, one should not have the workbook open in Excel when attempting to import it - that could potentially cause the issue too.

    Best,



    ------------------------------
    Curtis Browning
    SPSS Statistics Architect
    ------------------------------



  • 4.  RE: Importing from xlsx

    Posted Fri October 11, 2024 10:24 AM

    Trying it out - will give feedback in this thread. Thanks Curtis and Art.



    ------------------------------
    Jos Blykers
    ------------------------------



  • 5.  RE: Importing from xlsx

    Posted Fri October 11, 2024 03:06 PM

    I am not entirely sure what solved the problem, but I had indeed many xls sheets open AND column format in xlsx was not properly defined. So, I managed to upload xlsx to SPSS again.

    However, that led to another problem for  which I did not find an elegant solution. My xlsx is more than 10,000 rows long, with a mix of numerical and text data. Also with date fields. The majority of date fields are blank. But I do have hundreds even thousands of rows with valuable date information. When loading and converting xlsx to SPSS, the blank fields make SPSS interpret those fields as string, regardless of how I defined them in xlsx. I also played with selecting a lower percentage of rows to be used a indicator for the field type, to no avail (I cannot go under 50%). I also browsed the Command Reference GET DATA pages for a solution that may force xlsx to convert to a date format.

    I seem to lose the date information when applying ALTER TYPE (DATE11).  Another cumbersome solution might be to fill the blanks temporarily with a date like 01/01/2025 to force the conversion of the field to a date format and then undo again in SPSS.  But there may be a more elegant solution.



    ------------------------------
    Jos Blykers
    ------------------------------



  • 6.  RE: Importing from xlsx

    Posted Fri October 11, 2024 03:09 PM
    In syntax you can specify the field type for each one.--