IMPORT to SPSS from EXCEL 'as is ' (no changes to data)

[PRogman]

I am curious. Exactly what is entered in the cell displayed as "12-15", shown
in the bar above the spread sheet when the cell is selected?
It must be a date value which includes a year (44534 btw is Dec 4 2021),
with a custom date format.
The 'MM-DD' date format have never been standard in the MS Excel versions I
have encountered.
One solution is to compute a new column in Excel to keep single values and
extract the month and date from the date serial. The statement in the cell
(to be copied to all data lines) could be like this (assuming your response
in B2, and 1000 being an impossible response):
=IF(B2>1000;MONTH(B2)&"-"&DAY(B2);B2)
When opening/importing the Excel file you force the determination of data
type to be 100%, which will force a string import. Then you have to do more
stuff before analysis.
If you choose to import the date serial it can be converted to a julian date
number spss-style as in this post:
http://spssx-discussion.1045642.n5.nabble.com/Conversion-of-date-Julian-variable-td5737319.html#a5737357
Then some more data massage with XDATE.MONTH, XDATE.MDAY and STRING to build
your string value. Then, again, you have to do more stuff before analysis.
Which way you choose depends on your data set size, number of response
variables and proficiency in Excel/SPSS.
HTH,  /PR



researcher wrote

> Thank you all.
>
> Firstly to clarify I do not want any date info. the problems is that that
> the data (e.g. 12-15) which refers to 12-15 years' job experience , is
> being
> understood as a date (e.g. 15 Dec) and then converted to a string that
> represents that date such as 44534 etc . But I want it to remain as 12-15
> when it gets into SPSS
>
> No amount of formatting if the excel file (e.g. formatting all cells as
> text) seems to change this
>
> Converting to csv seems to work but gives me a whole load of other
> problems
> because commas occur ins some open text responses and so if using comma as
> separator then everything gets screwed.
>
>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD





--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[PRogman]

This syntax may help, it creates strJE as a string value of JobExp
HTH,  /PR

*[Begin]*****.
GET DATA
  /TYPE         = XLSX
  /FILE         = 'Indata.xlsx'
  /SHEET        = NAME 'Sheet 1'
  /CELLRANGE    = FULL
  /READNAMES    = ON
  /LEADINGSPACES  IGNORE    = YES
  /TRAILINGSPACES IGNORE    = YES
  /DATATYPEMIN    PERCENTAGE= 100
  /HIDDEN         IGNORE    = YES.
EXECUTE.

*[Massage]*****.
STRING strJE (A10).
COMPUTE #xlStartDate = DATE.DMY(31, 12, 1899).
IF (JobExp GT 1000) #spssDate     = #xlStartDate + (JobExp - (JobExp GT 60))
* TIME.DAYS(1).
DO IF (JobExp GT 1000).
  COMPUTE strJE = CONCAT(STRING(XDATE.MONTH(#spssDate), F2), "-",
STRING(XDATE.MDAY(#spssDate), F2)).
ELSE.
  COMPUTE strJE = STRING(JobExp, F8).
END IF.
COMPUTE strJE = REPLACE(strJE, ' ', '').
EXECUTE.
*[End]*****.






--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[researcher]

Thanks Bruce - I was also unable to make that work although I know it has on
other occasions,with other files. So some sort of hidden info in this file,
or part of it,  which is insisting it's date i guess. Anyway I had to do it
the hard way - went to all instances of 1-2 and changed to 1-2 years and no
problems then to keep it that way when importing, Luckily this only effected
2 variables and not a huge number of cases otherwise i might have been
screwed.

Excel really should have a function to instantly make all fields text.

Thanks so much to all for helping - this group is great for those  cold
sweat moments when deadlines approach and one has run out of new solutions
to try!

M









--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[researcher]

Thanks you that is a reasonable solution but is equivalent to the one i used
which is to write in (or serach and replace) the word ''years'' after the
12-15 (or indeed nay word would do)  which also forces excel to regard as
string and so is imported to SPSS as is '12-15 years'' and can then be
treated wit 'autorecode' in the usual way to get to a numeric variable.  So
I agree with your solution and i used something on the same lines but it
seems crazy to have to do this. But sometimes the 'low tech' solution is the
easiest.



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Baker, Harley]

Another solution, and easier one, is simply to insert a dummy first row in the Excel file that has a number in each numeric data column and a letter in each string/non-numeric column. If possible, this likely would be much easier and faster than a series of search/replace operations in Excel. Then simply tell SPSS to delete the first row after the file is imported into SPSS and before any analyses are conducted.

Dr. Harley Baker
Professor Emeritus
California State University Channel Islands

Sent from my iPad

> On Aug 16, 2020, at 3:50 AM, researcher <[hidden email]> wrote:
>
> CAUTION: This email originated from outside of CSUCI. Do not click links or open attachments unless you validate the sender and know the content is safe. Please forward this email to [hidden email] if you believe this email is suspicious. For more information on how to detect Phishing scams, please visit https://www.csuci.edu/its/security/phishing.htm
>
>
> Thanks you that is a reasonable solution but is equivalent to the one i used
> which is to write in (or serach and replace) the word ''years'' after the
> 12-15 (or indeed nay word would do)  which also forces excel to regard as
> string and so is imported to SPSS as is '12-15 years'' and can then be
> treated wit 'autorecode' in the usual way to get to a numeric variable.  So
> I agree with your solution and i used something on the same lines but it
> seems crazy to have to do this. But sometimes the 'low tech' solution is the
> easiest.
>
>
>
> --
> Sent from: https://nam01.safelinks.protection.outlook.com/?url=http%3A%2F%2Fspssx-discussion.1045642.n5.nabble.com%2F&amp;data=02%7C01%7CHarley.Baker%40CSUCI.EDU%7C6c4bf4ab56c94cb743f608d841d22727%7Ce30f5bdb7f18435b84369d84aa7b96dd%7C1%7C0%7C637331718150488469&amp;sdata=1ZHxpmK2uis6Cu22uIUl4fLEjC2wg1X1aOGTajJ06lU%3D&amp;reserved=0
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD