In syntax you can specify the field type for each one.
--
Original Message:
Sent: 10/11/2024 3:06:00 PM
From: Jos Blykers
Subject: RE: Importing from xlsx
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
------------------------------
Original Message:
Sent: Fri October 11, 2024 10:23 AM
From: Jos Blykers
Subject: Importing from xlsx
Trying it out - will give feedback in this thread. Thanks Curtis and Art.
------------------------------
Jos Blykers
Original Message:
Sent: Fri October 11, 2024 09:15 AM
From: Curtis Browning
Subject: Importing from xlsx
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
Original Message:
Sent: Thu October 10, 2024 03:06 PM
From: Jos Blykers
Subject: Importing from xlsx
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
------------------------------