conversion of excel data into spps.

[Mukesh]

I have 75 variables and i converted data from excel sheet to SPSS. All varibles are there in spss now but the columns after the 75th are shown as VAR76, VAR77, VAR78....etc. in the data view we can see dots in every columns. how can solve this. please help

[Mukesh]

Highlighting all the cells in spss and deleting is the only remedy? i have to highlight upto column number 16795  then.

[David Marso]

You  could much more easily achieve this using syntax with DELETE VARIABLES command, or better fix it in the Excel file.  I don't recall ever seeing extra columns in any of my Excel imports.

[Andy W]

I've seen this on multiple occasions. I presume it happens because at one point somebody edited something in those columns, although the current spreadsheet probably has nothing in them.

It is not that big of a deal though, like David said DELETE VARIABLES or using MATCH FILES with a DROP command suffices as well.

MATCH FILES FILE = * /DROP VAR?? TO VAR??.

Just fill in the ?? with the appropriate numbers. Simple for a one off job. If really needed you could code up a python script to search for these, or do the obverse and have a list of variables to keep.

[Jignesh Sutar]

In excel, delete all the columns to the right of the last active column (do this not by clear contents or delete button but actually deleting the columns) then re-save the file.

Now read file into SPSS and it should not pick up those blank columns.

If it still does persist, and if it is a one time operation then you can include in your syntax the variable names of those blank columns to exclude, using MATCH FILES / DELETE VARIABLES. But otherwise you can use the python code below (after downloading spssaux2 module) to dynamically find all empty variables and choose to delete them from the data (it would also delete any valid columns/variables that happen to be empty also so could affect your final datamap and so use with caution).


BEGIN PROGRAM .
import spssaux2
#print spssaux2.FindEmptyVars()
spssaux2.FindEmptyVars(delete=True)
END PROGRAM

[Maguin, Eugene]

I haven't followed this very closely at all so I'm assuming you are the same person that posted on "excel to spss problems" earlier today. If so, I think the suggestion (other than editing the excel file) was use either the Delete variables command, which is the easiest, or the clever match files construction
Match file file=*/keep=var1 to var75.

I interpret the dots that you see in var76 to var(huge number) as the sysmis character. Do those columns in the excel file have valid data in them? If there is no valid data and there is supposed to be no valid data in those columns, meaning that only 75 columns of data are expected, it might be time to track upstream to find out how the excel file acquired 16000+ columns.

Gene Maguin





-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Mukesh
Sent: Monday, March 09, 2015 2:02 PM
To: [hidden email]
Subject: conversion of excel data into spps.

I have 75 variables and i converted data from excel sheet to SPSS. All varibles are there in spss now but the columns after the 75th are shown as VAR76, VAR77, VAR78....etc. in the data view we can see dots in every columns. how can solve this. please help



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/conversion-of-excel-data-into-spps-tp5728949.html
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

[Mike]

I must be getting old because even though I followed the
responses to the email below (which just came in) I don't
understand why everyone is suggesting that the columns
or variables should be DELETED.

Given that the OP knows that there should be 75 variables,
wouldn't this be a better solution be once the data is in SPSS:

save outfile=' file name '/KEEP= var1 to var75.
get file= etc.

I mean, one doesn't even have to check how many "new variables"
there are.  Am I missing something?

-Mike Palij
New York University
[hidden email]



----- Original Message -----
From: "Mukesh" <[hidden email]>
To: <[hidden email]>
Sent: Monday, March 09, 2015 2:02 PM
Subject: conversion of excel data into spps.

>I have 75 variables and i converted data from excel sheet to SPSS. All
> varibles are there in spss now but the columns after the 75th are
> shown as
> VAR76, VAR77, VAR78....etc. in the data view we can see dots in every
> columns. how can solve this. please help
>
>
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/conversion-of-excel-data-into-spps-tp5728949.html
> 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

[David Marso]

Could save two keystrokes and run
ADD FILES FILE * / KEEP v1 TO v75.
;-)

Maguin, Eugene wrote

I haven't followed this very closely at all so I'm assuming you are the same person that posted on "excel to spss problems" earlier today. If so, I think the suggestion (other than editing the excel file) was use either the Delete variables command, which is the easiest, or the clever match files construction
Match file file=*/keep=var1 to var75.

I interpret the dots that you see in var76 to var(huge number) as the sysmis character. Do those columns in the excel file have valid data in them? If there is no valid data and there is supposed to be no valid data in those columns, meaning that only 75 columns of data are expected, it might be time to track upstream to find out how the excel file acquired 16000+ columns.

Gene Maguin





-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Mukesh
Sent: Monday, March 09, 2015 2:02 PM
To: [hidden email]
Subject: conversion of excel data into spps.

