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

[researcher]

I would be grtaful for help with an excel conversion problem. I have an excel file, some survey data, carried out in a very ugly way (not by me) in google forms. Much cleaning to do, but first i need to get data into SPSS without SPSS changing it all into string vars For example a respondent has written a response (shown in the excel file) such as '12-15' (meaning roughly how many years they have worked in a job) and SPSS converts this, on conversion, to a string value such as 44556 which I do not want. I want it to still say 12-15 after it gets into SPSS (and I will then use autorecode). I imagine I am missing something obvious. Have done a lot of conversion of csv, excel etc into SPSS but not come across this particular issue before All help appreciated! M

---

Sent from the SPSSX Discussion mailing list archive at 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

[Jon Peck]

If there is a nonnumeric value in a column, Statistics will import the column as a string, but it shouldn't change the values.  Can you provide a file with a column that misbehaves like that?  I suspect that its data type is not what it seems.  You could send it to me at [hidden email]

On Wed, Aug 12, 2020 at 10:42 AM researcher <[hidden email]> wrote:

I would be grtaful for help with an excel conversion problem. I have an excel file, some survey data, carried out in a very ugly way (not by me) in google forms. Much cleaning to do, but first i need to get data into SPSS without SPSS changing it all into string vars For example a respondent has written a response (shown in the excel file) such as '12-15' (meaning roughly how many years they have worked in a job) and SPSS converts this, on conversion, to a string value such as 44556 which I do not want. I want it to still say 12-15 after it gets into SPSS (and I will then use autorecode). I imagine I am missing something obvious. Have done a lot of conversion of csv, excel etc into SPSS but not come across this particular issue before All help appreciated! M

---

Sent from the SPSSX Discussion mailing list archive at 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

--
===================== 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 Jon I have messaged you with a link to a sample of the data file. Your
advice would be grealy appreciated


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]

Ps further experiments suggest saving file as CSV first (not excel) may solve
this but am too tired now to be sure!  



--
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

[Richard Ristow]

Boy, am I rusty!  But let me see if I can contribute.

As Jon Peck said, there's no way you can get some of what you say you want.  Specifically, you may have the value that displays as "12-15", and you don't want to import it as a string value;  but "12-15" is not a possible numeric value in SPSS.

One approach with your kind of situation is to force EVERYTHING to enter as strings; then, at least, you'll have an exact representation of what was in the original, and you can work on it with a transformation program.  In a situation like this, I usually add a row in Excel before the first real data row, having entirely string values:  "aaa","aaaaa", etc.;  make sure that the value in the added row is at least as wide as the widest value in the data. (Of course, if you have columns that you know to be well-behaved numeric, you can make the value in the added row numeric for those columns.)

You may have trouble with dates; I haven't done this recently enough to be sure.  If you're lucky, dates will import as strings matching how they display in Excel.  Excel internal date formats are different from SPSS internal formats. If you let the column be read as numeric, AND if the dates are all true Excel dates, SPSS will try to, and usually succeed, converting them to SPSS date values, but cells that are strings, or otherwise not valid Excel date values, may come through as something very strange; that's a reason for coercing all values to be SPSS strings, as I've described, and re-convert with transformation code.

Good luck. Something like this is a good deal of work, so much that for a small project, hand-entering the data while cleaning it up may be easier.

Finally, this may be screamingly obvious, but once you've imported the data from Excel to SPSS, say entirely as string variables, don't do anything to change it -- run your transformation program to convert as needed, and save as a DIFFERENT SPSS file. That way, any mistake can be traced back and corrected by a revised transformation program.

===================== 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

[Jon Peck]

If you select the entire columns and choose to format as text, you see what is going on.  Excel will then display those cells formatted as dates as the internal date value that it uses, which accounts for the strange numbers, and that is what you get on the SPSS import.  As strings aka text, you get the same result as Excel shows.

Hi, Richard.  Long time since you have posted here.

On Wed, Aug 12, 2020 at 4:48 PM Richard Ristow <[hidden email]> wrote:

Boy, am I rusty!  But let me see if I can contribute.

As Jon Peck said, there's no way you can get some of what you say you want.  Specifically, you may have the value that displays as "12-15", and you don't want to import it as a string value;  but "12-15" is not a possible numeric value in SPSS.