I have 75 variables and i converted data from excel sheet to SPSS. All varibles are there in spss now but the columns after the 75th are shown as VAR76, VAR77, VAR78....etc. in the data view we can see dots in every columns. how can solve this. please help



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/conversion-of-excel-data-into-spps-tp5728949.html
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

[David Marso]

I confess my response was primed by OP using word deleting ;-)
MATCH or ADD with KEEP trumps SAVE as it doesn't require actually wring the file/passing the data.

Mike Palij wrote

I must be getting old because even though I followed the
responses to the email below (which just came in) I don't
understand why everyone is suggesting that the columns
or variables should be DELETED.

Given that the OP knows that there should be 75 variables,
wouldn't this be a better solution be once the data is in SPSS:

save outfile=' file name '/KEEP= var1 to var75.
get file= etc.

I mean, one doesn't even have to check how many "new variables"
there are.  Am I missing something?

-Mike Palij
New York University
[hidden email]



----- Original Message -----
From: "Mukesh" <[hidden email]>
To: <[hidden email]>
Sent: Monday, March 09, 2015 2:02 PM
Subject: conversion of excel data into spps.


>I have 75 variables and i converted data from excel sheet to SPSS. All
> varibles are there in spss now but the columns after the 75th are
> shown as
> VAR76, VAR77, VAR78....etc. in the data view we can see dots in every
> columns. how can solve this. please help
>
>
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/conversion-of-excel-data-into-spps-tp5728949.html
> 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

[Bruce Weaver]

Furthermore, GET TRANSLATE has /DROP and /KEEP subcommands; and GET DATA has a CELLRANGE subcommand for for TYPE = XLS, XLSX, and XLSM*.  ;-)

Mike Palij wrote

I must be getting old because even though I followed the
responses to the email below (which just came in) I don't
understand why everyone is suggesting that the columns
or variables should be DELETED.

Given that the OP knows that there should be 75 variables,
wouldn't this be a better solution be once the data is in SPSS:

save outfile=' file name '/KEEP= var1 to var75.
get file= etc.

I mean, one doesn't even have to check how many "new variables"
there are.  Am I missing something?

-Mike Palij
New York University
[hidden email]



----- Original Message -----
From: "Mukesh" <[hidden email]>
To: <[hidden email]>
Sent: Monday, March 09, 2015 2:02 PM
Subject: conversion of excel data into spps.


>I have 75 variables and i converted data from excel sheet to SPSS. All
> varibles are there in spss now but the columns after the 75th are
> shown as
> VAR76, VAR77, VAR78....etc. in the data view we can see dots in every
> columns. how can solve this. please help
>
>
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/conversion-of-excel-data-into-spps-tp5728949.html
> 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

[Mike]

On Monday, March 09, 2015 9:40 PM, Bruce Weaver wrote:
> Furthermore, GET TRANSLATE has /DROP and /KEEP subcommands;
> and GET DATA has  a CELLRANGE subcommand for for
>TYPE = XLS, XLSX, and XLSM*.  ;-)

Kudos to Bruce, David, and others who showed alternative
and simpler ways to solving the problem.  Like they say,
there's more than one skin to skin whatever it is you skin. ;-)

-Mike Palij
New York University
[hidden email]

> Mike Palij wrote
>> I must be getting old because even though I followed the
>> responses to the email below (which just came in) I don't
>> understand why everyone is suggesting that the columns
>> or variables should be DELETED.
>>
>> Given that the OP knows that there should be 75 variables,
>> wouldn't this be a better solution be once the data is in SPSS:
>>
>> save outfile=' file name '/KEEP= var1 to var75.
>> get file= etc.
>>
>> I mean, one doesn't even have to check how many "new variables"
>> there are.  Am I missing something?
>>
>> -Mike Palij
>> New York University
>>
>>
>> ----- Original Message -----
>> From: "Mukesh" &lt;
>
>> mugesh.panthalil@
>
>> &gt;
>> To: &lt;
>
>> SPSSX-L@.UGA
>
>> &gt;
>> Sent: Monday, March 09, 2015 2:02 PM
>> Subject: conversion of excel data into spps.
>>
>>
>>>I have 75 variables and i converted data from excel sheet to SPSS.
>>>All
>>> varibles are there in spss now but the columns after the 75th are
>>> shown as
>>> VAR76, VAR77, VAR78....etc. in the data view we can see dots in
>>> every
>>> columns. how can solve this. please help
>>>
>>>
>>>
>>> --
>>> View this message in context:
>>> http://spssx-discussion.1045642.n5.nabble.com/conversion-of-excel-data-into-spps-tp5728949.html
>>> Sent from the SPSSX Discussion mailing list archive at 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
>>
>> =====================
>> 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.
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/conversion-of-excel-data-into-spps-tp5728949p5728961.html
> 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