One approach with your kind of situation is to force EVERYTHING to enter as strings; then, at least, you'll have an exact representation of what was in the original, and you can work on it with a transformation program.  In a situation like this, I usually add a row in Excel before the first real data row, having entirely string values:  "aaa","aaaaa", etc.;  make sure that the value in the added row is at least as wide as the widest value in the data. (Of course, if you have columns that you know to be well-behaved numeric, you can make the value in the added row numeric for those columns.)

You may have trouble with dates; I haven't done this recently enough to be sure.  If you're lucky, dates will import as strings matching how they display in Excel.  Excel internal date formats are different from SPSS internal formats. If you let the column be read as numeric, AND if the dates are all true Excel dates, SPSS will try to, and usually succeed, converting them to SPSS date values, but cells that are strings, or otherwise not valid Excel date values, may come through as something very strange; that's a reason for coercing all values to be SPSS strings, as I've described, and re-convert with transformation code.

Good luck. Something like this is a good deal of work, so much that for a small project, hand-entering the data while cleaning it up may be easier.

Finally, this may be screamingly obvious, but once you've imported the data from Excel to SPSS, say entirely as string variables, don't do anything to change it -- run your transformation program to convert as needed, and save as a DIFFERENT SPSS file. That way, any mistake can be traced back and corrected by a revised transformation program.

===================== 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

[researcher]

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
[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

[Art Kendall]

If you want 12-15 to saty that way you need to treat teh variable as text.

On some occasions, I have found it helpful to use pipe delimiters "|" rather
than comma delimiters, since it has been extremely rare for this character
to be used for any purpose.



-----
Art Kendall
Social Research Consultants
--
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

[Bruce Weaver]

Saving as tab-delimited should eliminate the issue with comment fields that
can include commas.  I made up a small Excel file to try that, and it mostly
works.  But as you'll see below, the range of years sometimes shows Max-Min
rather than Min-Max.  Further code could identify and fix those if
necessary, but I don't have time to pursue that right now.  

PRESERVE.
SET DECIMAL DOT.

GET DATA  /TYPE=TXT
  /FILE="C:\Temp\junk.txt"
  /ENCODING='UTF8'
  /DELCASE=LINE
  /DELIMITERS="\t"
  /ARRANGEMENT=DELIMITED
  /FIRSTCASE=2
  /DATATYPEMIN PERCENTAGE=95.0
  /VARIABLES=
  ID AUTO
  JobExp AUTO
  Comment AUTO
  /MAP.
RESTORE.

LIST.

DO REPEAT
 old = "Jan" "Feb" "Mar" "Apr" "May" "Jun" "Jul" "Aug" "Sep" "Oct" "Nov"
"Dec" /
 new = "1" "2" "3" "4" "5" "6" "7" "8" "9" "10" "11" "12".
COMPUTE JobExp = REPLACE(JobExp,old,new).
END REPEAT.

LIST.

Output from the first LIST:

ID JobExp Comment
 
 1 15-Dec "Well, I don't know!"
 2 3-Jan  "Let me see, yes."
 3 11-Sep No comment
 4 14-17  "blah, blah, blah"

Output from the second LIST:

ID JobExp Comment
 
 1 15-12  "Well, I don't know!"
 2 3-1    "Let me see, yes."
 3 11-9   No comment
 4 14-17  "blah, blah, blah"

HTH.



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





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
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]

Thank you - but the issue is that I cannot get excel or SPSS to treat '12-15'
as text - it insists in converting it to a numeric value such as 34221 which
i thing is a string value for the supposed date. have tried formatting whole
file as text only in excel - doesn't do it.



--
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  - interesting and I have tried using tab rater than comma but
not been able to make that work. I think i need a simple and quick solution
or else I may as well manually (or using search and replace)  change each
instance of 12-15 (e.g. to 12-15 years) in excel  and then it is preserved
that way on import, to SPSS, no problem. I think you have a sophsiticated
solution there but the time to make hat work would not be justified as
compared to the crude manual option I mention above (i.e. to write some text
after 12-15 so that excel and SPSS are in no doubt it is text and not date,



--
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

[Bruce Weaver]

You appear to be posting from Nabble--this was in the footer of your last
post:

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

Please upload a sample Excel file that illustrates the problem if you can
without violating privacy, etc.  In the composition window, click on More >
Upload a file.  Thanks.  




researcher wrote

> Thank you - but the issue is that I cannot get excel or SPSS to treat
> '12-15'
> as text - it insists in converting it to a numeric value such as 34221
> which
> i thing is a string value for the supposed date. have tried formatting
> whole
> file as text only in excel - doesn't do it.
>
>
>
> --
> 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





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
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

[Jon Peck]

Here is a magic solution.  In the Excel file,

1) select an entire column (you might have to do this column by column)

2) Go to  Data and select Text to Columns

3) Check the Delimited box

4) Do Next and uncheck ALL the delimiters

5) Do Next and select Text as the format

6) Finish

Save the file, and you will be good to go

On Thu, Aug 13, 2020 at 9:37 AM researcher <[hidden email]> wrote:

Thanks Bruce  - interesting and I have tried using tab rater than comma but
not been able to make that work. I think i need a simple and quick solution
or else I may as well manually (or using search and replace)  change each
instance of 12-15 (e.g. to 12-15 years) in excel  and then it is preserved
that way on import, to SPSS, no problem. I think you have a sophsiticated
solution there but the time to make hat work would not be justified as
compared to the crude manual option I mention above (i.e. to write some text
after 12-15 so that excel and SPSS are in no doubt it is text and not date,



--
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

--
===================== 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

[Bruce Weaver]

I carried out those steps on a small Excel file I had created, and saved it
as Junk2.xlsx.  But after importing it, I still had to trim "/2020" from the
right end on some rows, and then on those same rows, I had to replace the
remaining "/" with "-".  

GET DATA
  /TYPE=XLSX
  /FILE='C:\Temp\Junk2.xlsx'
  /SHEET=name 'Sheet1'
  /CELLRANGE=FULL
  /READNAMES=ON
  /DATATYPEMIN PERCENTAGE=95.0
  /HIDDEN IGNORE=YES.

LIST.
COMPUTE JobExp = REPLACE(JobExp, "/2020", "").
COMPUTE JobExp = REPLACE(JobExp,"/", "-").
LIST.

Output from the first LIST:

ID JobExp     Comment
 
 1 12/15/2020 Well, I don't know!
 2 1/3/2020   Let me see, yes.
 3 9/11/2020  No comment
 4 14-17      blah, blah, blah

And from the second LIST:

ID JobExp     Comment
 
 1 12-15      Well, I don't know!
 2 1-3        Let me see, yes.
 3 9-11       No comment
 4 14-17      blah, blah, blah




Jon Peck wrote
> Here is a magic solution.  In the Excel file,
> 1) select an entire column (you might have to do this column by column)
> 2) Go to  Data and select Text to Columns
> 3) Check the Delimited box
> 4) Do Next and uncheck ALL the delimiters
> 5) Do Next and select Text as the format
> 6) Finish
>
> Save the file, and you will be good to go





-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
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 Jon - I did try that  - as I know that little move form old when
trying to (insist) with excel that a column is or is not a date field! This
is usually the only way to force excel to change it to date or not date.
Still I iwll give it another go. Thankyou



--
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 can't upload a file directly as far as I'm aware but I can
send a PM with a link to file. Most grateful for the help form all. I will
give it another shot tomorrow. I would like to ban people form doing surveys
on  googleforms, typeform etc. !



--
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

[Jon Peck]

I tried that on your file, and it worked.

On Thu, Aug 13, 2020 at 12:53 PM researcher <[hidden email]> wrote:

Thanks Jon - I did try that  - as I know that little move form old when
trying to (insist) with excel that a column is or is not a date field! This
is usually the only way to force excel to change it to date or not date.
Still I iwll give it another go. Thankyou



--
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

--
===================== 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 Jon! I only gave you part of the file though and did not work when I
tried it on whole file previously,  so maybe I need to do it again,
carefully,  one column at a time and see how that goes. Sounds promising!
thank you all



--
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

[Jon Peck]

Excel only allows you to do this one column at  a time.  I have no idea why that restriction exists.  Maybe it is expecting to expand the column into additional columns based on finding delimiters, but in this case you would be tricking it by having no delimiters selected.

On Thu, Aug 13, 2020 at 1:06 PM researcher <[hidden email]> wrote:

Thanks Jon! I only gave you part of the file though and did not work when I
tried it on whole file previously,  so maybe I need to do it again,
carefully,  one column at a time and see how that goes. Sounds promising!
thank you all



--
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

--
===================== 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

[hillel vardi]

Shalom

Adding to the other answers one can change  all the - (minus sign) to _ underscore  in the excel sheet.

Excel treat  12 - 15 (or 12-15)  as date but 12_15 as string, there after you can change it back to 12 -15 in SPSS if you really need it with the minus sign .

Hillel Vardi

On 13/08/2020 13:48, 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
[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