[Andy W]

That would be fine Mike if the variable names were like that, but here is an example of data files I have had before.

*********************************.
SPSSINC GETURI DATA URI="https://dl.dropboxusercontent.com/u/3385251/Test_ExtraCol.xls"
                    FILETYPE=XLS DATASET=Test
  /OPTIONS SHEETNUMBER=1 READNAMES=YES ASSUMEDSTRWIDTH=32767.

*Or download the file and use this.
 * GET DATA
  /TYPE=XLS
  /FILE='Your Handle\' + 'Test_ExtraCol.xls'
  /SHEET=name 'Sheet1'
  /CELLRANGE=full
  /READNAMES=on
  /ASSUMEDSTRWIDTH=32767.
 * EXECUTE.
*********************************.

In this case using formatting in the top row caused SPSS to read those columns, and so the variable list is:

Num V2 V3 V4 V5

I presume the OP did not know they had 75 variables until they opened up the file in SPSS!

I'm surprised others have not encountered this - this has happened pretty regularly to me. Just another reason why XLS is a very poor format to disseminate data in - although this is pretty benign compared to some of the other problems.

[Mike]

Andy, okay, I see your point but, as David M mentioned, I have never
seen this situation either.  Then again, I create my own Excel files
and make sure that they are in "pure" form (i.e., just numbers and
labels).  When you get an Excel file from another person, well,
you never know where it has been. ;-)

I find the Excel dataset you created below to be odd because it
generates a number of error messages when I try to use keep
(on the machine I'm on right now, SPSS can't find the file with
the list of error messages so I can't say what SPSS thinks the
errors are).  I wonder why this is the case.  I'll try it on another
machine later to see what the error messages are but I think
that SPSS should not be behaving this way.  But I can't figure
out what is causing the problem.  Anyone?

-Mike Palij
New York University
[hidden email]



On Tuesday, March 10, 2015 8:24 AM, Andy W wrote:

> That would be fine Mike if the variable names were like that, but here
> is an
> example of data files I have had before.
>
> *********************************.
> SPSSINC GETURI DATA
> URI="https://dl.dropboxusercontent.com/u/3385251/Test_ExtraCol.xls"
>                    FILETYPE=XLS DATASET=Test
>  /OPTIONS SHEETNUMBER=1 READNAMES=YES ASSUMEDSTRWIDTH=32767.
>
> *Or download the file and use this.
> * GET DATA
>  /TYPE=XLS
>  /FILE='Your Handle\' + 'Test_ExtraCol.xls'
>  /SHEET=name 'Sheet1'
>  /CELLRANGE=full
>  /READNAMES=on
>  /ASSUMEDSTRWIDTH=32767.
> * EXECUTE.
> *********************************.
>
> In this case using formatting in the top row caused SPSS to read those
> columns, and so the variable list is:
>
> Num V2 V3 V4 V5
>
> I presume the OP did not know they had 75 variables until they opened
> up the
> file in SPSS!
>
> I'm surprised others have not encountered this - this has happened
> pretty
> regularly to me. Just another reason why XLS is a very poor format to
> disseminate data in - although this is pretty benign compared to some
> of the
> other problems.
>

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

[Jignesh Sutar]

Also, the valid variable list could change from one delivery to the next, if this analysis is to be repeated periodically with potential of new variables being added? Of course if it just a once off operation, then MATCH FILES using either KEEP or DROP would be more than adequate.

[David Marso]

I ran the GETURI code and received:
>Warning.  Command name: GET DATA
>(2101) The column contained no recognized type; defaulting to "Numeric[8,2]"
>* Column 2 , SAME: Columns 3:5
 
Opening the file in Excel reveals 5 columns in range, col1 header Num with 1,2,3,4,5...
col 2:5 blank.
----
I have had bastardized Excel files in the past, largely when people put NA, -  or other crap in fields which should be numeric.  Sometimes extra columns, but never something with a RANGE of 15K+ columns as OP was describing.

Mike Palij wrote

Andy, okay, I see your point but, as David M mentioned, I have never
seen this situation either.  Then again, I create my own Excel files
and make sure that they are in "pure" form (i.e., just numbers and
labels).  When you get an Excel file from another person, well,
you never know where it has been. ;-)

I find the Excel dataset you created below to be odd because it
generates a number of error messages when I try to use keep
(on the machine I'm on right now, SPSS can't find the file with
the list of error messages so I can't say what SPSS thinks the
errors are).  I wonder why this is the case.  I'll try it on another
machine later to see what the error messages are but I think
that SPSS should not be behaving this way.  But I can't figure
out what is causing the problem.  Anyone?

-Mike Palij
New York University
[hidden email]



On Tuesday, March 10, 2015 8:24 AM, Andy W wrote:
> That would be fine Mike if the variable names were like that, but here
> is an
> example of data files I have had before.
>
> *********************************.
> SPSSINC GETURI DATA
> URI="https://dl.dropboxusercontent.com/u/3385251/Test_ExtraCol.xls"
>                    FILETYPE=XLS DATASET=Test
>  /OPTIONS SHEETNUMBER=1 READNAMES=YES ASSUMEDSTRWIDTH=32767.
>
> *Or download the file and use this.
> * GET DATA
>  /TYPE=XLS
>  /FILE='Your Handle\' + 'Test_ExtraCol.xls'
>  /SHEET=name 'Sheet1'
>  /CELLRANGE=full
>  /READNAMES=on
>  /ASSUMEDSTRWIDTH=32767.
> * EXECUTE.
> *********************************.
>
> In this case using formatting in the top row caused SPSS to read those
> columns, and so the variable list is:
>
> Num V2 V3 V4 V5
>
> I presume the OP did not know they had 75 variables until they opened
> up the
> file in SPSS!
>
> I'm surprised others have not encountered this - this has happened
> pretty
> regularly to me. Just another reason why XLS is a very poor format to
> disseminate data in - although this is pretty benign compared to some
> of the
> other problems.
>

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

[Jignesh Sutar]

When SPSS reads a Excel file, it scans each column to assess whether that same column should be initialised as a string or numeric (or any other format type) in SPSS itself. If it contains no data to make that judgment call it defaults to a numeric, and is the (trivial) warnings you note.

[Mike]



Andy, sorry, mistake on my end. Right you are, keep works

as advertised, just need to have a cup of coffee before I start

typing. ;-)

 

-Mike Palij

New York University

[hidden email]

 

 

----- Original Message -----

From: [hidden email]

To: [hidden email]

Sent: Tuesday, March 10, 2015 10:40 AM

Subject: Re: conversion of excel data into spps.

I swear the excel file does not have any viruses! Below works fine for me - only error messages are about the empty columns (which is to be expected).

***********************.

SPSSINC GETURI DATA

URI="https://dl.dropboxusercontent.com/u/3385251/Test_ExtraCol.xls"

                   FILETYPE=XLS DATASET=Test

 /OPTIONS SHEETNUMBER=1 READNAMES=YES ASSUMEDSTRWIDTH=32767.

MATCH FILES FILE = * /KEEP = Num.

EXECUTE.

***********************.

On Tue, Mar 10, 2015 at 10:27 AM, Mike Palij <[hidden email]> wrote:

Andy, okay, I see your point but, as David M mentioned, I have never
seen this situation either.  Then again, I create my own Excel files
and make sure that they are in "pure" form (i.e., just numbers and
labels).  When you get an Excel file from another person, well,
you never know where it has been. ;-)

I find the Excel dataset you created below to be odd because it
generates a number of error messages when I try to use keep
(on the machine I'm on right now, SPSS can't find the file with
the list of error messages so I can't say what SPSS thinks the
errors are).  I wonder why this is the case.  I'll try it on another
machine later to see what the error messages are but I think
that SPSS should not be behaving this way.  But I can't figure
out what is causing the problem.  Anyone?

-Mike Palij
New York University


On Tuesday, March 10, 2015 8:24 AM, Andy W wrote:

That would be fine Mike if the variable names were like that, but here is an
example of data files I have had before.

*********************************.
SPSSINC GETURI DATA
URI="https://dl.dropboxusercontent.com/u/3385251/Test_ExtraCol.xls"
                   FILETYPE=XLS DATASET=Test
 /OPTIONS SHEETNUMBER=1 READNAMES=YES ASSUMEDSTRWIDTH=32767.

*Or download the file and use this.
* GET DATA
 /TYPE=XLS
 /FILE='Your Handle\' + 'Test_ExtraCol.xls'
 /SHEET=name 'Sheet1'
 /CELLRANGE=full
 /READNAMES=on
 /ASSUMEDSTRWIDTH=32767.
* EXECUTE.
*********************************.

In this case using formatting in the top row caused SPSS to read those
columns, and so the variable list is:

Num V2 V3 V4 V5

I presume the OP did not know they had 75 variables until they opened up the
file in SPSS!

I'm surprised others have not encountered this - this has happened pretty
regularly to me. Just another reason why XLS is a very poor format to
disseminate data in - although this is pretty benign compared to some of the
other problems.

--

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