Excel importing with missing data

classic Classic list List threaded Threaded
32 messages Options
12
Reply | Threaded
Open this post in threaded view
|

Excel importing with missing data

Rajeshms
Dear all,

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

--
Regards,

Rajesh M S



Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Maguin, Eugene

I think you mean this but I’m not sure. The excel file SHOULD have 832 cases but it also has a bunch of blank records that come into spss as all sysmis. Solution, the only one that I know is to delete the blank records from the excel file. However, you could write a bit of spss code to delete the records in the spss data file. Assuming all variables being numeric, count instances of sysmis across the variable list and delete records with all variables missing. All string or mixed string and numeric is harder.

 

Gene Maguin

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data

 

Dear all,

 

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

 

--

Regards,

 

Rajesh M S

 

 

 

Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

David Marso
Administrator
"All string or mixed string and numeric is harder."
---
data list list / a b c (3F) d e f (3A).
begin data
1 2 3 a b c
4 5 6 d e f


end data.

PRESERVE.
SET ERRORS OFF.
COMPUTE #numvars=6.
*------*.
COMPUTE #deadnum=0.
COMPUTE #deadstring=0.
DO REPEAT v=a TO f.
+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
END REPEAT.
SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
RESTORE.

Maguin, Eugene wrote
I think you mean this but I’m not sure. The excel file SHOULD have 832 cases but it also has a bunch of blank records that come into spss as all sysmis. Solution, the only one that I know is to delete the blank records from the excel file. However, you could write a bit of spss code to delete the records in the spss data file. Assuming all variables being numeric, count instances of sysmis across the variable list and delete records with all variables missing. All string or mixed string and numeric is harder.

Gene Maguin

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data

Dear all,

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

--
Regards,

Rajesh M S
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Martha Hewett
In reply to this post by Maguin, Eugene
When you import data from Excel using the menus, at least, you see a window that says "Opening Excel Data Source" and there you can use a pull-down list to specify which of the sheets in the workbook you want to import, and you can fill in the Range.  So for a little test case I just tried, the Worksheet pulldown says Sheet1[A1:C5] but if I fill in the range to say A1:C4 it will not read in the 5th row.

Martha Hewett  | 
Director of Research | 612.335.5865
Center for Energy and Environment
212 Third Avenue North, Suite 560 | Minneapolis, MN 55401
(cell) 612.839.2358 | (fax) 612.335.5888 | www.mncee.org



On Thu, Feb 21, 2013 at 7:57 AM, Maguin, Eugene <[hidden email]> wrote:

I think you mean this but I’m not sure. The excel file SHOULD have 832 cases but it also has a bunch of blank records that come into spss as all sysmis. Solution, the only one that I know is to delete the blank records from the excel file. However, you could write a bit of spss code to delete the records in the spss data file. Assuming all variables being numeric, count instances of sysmis across the variable list and delete records with all variables missing. All string or mixed string and numeric is harder.

 

Gene Maguin

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data

 

Dear all,

 

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

 

--

Regards,

 

Rajesh M S

 

 

 




This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

henryilian
In reply to this post by Rajeshms

Hi Rajesh

 

I’ve had that problem many times. It comes from the fact that if cells have been used at any time in the spreadsheet, Excel sees them as active cells, even if there is nothing currently in them.

 

What I do is copy the data set, being careful to highlight the data and nothing else, into a fresh spreadsheet—an unused tab in the same workbook will work. I import the copied data into SPSS, and there are no phantom cases.

 

Henry

 


From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data

 

Dear all,

 

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

 

--

Regards,

 

Rajesh M S

 

 

 



Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Martha Hewett
You can also just "delete" all the rows in your spreadsheet that no longer have data.
Martha Hewett  | 
Director of Research | 612.335.5865
Center for Energy and Environment
212 Third Avenue North, Suite 560 | Minneapolis, MN 55401
(cell) 612.839.2358 | (fax) 612.335.5888 | www.mncee.org



On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <[hidden email]> wrote:

Hi Rajesh

 

I’ve had that problem many times. It comes from the fact that if cells have been used at any time in the spreadsheet, Excel sees them as active cells, even if there is nothing currently in them.

 

What I do is copy the data set, being careful to highlight the data and nothing else, into a fresh spreadsheet—an unused tab in the same workbook will work. I import the copied data into SPSS, and there are no phantom cases.

 

Henry

 


From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data

 

Dear all,

 

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

 

--

Regards,

 

Rajesh M S

 

 

 



Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.



This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Martha Hewett
If I delete the whole row instead of the contents, it works for me.

Martha Hewett  | 
Director of Research | 612.335.5865
Center for Energy and Environment
212 Third Avenue North, Suite 560 | Minneapolis, MN 55401
(cell) 612.839.2358 | (fax) 612.335.5888 | www.mncee.org



On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <[hidden email]> wrote:

If you delete rows in a spreadsheet, Excel still counts the same number of rows as part of the data area of the spread sheet, and SPSS will still bring in empty rows. The best solution is to copy only what you want to bring into SPSS into a fresh spreadsheet—it can be a new tab in the current workbook—and bring the data in from the new worksheet.

 

Henry

 


From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Martha Hewett
Sent: Thursday, February 21, 2013 12:31 PM
To: [hidden email]
Subject: Re: Excel importing with missing data

 

You can also just "delete" all the rows in your spreadsheet that no longer have data.

Martha Hewett  | 

Director of Research | 612.335.5865

Center for Energy and Environment

212 Third Avenue North, Suite 560 | Minneapolis, MN 55401

(cell) 612.839.2358 | (fax) 612.335.5888 | www.mncee.org

 

On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <[hidden email]> wrote:

Hi Rajesh

 

I’ve had that problem many times. It comes from the fact that if cells have been used at any time in the spreadsheet, Excel sees them as active cells, even if there is nothing currently in them.

 

What I do is copy the data set, being careful to highlight the data and nothing else, into a fresh spreadsheet—an unused tab in the same workbook will work. I import the copied data into SPSS, and there are no phantom cases.

 

Henry

 


From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data

 

Dear all,

 

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

 

--

Regards,

 

Rajesh M S

 

 

 

 


Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

 


This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission




This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Rajeshms
Thanks to all,
 
But am looking for this not to happen. I am creating a syntax for future works, so that any person can work on it with out going to details of data pulling and manipulating,transforming and other tricks.

I doubt some thing is wrong.Anyways am doing some research on this..........

regards,

Rajesh M S


On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <[hidden email]> wrote:
If I delete the whole row instead of the contents, it works for me.

Martha Hewett  | 
Director of Research | <a href="tel:612.335.5865" value="+16123355865" target="_blank">612.335.5865
Center for Energy and Environment
212 Third Avenue North, Suite 560 | Minneapolis, MN 55401
(cell) <a href="tel:612.839.2358" value="+16128392358" target="_blank">612.839.2358 | (fax) <a href="tel:612.335.5888" value="+16123355888" target="_blank">612.335.5888 | www.mncee.org



On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <[hidden email]> wrote:

If you delete rows in a spreadsheet, Excel still counts the same number of rows as part of the data area of the spread sheet, and SPSS will still bring in empty rows. The best solution is to copy only what you want to bring into SPSS into a fresh spreadsheet—it can be a new tab in the current workbook—and bring the data in from the new worksheet.

 

Henry

 


From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Martha Hewett
Sent: Thursday, February 21, 2013 12:31 PM
To: [hidden email]
Subject: Re: Excel importing with missing data

 

You can also just "delete" all the rows in your spreadsheet that no longer have data.

Martha Hewett  | 

Director of Research | <a href="tel:612.335.5865" value="+16123355865" target="_blank">612.335.5865

Center for Energy and Environment

212 Third Avenue North, Suite 560 | Minneapolis, MN 55401

(cell) <a href="tel:612.839.2358" value="+16128392358" target="_blank">612.839.2358 | (fax) <a href="tel:612.335.5888" value="+16123355888" target="_blank">612.335.5888 | www.mncee.org

 

On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <[hidden email]> wrote:

Hi Rajesh

 

I’ve had that problem many times. It comes from the fact that if cells have been used at any time in the spreadsheet, Excel sees them as active cells, even if there is nothing currently in them.

 

What I do is copy the data set, being careful to highlight the data and nothing else, into a fresh spreadsheet—an unused tab in the same workbook will work. I import the copied data into SPSS, and there are no phantom cases.

 

Henry

 


From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data

 

Dear all,

 

I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.

 

--

Regards,

 

Rajesh M S

 

 

 

 


Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

 


This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission




This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission



--
Regards,

Rajesh M S



CG
Reply | Threaded
Open this post in threaded view
|

Automatic reply: Excel importing with missing data

CG

I am currently out. Please contact Fran Lucero ([hidden email]) for immediate assistance.

Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Albert-Jan Roskam
In reply to this post by Rajeshms
Hi,

Untested:

BEGIN PROGRAM.
## mimics the R function 'na.omit'
import spss, spssaux
nVars = len(spss.GetVariableNamesList())
strVars = " ".join(spssaux.VariableDict(variableType='string').variables)
numVars = " ".join(spssaux.VariableDict(variableType='numeric').variables)

spss.Submit("""compute #strVarsMissing = 0.
do repeat #strVar = %(strVars)s.
+if(length(rtrim(#strVar)) eq 0) #strVarsMissing = #strVarsMissing + 1.
end repeat.
count #numVarsMissing = %(numVars)s (sysmis).

compute #isEmptyRow = #strVarsMissing + #numVarsMissing eq  %(nVars)s.
select if not #isEmptyRow.""" % locals())
END PROGRAM.

You could write a function (let's call it na_omit), and on top of that an extension command to hide all of this.
Since it's typically something excel-related maybe that function could simply take an excel file as its argument.
 
Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

From: Rajeshms <[hidden email]>
To: [hidden email]
Sent: Friday, February 22, 2013 7:03 AM
Subject: Re: [SPSSX-L] Excel importing with missing data

Thanks to all,
 
But am looking for this not to happen. I am creating a syntax for future works, so that any person can work on it with out going to details of data pulling and manipulating,transforming and other tricks.

I doubt some thing is wrong.Anyways am doing some research on this..........

regards,

Rajesh M S


On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <[hidden email]> wrote:
If I delete the whole row instead of the contents, it works for me.

Martha Hewett  | 
Director of Research | 612.335.5865
Center for Energy and Environment
212 Third Avenue North, Suite 560 | Minneapolis, MN 55401
(cell) 612.839.2358 | (fax) 612.335.5888 | www.mncee.org



On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <[hidden email]> wrote:
If you delete rows in a spreadsheet, Excel still counts the same number of rows as part of the data area of the spread sheet, and SPSS will still bring in empty rows. The best solution is to copy only what you want to bring into SPSS into a fresh spreadsheet—it can be a new tab in the current workbook—and bring the data in from the new worksheet.
 
Henry
 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Martha Hewett
Sent: Thursday, February 21, 2013 12:31 PM
To: [hidden email]
Subject: Re: Excel importing with missing data
 
You can also just "delete" all the rows in your spreadsheet that no longer have data.
Martha Hewett  | 
Director of Research | 612.335.5865
Center for Energy and Environment
212 Third Avenue North, Suite 560 | Minneapolis, MN 55401
 
On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <[hidden email]> wrote:
Hi Rajesh
 
I’ve had that problem many times. It comes from the fact that if cells have been used at any time in the spreadsheet, Excel sees them as active cells, even if there is nothing currently in them.
 
What I do is copy the data set, being careful to highlight the data and nothing else, into a fresh spreadsheet—an unused tab in the same workbook will work. I import the copied data into SPSS, and there are no phantom cases.
 
Henry
 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Rajeshms
Sent: Thursday, February 21, 2013 6:51 AM
To: [hidden email]
Subject: Excel importing with missing data
 
Dear all,
 
I have an  Excel data, with 832 cases and there is no other cases after case number 832 in excel.But when I import the same data into spss , I can see the full data imported with other lots of missing (dot cases) data. I don know why this is coming , how to overcome this. I don need to do manual deletion of the extra missing cases in spss.,Thanks to all.
 
--
Regards,
 
Rajesh M S
 
 
 
 

Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.
 

This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission



This e-mail transmission and any attachments accompanying it may contain confidential and/or proprietary information and is intended only for the person or entity to whom it was originally addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or other use of this information is strictly prohibited. Any unauthorized interception of this transmission is illegal. If you have received this transmission in error, please notify the sender by reply e-mail, and then destroy all copies of this transmission



--
Regards,

Rajesh M S





Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Bruce Weaver
Administrator
In reply to this post by Rajeshms
The solution David posted earlier in the thread can be made a bit more general so that it works for any input file.  E.g.,

data list list / a b c (3F) d e f (3A).
begin data
1 2 3 a b c
4 5 6 d e f


end data.

* Create two placeholder variables, make one the first variable
* in the file, the other the last variable.

NUMERIC junk1 junk2 (f1).
MATCH FILES file = * / KEEP = junk1 all.
EXECUTE.

PRESERVE.
SET ERRORS OFF.

* Initialize #numvars to -2, because junk1 and junk2 don't count.
COMPUTE #numvars = -2.
*------*.
COMPUTE #deadnum=0.
COMPUTE #deadstring=0.
DO REPEAT v=junk1 TO junk2.
+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
+  COMPUTE #numvars = #numvars + 1.
END REPEAT.
SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
RESTORE.
DELETE VARIABLES junk1 junk2.
LIST.



Rajeshms wrote
Thanks to all,

But am looking for this not to happen. I am creating a syntax for future
works, so that any person can work on it with out going to details of data
pulling and manipulating,transforming and other tricks.

I doubt some thing is wrong.Anyways am doing some research on this..........

regards,

Rajesh M S


On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <[hidden email]> wrote:

> If I delete the whole row instead of the contents, it works for me.
>
> *Martha Hewett*  |  *Director of Research* | *612.335.5865* Center for
> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>
> *
>
>
> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
> [hidden email]> wrote:
>
>> ** ** ** ** ** ** **
>>
>> If you delete rows in a spreadsheet, Excel still counts the same number
>> of rows as part of the data area of the spread sheet, and SPSS will still
>> bring in empty rows. The best solution is to copy only what you want to
>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the current
>> workbook—and bring the data in from the new worksheet. ****
>>
>> ** **
>>
>> Henry****
>>
>> ** **
>>  ------------------------------
>>
>> *From:* SPSSX(r) Discussion [mailto:**[hidden email]**] *On
>> Behalf Of *Martha Hewett
>> *Sent:* Thursday, February 21, 2013 12:31 PM
>> *To:* **[hidden email]**
>> *Subject:* Re: Excel importing with missing data****
>>
>> ** **
>>
>> You can also just "delete" all the rows in your spreadsheet that no
>> longer have data.
>> ****
>>
>> *Martha Hewett*  | ****
>>
>> *Director of Research* | *612.335.5865*****
>>
>> Center for Energy and Environment****
>>
>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN** **
>> 55401********
>>
>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>
>> ** **
>>
>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>> [hidden email]> wrote:****
>>
>> Hi Rajesh****
>>
>>  ****
>>
>> I’ve had that problem many times. It comes from the fact that if cells
>> have been used at any time in the spreadsheet, Excel sees them as active
>> cells, even if there is nothing currently in them. ****
>>
>>  ****
>>
>> What I do is copy the data set, being careful to highlight the data and
>> nothing else, into a fresh spreadsheet—an unused tab in the same workbook
>> will work. I import the copied data into SPSS, and there are no phantom
>> cases.****
>>
>>  ****
>>
>> Henry****
>>
>>  ****
>>  ------------------------------
>>
>> *From:* SPSSX(r) Discussion [mailto:[hidden email]] *On Behalf
>> Of *Rajeshms
>> *Sent:* Thursday, February 21, 2013 6:51 AM
>> *To:* [hidden email]
>> *Subject:* Excel importing with missing data****
>>
>>  ****
>>
>> Dear all,****
>>
>>  ****
>>
>> I have an  Excel data, with 832 cases and there is no other cases after
>> case number 832 in excel.But when I import the same data into spss , I can
>> see the full data imported with other lots of missing (dot cases) data. I
>> don know why this is coming , how to overcome this. I don need to do manual
>> deletion of the extra missing cases in spss.,Thanks to all.
>> ****
>>
>>  ****
>>
>> -- ****
>>
>> Regards,****
>>
>>  ****
>>
>> Rajesh M S****
>>
>>  ****
>>
>>  ****
>>
>>  ****
>>
>> ** **
>>  ------------------------------
>>
>> Confidentiality Notice: This e-mail communication, and any attachments,
>> contains confidential and privileged information for the exclusive use of
>> the recipient(s) named above. If you are not an intended recipient, or the
>> employee or agent responsible to deliver it to an intended recipient, you
>> are hereby notified that you have received this communication in error and
>> that any review, disclosure, dissemination, distribution or copying of it
>> or its contents is prohibited. If you have received this communication in
>> error, please notify me immediately by replying to this message and delete
>> this communication from your computer. Thank you.****
>>
>> ** **
>>  ------------------------------
>>
>> This e-mail transmission and any attachments accompanying it may contain
>> confidential and/or proprietary information and is intended only for the
>> person or entity to whom it was originally addressed. If you are not the
>> intended recipient, you are hereby notified that any disclosure, copying,
>> distribution or other use of this information is strictly prohibited. Any
>> unauthorized interception of this transmission is illegal. If you have
>> received this transmission in error, please notify the sender by reply
>> e-mail, and then destroy all copies of this transmission****
>>
>
>
> ------------------------------
> This e-mail transmission and any attachments accompanying it may contain
> confidential and/or proprietary information and is intended only for the
> person or entity to whom it was originally addressed. If you are not the
> intended recipient, you are hereby notified that any disclosure, copying,
> distribution or other use of this information is strictly prohibited. Any
> unauthorized interception of this transmission is illegal. If you have
> received this transmission in error, please notify the sender by reply
> e-mail, and then destroy all copies of this transmission
>



--
Regards,

Rajesh M S
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Albert-Jan Roskam

....
+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
 ....

Will this really work if stand-in variable v could represent both numerical and string variables??
I'd think that either expression will cause an error if the variable is of the wrong type.

Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

From: Bruce Weaver <[hidden email]>
To: [hidden email]
Sent: Friday, February 22, 2013 3:09 PM
Subject: Re: [SPSSX-L] Excel importing with missing data

The solution David posted earlier in the thread can be made a bit more
general so that it works for any input file.  E.g.,

data list list / a b c (3F) d e f (3A).
begin data
1 2 3 a b c
4 5 6 d e f


end data.

** Create two placeholder variables, make one the first variable
* in the file, the other the last variable.

NUMERIC junk1 junk2 (f1).
MATCH FILES file = * / KEEP = junk1 all.
EXECUTE.
*
PRESERVE.
SET ERRORS OFF.

** Initialize #numvars to -2, because junk1 and junk2 don't count.
COMPUTE #numvars = -2.
**------*.
COMPUTE #deadnum=0.
COMPUTE #deadstring=0.
DO REPEAT v=junk1 TO junk2.
+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
*+  COMPUTE #numvars = #numvars + 1.*
END REPEAT.
SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
RESTORE.
*DELETE VARIABLES junk1 junk2.*
LIST.




Rajeshms wrote

> Thanks to all,
>
> But am looking for this not to happen. I am creating a syntax for future
> works, so that any person can work on it with out going to details of data
> pulling and manipulating,transforming and other tricks.
>
> I doubt some thing is wrong.Anyways am doing some research on
> this..........
>
> regards,
>
> Rajesh M S
>
>
> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett &lt;

> mhewett@

> &gt; wrote:
>
>> If I delete the whole row instead of the contents, it works for me.
>>
>> *Martha Hewett*  |  *Director of Research* | *612.335.5865* Center for
>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>
>> *
>>
>>
>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>

> Henry.Ilian@.ny

>> wrote:
>>
>>> ** ** ** ** ** ** **
>>>
>>> If you delete rows in a spreadsheet, Excel still counts the same number
>>> of rows as part of the data area of the spread sheet, and SPSS will
>>> still
>>> bring in empty rows. The best solution is to copy only what you want to
>>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the
>>> current
>>> workbook—and bring the data in from the new worksheet. ****
>>>
>>> ** **
>>>
>>> Henry****
>>>
>>> ** **
>>>  ------------------------------
>>>
>>> *From:* SPSSX(r) Discussion [mailto:**

> SPSSX-L@.UGA

> **] *On
>>> Behalf Of *Martha Hewett
>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>> *To:* **

> SPSSX-L@.UGA

> **
>>> *Subject:* Re: Excel importing with missing data****
>>>
>>> ** **
>>>
>>> You can also just "delete" all the rows in your spreadsheet that no
>>> longer have data.
>>> ****
>>>
>>> *Martha Hewett*  | ****
>>>
>>> *Director of Research* | *612.335.5865*****
>>>
>>> Center for Energy and Environment****
>>>
>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN** **
>>> 55401********
>>>
>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>
>>> ** **
>>>
>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>

> Henry.Ilian@.ny

>> wrote:****
>>>
>>> Hi Rajesh****
>>>
>>>  ****
>>>
>>> I’ve had that problem many times. It comes from the fact that if cells
>>> have been used at any time in the spreadsheet, Excel sees them as active
>>> cells, even if there is nothing currently in them. ****
>>>
>>>  ****
>>>
>>> What I do is copy the data set, being careful to highlight the data and
>>> nothing else, into a fresh spreadsheet—an unused tab in the same
>>> workbook
>>> will work. I import the copied data into SPSS, and there are no phantom
>>> cases.****
>>>
>>>  ****
>>>
>>> Henry****
>>>
>>>  ****
>>>  ------------------------------
>>>
>>> *From:* SPSSX(r) Discussion [mailto:

> SPSSX-L@.UGA

> ] *On Behalf
>>> Of *Rajeshms
>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>> *To:*

> SPSSX-L@.UGA

>>> *Subject:* Excel importing with missing data****
>>>
>>>  ****
>>>
>>> Dear all,****
>>>
>>>  ****
>>>
>>> I have an  Excel data, with 832 cases and there is no other cases after
>>> case number 832 in excel.But when I import the same data into spss , I
>>> can
>>> see the full data imported with other lots of missing (dot cases) data.
>>> I
>>> don know why this is coming , how to overcome this. I don need to do
>>> manual
>>> deletion of the extra missing cases in spss.,Thanks to all.
>>> ****
>>>
>>>  ****
>>>
>>> -- ****
>>>
>>> Regards,****
>>>
>>>  ****
>>>
>>> Rajesh M S****
>>>
>>>  ****
>>>
>>>  ****
>>>
>>>  ****
>>>
>>> ** **
>>>  ------------------------------
>>>
>>> Confidentiality Notice: This e-mail communication, and any attachments,
>>> contains confidential and privileged information for the exclusive use
>>> of
>>> the recipient(s) named above. If you are not an intended recipient, or
>>> the
>>> employee or agent responsible to deliver it to an intended recipient,
>>> you
>>> are hereby notified that you have received this communication in error
>>> and
>>> that any review, disclosure, dissemination, distribution or copying of
>>> it
>>> or its contents is prohibited. If you have received this communication
>>> in
>>> error, please notify me immediately by replying to this message and
>>> delete
>>> this communication from your computer. Thank you.****
>>>
>>> ** **
>>>  ------------------------------
>>>
>>> This e-mail transmission and any attachments accompanying it may contain
>>> confidential and/or proprietary information and is intended only for the
>>> person or entity to whom it was originally addressed. If you are not the
>>> intended recipient, you are hereby notified that any disclosure,
>>> copying,
>>> distribution or other use of this information is strictly prohibited.
>>> Any
>>> unauthorized interception of this transmission is illegal. If you have
>>> received this transmission in error, please notify the sender by reply
>>> e-mail, and then destroy all copies of this transmission****
>>>
>>
>>
>> ------------------------------
>> This e-mail transmission and any attachments accompanying it may contain
>> confidential and/or proprietary information and is intended only for the
>> person or entity to whom it was originally addressed. If you are not the
>> intended recipient, you are hereby notified that any disclosure, copying,
>> distribution or other use of this information is strictly prohibited. Any
>> unauthorized interception of this transmission is illegal. If you have
>> received this transmission in error, please notify the sender by reply
>> e-mail, and then destroy all copies of this transmission
>>
>
>
>
> --
> Regards,
>
> Rajesh M S





-----
--
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/Excel-importing-with-missing-data-tp5718176p5718197.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


Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Albert-Jan Roskam
In reply to this post by Bruce Weaver
Never mind... SET ERRORS OFF.. ;-)
Hmmm, I guess it works but it feels wrong to let errors pass silently.
 
Regards,
Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 

From: Bruce Weaver <[hidden email]>
To: [hidden email]
Sent: Friday, February 22, 2013 3:09 PM
Subject: Re: [SPSSX-L] Excel importing with missing data

The solution David posted earlier in the thread can be made a bit more
general so that it works for any input file.  E.g.,

data list list / a b c (3F) d e f (3A).
begin data
1 2 3 a b c
4 5 6 d e f


end data.

** Create two placeholder variables, make one the first variable
* in the file, the other the last variable.

NUMERIC junk1 junk2 (f1).
MATCH FILES file = * / KEEP = junk1 all.
EXECUTE.
*
PRESERVE.
SET ERRORS OFF.

** Initialize #numvars to -2, because junk1 and junk2 don't count.
COMPUTE #numvars = -2.
**------*.
COMPUTE #deadnum=0.
COMPUTE #deadstring=0.
DO REPEAT v=junk1 TO junk2.
+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
*+  COMPUTE #numvars = #numvars + 1.*
END REPEAT.
SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
RESTORE.
*DELETE VARIABLES junk1 junk2.*
LIST.




Rajeshms wrote

> Thanks to all,
>
> But am looking for this not to happen. I am creating a syntax for future
> works, so that any person can work on it with out going to details of data
> pulling and manipulating,transforming and other tricks.
>
> I doubt some thing is wrong.Anyways am doing some research on
> this..........
>
> regards,
>
> Rajesh M S
>
>
> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett &lt;

> mhewett@

> &gt; wrote:
>
>> If I delete the whole row instead of the contents, it works for me.
>>
>> *Martha Hewett*  |  *Director of Research* | *612.335.5865* Center for
>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>
>> *
>>
>>
>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>

> Henry.Ilian@.ny

>> wrote:
>>
>>> ** ** ** ** ** ** **
>>>
>>> If you delete rows in a spreadsheet, Excel still counts the same number
>>> of rows as part of the data area of the spread sheet, and SPSS will
>>> still
>>> bring in empty rows. The best solution is to copy only what you want to
>>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the
>>> current
>>> workbook—and bring the data in from the new worksheet. ****
>>>
>>> ** **
>>>
>>> Henry****
>>>
>>> ** **
>>>  ------------------------------
>>>
>>> *From:* SPSSX(r) Discussion [mailto:**

> SPSSX-L@.UGA

> **] *On
>>> Behalf Of *Martha Hewett
>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>> *To:* **

> SPSSX-L@.UGA

> **
>>> *Subject:* Re: Excel importing with missing data****
>>>
>>> ** **
>>>
>>> You can also just "delete" all the rows in your spreadsheet that no
>>> longer have data.
>>> ****
>>>
>>> *Martha Hewett*  | ****
>>>
>>> *Director of Research* | *612.335.5865*****
>>>
>>> Center for Energy and Environment****
>>>
>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN** **
>>> 55401********
>>>
>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>
>>> ** **
>>>
>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>

> Henry.Ilian@.ny

>> wrote:****
>>>
>>> Hi Rajesh****
>>>
>>>  ****
>>>
>>> I’ve had that problem many times. It comes from the fact that if cells
>>> have been used at any time in the spreadsheet, Excel sees them as active
>>> cells, even if there is nothing currently in them. ****
>>>
>>>  ****
>>>
>>> What I do is copy the data set, being careful to highlight the data and
>>> nothing else, into a fresh spreadsheet—an unused tab in the same
>>> workbook
>>> will work. I import the copied data into SPSS, and there are no phantom
>>> cases.****
>>>
>>>  ****
>>>
>>> Henry****
>>>
>>>  ****
>>>  ------------------------------
>>>
>>> *From:* SPSSX(r) Discussion [mailto:

> SPSSX-L@.UGA

> ] *On Behalf
>>> Of *Rajeshms
>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>> *To:*

> SPSSX-L@.UGA

>>> *Subject:* Excel importing with missing data****
>>>
>>>  ****
>>>
>>> Dear all,****
>>>
>>>  ****
>>>
>>> I have an  Excel data, with 832 cases and there is no other cases after
>>> case number 832 in excel.But when I import the same data into spss , I
>>> can
>>> see the full data imported with other lots of missing (dot cases) data.
>>> I
>>> don know why this is coming , how to overcome this. I don need to do
>>> manual
>>> deletion of the extra missing cases in spss.,Thanks to all.
>>> ****
>>>
>>>  ****
>>>
>>> -- ****
>>>
>>> Regards,****
>>>
>>>  ****
>>>
>>> Rajesh M S****
>>>
>>>  ****
>>>
>>>  ****
>>>
>>>  ****
>>>
>>> ** **
>>>  ------------------------------
>>>
>>> Confidentiality Notice: This e-mail communication, and any attachments,
>>> contains confidential and privileged information for the exclusive use
>>> of
>>> the recipient(s) named above. If you are not an intended recipient, or
>>> the
>>> employee or agent responsible to deliver it to an intended recipient,
>>> you
>>> are hereby notified that you have received this communication in error
>>> and
>>> that any review, disclosure, dissemination, distribution or copying of
>>> it
>>> or its contents is prohibited. If you have received this communication
>>> in
>>> error, please notify me immediately by replying to this message and
>>> delete
>>> this communication from your computer. Thank you.****
>>>
>>> ** **
>>>  ------------------------------
>>>
>>> This e-mail transmission and any attachments accompanying it may contain
>>> confidential and/or proprietary information and is intended only for the
>>> person or entity to whom it was originally addressed. If you are not the
>>> intended recipient, you are hereby notified that any disclosure,
>>> copying,
>>> distribution or other use of this information is strictly prohibited.
>>> Any
>>> unauthorized interception of this transmission is illegal. If you have
>>> received this transmission in error, please notify the sender by reply
>>> e-mail, and then destroy all copies of this transmission****
>>>
>>
>>
>> ------------------------------
>> This e-mail transmission and any attachments accompanying it may contain
>> confidential and/or proprietary information and is intended only for the
>> person or entity to whom it was originally addressed. If you are not the
>> intended recipient, you are hereby notified that any disclosure, copying,
>> distribution or other use of this information is strictly prohibited. Any
>> unauthorized interception of this transmission is illegal. If you have
>> received this transmission in error, please notify the sender by reply
>> e-mail, and then destroy all copies of this transmission
>>
>
>
>
> --
> Regards,
>
> Rajesh M S





-----
--
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/Excel-importing-with-missing-data-tp5718176p5718197.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


Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

David Marso
Administrator
In reply to this post by Albert-Jan Roskam
The DO REPEAT structure is unaware of the data type (unlike COUNT) hence the list passes quite innocuously.  However, you will indeed incur one error per mistyped variable in the IF statements.
So SET ERRORS OFF within a PRESERVE RESTORE block seemed reasonable ;-)
Bruce's addition seals the deal ;-)
--
Albert-Jan Roskam wrote
....

+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
 ....

Will this really work if stand-in variable v could represent both numerical and string variables??
I'd think that either expression will cause an error if the variable is of the wrong type.


Regards,
Albert-Jan


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 


>________________________________
> From: Bruce Weaver <[hidden email]>
>To: [hidden email] 
>Sent: Friday, February 22, 2013 3:09 PM
>Subject: Re: [SPSSX-L] Excel importing with missing data
>
>The solution David posted earlier in the thread can be made a bit more
>general so that it works for any input file.  E.g.,
>
>data list list / a b c (3F) d e f (3A).
>begin data
>1 2 3 a b c
>4 5 6 d e f
>
>
>end data.
>
>** Create two placeholder variables, make one the first variable
>* in the file, the other the last variable.
>
>NUMERIC junk1 junk2 (f1).
>MATCH FILES file = * / KEEP = junk1 all.
>EXECUTE.
>*
>PRESERVE.
>SET ERRORS OFF.
>
>** Initialize #numvars to -2, because junk1 and junk2 don't count.
>COMPUTE #numvars = -2.
>**------*.
>COMPUTE #deadnum=0.
>COMPUTE #deadstring=0.
>DO REPEAT v=junk1 TO junk2.
>+  IF MISSING(v) #deadnum=#deadnum+1.
>+  IF (v EQ " ") #deadstring=#deadstring+1.
>*+  COMPUTE #numvars = #numvars + 1.*
>END REPEAT.
>SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
>RESTORE.
>*DELETE VARIABLES junk1 junk2.*
>LIST.
>
>
>
>
>Rajeshms wrote
>> Thanks to all,
>>
>> But am looking for this not to happen. I am creating a syntax for future
>> works, so that any person can work on it with out going to details of data
>> pulling and manipulating,transforming and other tricks.
>>
>> I doubt some thing is wrong.Anyways am doing some research on
>> this..........
>>
>> regards,
>>
>> Rajesh M S
>>
>>
>> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <
>
>> mhewett@
>
>> > wrote:
>>
>>> If I delete the whole row instead of the contents, it works for me.
>>>
>>> *Martha Hewett*  |  *Director of Research* | *612.335.5865* Center for
>>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>>
>>> *
>>>
>>>
>>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:
>>>
>>>> ** ** ** ** ** ** **
>>>>
>>>> If you delete rows in a spreadsheet, Excel still counts the same number
>>>> of rows as part of the data area of the spread sheet, and SPSS will
>>>> still
>>>> bring in empty rows. The best solution is to copy only what you want to
>>>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the
>>>> current
>>>> workbook—and bring the data in from the new worksheet. ****
>>>>
>>>> ** **
>>>>
>>>> Henry****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:**
>
>> SPSSX-L@.UGA
>
>> **] *On
>>>> Behalf Of *Martha Hewett
>>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>>> *To:* **
>
>> SPSSX-L@.UGA
>
>> **
>>>> *Subject:* Re: Excel importing with missing data****
>>>>
>>>> ** **
>>>>
>>>> You can also just "delete" all the rows in your spreadsheet that no
>>>> longer have data.
>>>> ****
>>>>
>>>> *Martha Hewett*  | ****
>>>>
>>>> *Director of Research* | *612.335.5865*****
>>>>
>>>> Center for Energy and Environment****
>>>>
>>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN** **
>>>> 55401********
>>>>
>>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>>
>>>> ** **
>>>>
>>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:****
>>>>
>>>> Hi Rajesh****
>>>>
>>>>  ****
>>>>
>>>> I’ve had that problem many times. It comes from the fact that if cells
>>>> have been used at any time in the spreadsheet, Excel sees them as active
>>>> cells, even if there is nothing currently in them. ****
>>>>
>>>>  ****
>>>>
>>>> What I do is copy the data set, being careful to highlight the data and
>>>> nothing else, into a fresh spreadsheet—an unused tab in the same
>>>> workbook
>>>> will work. I import the copied data into SPSS, and there are no phantom
>>>> cases.****
>>>>
>>>>  ****
>>>>
>>>> Henry****
>>>>
>>>>  ****
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:
>
>> SPSSX-L@.UGA
>
>> ] *On Behalf
>>>> Of *Rajeshms
>>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>>> *To:*
>
>> SPSSX-L@.UGA
>
>>>> *Subject:* Excel importing with missing data****
>>>>
>>>>  ****
>>>>
>>>> Dear all,****
>>>>
>>>>  ****
>>>>
>>>> I have an  Excel data, with 832 cases and there is no other cases after
>>>> case number 832 in excel.But when I import the same data into spss , I
>>>> can
>>>> see the full data imported with other lots of missing (dot cases) data.
>>>> I
>>>> don know why this is coming , how to overcome this. I don need to do
>>>> manual
>>>> deletion of the extra missing cases in spss.,Thanks to all.
>>>> ****
>>>>
>>>>  ****
>>>>
>>>> -- ****
>>>>
>>>> Regards,****
>>>>
>>>>  ****
>>>>
>>>> Rajesh M S****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> Confidentiality Notice: This e-mail communication, and any attachments,
>>>> contains confidential and privileged information for the exclusive use
>>>> of
>>>> the recipient(s) named above. If you are not an intended recipient, or
>>>> the
>>>> employee or agent responsible to deliver it to an intended recipient,
>>>> you
>>>> are hereby notified that you have received this communication in error
>>>> and
>>>> that any review, disclosure, dissemination, distribution or copying of
>>>> it
>>>> or its contents is prohibited. If you have received this communication
>>>> in
>>>> error, please notify me immediately by replying to this message and
>>>> delete
>>>> this communication from your computer. Thank you.****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> This e-mail transmission and any attachments accompanying it may contain
>>>> confidential and/or proprietary information and is intended only for the
>>>> person or entity to whom it was originally addressed. If you are not the
>>>> intended recipient, you are hereby notified that any disclosure,
>>>> copying,
>>>> distribution or other use of this information is strictly prohibited.
>>>> Any
>>>> unauthorized interception of this transmission is illegal. If you have
>>>> received this transmission in error, please notify the sender by reply
>>>> e-mail, and then destroy all copies of this transmission****
>>>>
>>>
>>>
>>> ------------------------------
>>> This e-mail transmission and any attachments accompanying it may contain
>>> confidential and/or proprietary information and is intended only for the
>>> person or entity to whom it was originally addressed. If you are not the
>>> intended recipient, you are hereby notified that any disclosure, copying,
>>> distribution or other use of this information is strictly prohibited. Any
>>> unauthorized interception of this transmission is illegal. If you have
>>> received this transmission in error, please notify the sender by reply
>>> e-mail, and then destroy all copies of this transmission
>>>
>>
>>
>>
>> --
>> Regards,
>>
>> Rajesh M S
>
>
>
>
>
>-----
>--
>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/Excel-importing-with-missing-data-tp5718176p5718197.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
>
>
>
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

David Marso
Administrator
In reply to this post by Albert-Jan Roskam
I feel dirty ;-)
--
Albert-Jan Roskam wrote
Never mind... SET ERRORS OFF.. ;-)
Hmmm, I guess it works but it feels wrong to let errors pass silently.
 

Regards,
Albert-Jan


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 


>________________________________
> From: Bruce Weaver <[hidden email]>
>To: [hidden email] 
>Sent: Friday, February 22, 2013 3:09 PM
>Subject: Re: [SPSSX-L] Excel importing with missing data
>
>The solution David posted earlier in the thread can be made a bit more
>general so that it works for any input file.  E.g.,
>
>data list list / a b c (3F) d e f (3A).
>begin data
>1 2 3 a b c
>4 5 6 d e f
>
>
>end data.
>
>** Create two placeholder variables, make one the first variable
>* in the file, the other the last variable.
>
>NUMERIC junk1 junk2 (f1).
>MATCH FILES file = * / KEEP = junk1 all.
>EXECUTE.
>*
>PRESERVE.
>SET ERRORS OFF.
>
>** Initialize #numvars to -2, because junk1 and junk2 don't count.
>COMPUTE #numvars = -2.
>**------*.
>COMPUTE #deadnum=0.
>COMPUTE #deadstring=0.
>DO REPEAT v=junk1 TO junk2.
>+  IF MISSING(v) #deadnum=#deadnum+1.
>+  IF (v EQ " ") #deadstring=#deadstring+1.
>*+  COMPUTE #numvars = #numvars + 1.*
>END REPEAT.
>SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
>RESTORE.
>*DELETE VARIABLES junk1 junk2.*
>LIST.
>
>
>
>
>Rajeshms wrote
>> Thanks to all,
>>
>> But am looking for this not to happen. I am creating a syntax for future
>> works, so that any person can work on it with out going to details of data
>> pulling and manipulating,transforming and other tricks.
>>
>> I doubt some thing is wrong.Anyways am doing some research on
>> this..........
>>
>> regards,
>>
>> Rajesh M S
>>
>>
>> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <
>
>> mhewett@
>
>> > wrote:
>>
>>> If I delete the whole row instead of the contents, it works for me.
>>>
>>> *Martha Hewett*  |  *Director of Research* | *612.335.5865* Center for
>>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>>
>>> *
>>>
>>>
>>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:
>>>
>>>> ** ** ** ** ** ** **
>>>>
>>>> If you delete rows in a spreadsheet, Excel still counts the same number
>>>> of rows as part of the data area of the spread sheet, and SPSS will
>>>> still
>>>> bring in empty rows. The best solution is to copy only what you want to
>>>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the
>>>> current
>>>> workbook—and bring the data in from the new worksheet. ****
>>>>
>>>> ** **
>>>>
>>>> Henry****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:**
>
>> SPSSX-L@.UGA
>
>> **] *On
>>>> Behalf Of *Martha Hewett
>>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>>> *To:* **
>
>> SPSSX-L@.UGA
>
>> **
>>>> *Subject:* Re: Excel importing with missing data****
>>>>
>>>> ** **
>>>>
>>>> You can also just "delete" all the rows in your spreadsheet that no
>>>> longer have data.
>>>> ****
>>>>
>>>> *Martha Hewett*  | ****
>>>>
>>>> *Director of Research* | *612.335.5865*****
>>>>
>>>> Center for Energy and Environment****
>>>>
>>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN** **
>>>> 55401********
>>>>
>>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>>
>>>> ** **
>>>>
>>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:****
>>>>
>>>> Hi Rajesh****
>>>>
>>>>  ****
>>>>
>>>> I’ve had that problem many times. It comes from the fact that if cells
>>>> have been used at any time in the spreadsheet, Excel sees them as active
>>>> cells, even if there is nothing currently in them. ****
>>>>
>>>>  ****
>>>>
>>>> What I do is copy the data set, being careful to highlight the data and
>>>> nothing else, into a fresh spreadsheet—an unused tab in the same
>>>> workbook
>>>> will work. I import the copied data into SPSS, and there are no phantom
>>>> cases.****
>>>>
>>>>  ****
>>>>
>>>> Henry****
>>>>
>>>>  ****
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:
>
>> SPSSX-L@.UGA
>
>> ] *On Behalf
>>>> Of *Rajeshms
>>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>>> *To:*
>
>> SPSSX-L@.UGA
>
>>>> *Subject:* Excel importing with missing data****
>>>>
>>>>  ****
>>>>
>>>> Dear all,****
>>>>
>>>>  ****
>>>>
>>>> I have an  Excel data, with 832 cases and there is no other cases after
>>>> case number 832 in excel.But when I import the same data into spss , I
>>>> can
>>>> see the full data imported with other lots of missing (dot cases) data.
>>>> I
>>>> don know why this is coming , how to overcome this. I don need to do
>>>> manual
>>>> deletion of the extra missing cases in spss.,Thanks to all.
>>>> ****
>>>>
>>>>  ****
>>>>
>>>> -- ****
>>>>
>>>> Regards,****
>>>>
>>>>  ****
>>>>
>>>> Rajesh M S****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> Confidentiality Notice: This e-mail communication, and any attachments,
>>>> contains confidential and privileged information for the exclusive use
>>>> of
>>>> the recipient(s) named above. If you are not an intended recipient, or
>>>> the
>>>> employee or agent responsible to deliver it to an intended recipient,
>>>> you
>>>> are hereby notified that you have received this communication in error
>>>> and
>>>> that any review, disclosure, dissemination, distribution or copying of
>>>> it
>>>> or its contents is prohibited. If you have received this communication
>>>> in
>>>> error, please notify me immediately by replying to this message and
>>>> delete
>>>> this communication from your computer. Thank you.****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> This e-mail transmission and any attachments accompanying it may contain
>>>> confidential and/or proprietary information and is intended only for the
>>>> person or entity to whom it was originally addressed. If you are not the
>>>> intended recipient, you are hereby notified that any disclosure,
>>>> copying,
>>>> distribution or other use of this information is strictly prohibited.
>>>> Any
>>>> unauthorized interception of this transmission is illegal. If you have
>>>> received this transmission in error, please notify the sender by reply
>>>> e-mail, and then destroy all copies of this transmission****
>>>>
>>>
>>>
>>> ------------------------------
>>> This e-mail transmission and any attachments accompanying it may contain
>>> confidential and/or proprietary information and is intended only for the
>>> person or entity to whom it was originally addressed. If you are not the
>>> intended recipient, you are hereby notified that any disclosure, copying,
>>> distribution or other use of this information is strictly prohibited. Any
>>> unauthorized interception of this transmission is illegal. If you have
>>> received this transmission in error, please notify the sender by reply
>>> e-mail, and then destroy all copies of this transmission
>>>
>>
>>
>>
>> --
>> Regards,
>>
>> Rajesh M S
>
>
>
>
>
>-----
>--
>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/Excel-importing-with-missing-data-tp5718176p5718197.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
>
>
>
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Bruce Weaver
Administrator
In reply to this post by David Marso
For the problem the OP described, perhaps it should be SYSMIS(v) rather than MISSING(v).  i.e.,

+  IF SYSMIS(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.



David Marso wrote
The DO REPEAT structure is unaware of the data type (unlike COUNT) hence the list passes quite innocuously.  However, you will indeed incur one error per mistyped variable in the IF statements.
So SET ERRORS OFF within a PRESERVE RESTORE block seemed reasonable ;-)
Bruce's addition seals the deal ;-)
--
Albert-Jan Roskam wrote
....

+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
 ....

Will this really work if stand-in variable v could represent both numerical and string variables??
I'd think that either expression will cause an error if the variable is of the wrong type.


Regards,
Albert-Jan


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 


>________________________________
> From: Bruce Weaver <[hidden email]>
>To: [hidden email] 
>Sent: Friday, February 22, 2013 3:09 PM
>Subject: Re: [SPSSX-L] Excel importing with missing data
>
>The solution David posted earlier in the thread can be made a bit more
>general so that it works for any input file.  E.g.,
>
>data list list / a b c (3F) d e f (3A).
>begin data
>1 2 3 a b c
>4 5 6 d e f
>
>
>end data.
>
>** Create two placeholder variables, make one the first variable
>* in the file, the other the last variable.
>
>NUMERIC junk1 junk2 (f1).
>MATCH FILES file = * / KEEP = junk1 all.
>EXECUTE.
>*
>PRESERVE.
>SET ERRORS OFF.
>
>** Initialize #numvars to -2, because junk1 and junk2 don't count.
>COMPUTE #numvars = -2.
>**------*.
>COMPUTE #deadnum=0.
>COMPUTE #deadstring=0.
>DO REPEAT v=junk1 TO junk2.
>+  IF MISSING(v) #deadnum=#deadnum+1.
>+  IF (v EQ " ") #deadstring=#deadstring+1.
>*+  COMPUTE #numvars = #numvars + 1.*
>END REPEAT.
>SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
>RESTORE.
>*DELETE VARIABLES junk1 junk2.*
>LIST.
>
>
>
>
>Rajeshms wrote
>> Thanks to all,
>>
>> But am looking for this not to happen. I am creating a syntax for future
>> works, so that any person can work on it with out going to details of data
>> pulling and manipulating,transforming and other tricks.
>>
>> I doubt some thing is wrong.Anyways am doing some research on
>> this..........
>>
>> regards,
>>
>> Rajesh M S
>>
>>
>> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <
>
>> mhewett@
>
>> > wrote:
>>
>>> If I delete the whole row instead of the contents, it works for me.
>>>
>>> *Martha Hewett*  |  *Director of Research* | *612.335.5865* Center for
>>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>>
>>> *
>>>
>>>
>>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:
>>>
>>>> ** ** ** ** ** ** **
>>>>
>>>> If you delete rows in a spreadsheet, Excel still counts the same number
>>>> of rows as part of the data area of the spread sheet, and SPSS will
>>>> still
>>>> bring in empty rows. The best solution is to copy only what you want to
>>>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the
>>>> current
>>>> workbook—and bring the data in from the new worksheet. ****
>>>>
>>>> ** **
>>>>
>>>> Henry****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:**
>
>> SPSSX-L@.UGA
>
>> **] *On
>>>> Behalf Of *Martha Hewett
>>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>>> *To:* **
>
>> SPSSX-L@.UGA
>
>> **
>>>> *Subject:* Re: Excel importing with missing data****
>>>>
>>>> ** **
>>>>
>>>> You can also just "delete" all the rows in your spreadsheet that no
>>>> longer have data.
>>>> ****
>>>>
>>>> *Martha Hewett*  | ****
>>>>
>>>> *Director of Research* | *612.335.5865*****
>>>>
>>>> Center for Energy and Environment****
>>>>
>>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN** **
>>>> 55401********
>>>>
>>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>>
>>>> ** **
>>>>
>>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:****
>>>>
>>>> Hi Rajesh****
>>>>
>>>>  ****
>>>>
>>>> I’ve had that problem many times. It comes from the fact that if cells
>>>> have been used at any time in the spreadsheet, Excel sees them as active
>>>> cells, even if there is nothing currently in them. ****
>>>>
>>>>  ****
>>>>
>>>> What I do is copy the data set, being careful to highlight the data and
>>>> nothing else, into a fresh spreadsheet—an unused tab in the same
>>>> workbook
>>>> will work. I import the copied data into SPSS, and there are no phantom
>>>> cases.****
>>>>
>>>>  ****
>>>>
>>>> Henry****
>>>>
>>>>  ****
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:
>
>> SPSSX-L@.UGA
>
>> ] *On Behalf
>>>> Of *Rajeshms
>>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>>> *To:*
>
>> SPSSX-L@.UGA
>
>>>> *Subject:* Excel importing with missing data****
>>>>
>>>>  ****
>>>>
>>>> Dear all,****
>>>>
>>>>  ****
>>>>
>>>> I have an  Excel data, with 832 cases and there is no other cases after
>>>> case number 832 in excel.But when I import the same data into spss , I
>>>> can
>>>> see the full data imported with other lots of missing (dot cases) data.
>>>> I
>>>> don know why this is coming , how to overcome this. I don need to do
>>>> manual
>>>> deletion of the extra missing cases in spss.,Thanks to all.
>>>> ****
>>>>
>>>>  ****
>>>>
>>>> -- ****
>>>>
>>>> Regards,****
>>>>
>>>>  ****
>>>>
>>>> Rajesh M S****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> Confidentiality Notice: This e-mail communication, and any attachments,
>>>> contains confidential and privileged information for the exclusive use
>>>> of
>>>> the recipient(s) named above. If you are not an intended recipient, or
>>>> the
>>>> employee or agent responsible to deliver it to an intended recipient,
>>>> you
>>>> are hereby notified that you have received this communication in error
>>>> and
>>>> that any review, disclosure, dissemination, distribution or copying of
>>>> it
>>>> or its contents is prohibited. If you have received this communication
>>>> in
>>>> error, please notify me immediately by replying to this message and
>>>> delete
>>>> this communication from your computer. Thank you.****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> This e-mail transmission and any attachments accompanying it may contain
>>>> confidential and/or proprietary information and is intended only for the
>>>> person or entity to whom it was originally addressed. If you are not the
>>>> intended recipient, you are hereby notified that any disclosure,
>>>> copying,
>>>> distribution or other use of this information is strictly prohibited.
>>>> Any
>>>> unauthorized interception of this transmission is illegal. If you have
>>>> received this transmission in error, please notify the sender by reply
>>>> e-mail, and then destroy all copies of this transmission****
>>>>
>>>
>>>
>>> ------------------------------
>>> This e-mail transmission and any attachments accompanying it may contain
>>> confidential and/or proprietary information and is intended only for the
>>> person or entity to whom it was originally addressed. If you are not the
>>> intended recipient, you are hereby notified that any disclosure, copying,
>>> distribution or other use of this information is strictly prohibited. Any
>>> unauthorized interception of this transmission is illegal. If you have
>>> received this transmission in error, please notify the sender by reply
>>> e-mail, and then destroy all copies of this transmission
>>>
>>
>>
>>
>> --
>> Regards,
>>
>> Rajesh M S
>
>
>
>
>
>-----
>--
>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/Excel-importing-with-missing-data-tp5718176p5718197.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
>
>
>
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

Bruce Weaver
Administrator
In reply to this post by David Marso
In hindsight, and after testing a few more variations on the input data, I didn't have it quite right.  E.g., when I created an input line with valid data for only the first numeric variable, that case was missing from the final data file.  This version seems to work properly.  I changed the variable names so that it's not so easy to confuse data lines with variable names in the listing at the end.  Other changes are in bold.

data list list / n1 to n3 (3F) s1 to s3 (3A).
begin data
1 2 3 a b c

4 5 . d e f
. 7 8 " " h ""

9 . . " " " " " "

end data.

* Note that there are 7 cases in the original file, 3 rows
* with all numeric variables missing and all strings blank.

* Create two placeholder variables, make one the first variable
* in the file, the other the last variable.
* This code assumes you don't have variables named junk1 and junk2.
* If you do, rename variables junk1 and junk2 below.

NUMERIC junk1 junk2 (f1).
MATCH FILES file = * / KEEP = junk1 all.
EXECUTE.

PRESERVE.
SET ERRORS OFF.

COMPUTE #numvars = 0. /* I set this to -2 previously.
*------*.
COMPUTE #deadnum=0.
COMPUTE #deadstring=0.
DO REPEAT v=junk1 TO junk2.
+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (RTRIM(LTRIM(v)) EQ "") #deadstring=#deadstring+1.
+  COMPUTE #numvars = #numvars + 1.
END REPEAT.
* Note that junk1 and junk2 are still in the file.
* Therefore, they are included in the #numvars count.
SELECT IF SUM(#deadnum,#deadstring) LT (#numvars).
EXECUTE.
RESTORE.
DELETE VARIABLES junk1 junk2.

* Omit the following LIST command if working with your own dataset,
* especially if it is LARGE! .

LIST.

OUTPUT:
n1 n2 n3 s1 s2 s3
 
 1  2  3 a  b  c
 4  5  . d  e  f
 .  7  8    h
 9  .  .
 
Number of cases read:  4    Number of cases listed:  4


David Marso wrote
The DO REPEAT structure is unaware of the data type (unlike COUNT) hence the list passes quite innocuously.  However, you will indeed incur one error per mistyped variable in the IF statements.
So SET ERRORS OFF within a PRESERVE RESTORE block seemed reasonable ;-)
Bruce's addition seals the deal ;-)
--
Albert-Jan Roskam wrote
....

+  IF MISSING(v) #deadnum=#deadnum+1.
+  IF (v EQ " ") #deadstring=#deadstring+1.
 ....

Will this really work if stand-in variable v could represent both numerical and string variables??
I'd think that either expression will cause an error if the variable is of the wrong type.


Regards,
Albert-Jan


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a
fresh water system, and public health, what have the Romans ever done for us?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 


>________________________________
> From: Bruce Weaver <[hidden email]>
>To: [hidden email] 
>Sent: Friday, February 22, 2013 3:09 PM
>Subject: Re: [SPSSX-L] Excel importing with missing data
>
>The solution David posted earlier in the thread can be made a bit more
>general so that it works for any input file.  E.g.,
>
>data list list / a b c (3F) d e f (3A).
>begin data
>1 2 3 a b c
>4 5 6 d e f
>
>
>end data.
>
>** Create two placeholder variables, make one the first variable
>* in the file, the other the last variable.
>
>NUMERIC junk1 junk2 (f1).
>MATCH FILES file = * / KEEP = junk1 all.
>EXECUTE.
>*
>PRESERVE.
>SET ERRORS OFF.
>
>** Initialize #numvars to -2, because junk1 and junk2 don't count.
>COMPUTE #numvars = -2.
>**------*.
>COMPUTE #deadnum=0.
>COMPUTE #deadstring=0.
>DO REPEAT v=junk1 TO junk2.
>+  IF MISSING(v) #deadnum=#deadnum+1.
>+  IF (v EQ " ") #deadstring=#deadstring+1.
>*+  COMPUTE #numvars = #numvars + 1.*
>END REPEAT.
>SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
>RESTORE.
>*DELETE VARIABLES junk1 junk2.*
>LIST.
>
>
>
>
>Rajeshms wrote
>> Thanks to all,
>>
>> But am looking for this not to happen. I am creating a syntax for future
>> works, so that any person can work on it with out going to details of data
>> pulling and manipulating,transforming and other tricks.
>>
>> I doubt some thing is wrong.Anyways am doing some research on
>> this..........
>>
>> regards,
>>
>> Rajesh M S
>>
>>
>> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <
>
>> mhewett@
>
>> > wrote:
>>
>>> If I delete the whole row instead of the contents, it works for me.
>>>
>>> *Martha Hewett*  |  *Director of Research* | *612.335.5865* Center for
>>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>>
>>> *
>>>
>>>
>>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:
>>>
>>>> ** ** ** ** ** ** **
>>>>
>>>> If you delete rows in a spreadsheet, Excel still counts the same number
>>>> of rows as part of the data area of the spread sheet, and SPSS will
>>>> still
>>>> bring in empty rows. The best solution is to copy only what you want to
>>>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the
>>>> current
>>>> workbook—and bring the data in from the new worksheet. ****
>>>>
>>>> ** **
>>>>
>>>> Henry****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:**
>
>> SPSSX-L@.UGA
>
>> **] *On
>>>> Behalf Of *Martha Hewett
>>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>>> *To:* **
>
>> SPSSX-L@.UGA
>
>> **
>>>> *Subject:* Re: Excel importing with missing data****
>>>>
>>>> ** **
>>>>
>>>> You can also just "delete" all the rows in your spreadsheet that no
>>>> longer have data.
>>>> ****
>>>>
>>>> *Martha Hewett*  | ****
>>>>
>>>> *Director of Research* | *612.335.5865*****
>>>>
>>>> Center for Energy and Environment****
>>>>
>>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN** **
>>>> 55401********
>>>>
>>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>>
>>>> ** **
>>>>
>>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>>
>
>> Henry.Ilian@.ny
>
>>> wrote:****
>>>>
>>>> Hi Rajesh****
>>>>
>>>>  ****
>>>>
>>>> I’ve had that problem many times. It comes from the fact that if cells
>>>> have been used at any time in the spreadsheet, Excel sees them as active
>>>> cells, even if there is nothing currently in them. ****
>>>>
>>>>  ****
>>>>
>>>> What I do is copy the data set, being careful to highlight the data and
>>>> nothing else, into a fresh spreadsheet—an unused tab in the same
>>>> workbook
>>>> will work. I import the copied data into SPSS, and there are no phantom
>>>> cases.****
>>>>
>>>>  ****
>>>>
>>>> Henry****
>>>>
>>>>  ****
>>>>  ------------------------------
>>>>
>>>> *From:* SPSSX(r) Discussion [mailto:
>
>> SPSSX-L@.UGA
>
>> ] *On Behalf
>>>> Of *Rajeshms
>>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>>> *To:*
>
>> SPSSX-L@.UGA
>
>>>> *Subject:* Excel importing with missing data****
>>>>
>>>>  ****
>>>>
>>>> Dear all,****
>>>>
>>>>  ****
>>>>
>>>> I have an  Excel data, with 832 cases and there is no other cases after
>>>> case number 832 in excel.But when I import the same data into spss , I
>>>> can
>>>> see the full data imported with other lots of missing (dot cases) data.
>>>> I
>>>> don know why this is coming , how to overcome this. I don need to do
>>>> manual
>>>> deletion of the extra missing cases in spss.,Thanks to all.
>>>> ****
>>>>
>>>>  ****
>>>>
>>>> -- ****
>>>>
>>>> Regards,****
>>>>
>>>>  ****
>>>>
>>>> Rajesh M S****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>>  ****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> Confidentiality Notice: This e-mail communication, and any attachments,
>>>> contains confidential and privileged information for the exclusive use
>>>> of
>>>> the recipient(s) named above. If you are not an intended recipient, or
>>>> the
>>>> employee or agent responsible to deliver it to an intended recipient,
>>>> you
>>>> are hereby notified that you have received this communication in error
>>>> and
>>>> that any review, disclosure, dissemination, distribution or copying of
>>>> it
>>>> or its contents is prohibited. If you have received this communication
>>>> in
>>>> error, please notify me immediately by replying to this message and
>>>> delete
>>>> this communication from your computer. Thank you.****
>>>>
>>>> ** **
>>>>  ------------------------------
>>>>
>>>> This e-mail transmission and any attachments accompanying it may contain
>>>> confidential and/or proprietary information and is intended only for the
>>>> person or entity to whom it was originally addressed. If you are not the
>>>> intended recipient, you are hereby notified that any disclosure,
>>>> copying,
>>>> distribution or other use of this information is strictly prohibited.
>>>> Any
>>>> unauthorized interception of this transmission is illegal. If you have
>>>> received this transmission in error, please notify the sender by reply
>>>> e-mail, and then destroy all copies of this transmission****
>>>>
>>>
>>>
>>> ------------------------------
>>> This e-mail transmission and any attachments accompanying it may contain
>>> confidential and/or proprietary information and is intended only for the
>>> person or entity to whom it was originally addressed. If you are not the
>>> intended recipient, you are hereby notified that any disclosure, copying,
>>> distribution or other use of this information is strictly prohibited. Any
>>> unauthorized interception of this transmission is illegal. If you have
>>> received this transmission in error, please notify the sender by reply
>>> e-mail, and then destroy all copies of this transmission
>>>
>>
>>
>>
>> --
>> Regards,
>>
>> Rajesh M S
>
>
>
>
>
>-----
>--
>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/Excel-importing-with-missing-data-tp5718176p5718197.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
>
>
>
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

henryilian
In reply to this post by David Marso
There's nothing wrong with making a simple task complicated so long as you recognize that that's what you're doing. I've encountered the empty row problem often. The simple solution, which works, is to copy only what you want to import into a fresh spreadsheet and import it. It works without problems. If there are numbers that Excel is storing as alphanumeric characters, then saving the spreadsheet as a csv file will take care of that. No doubt there are any number of Rube Goldberg devices that would also work, but why spend time that you could use analyzing data?

Henry


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso
Sent: Friday, February 22, 2013 12:30 PM
To: [hidden email]
Subject: Re: Excel importing with missing data

I feel dirty ;-)
--

Albert-Jan Roskam wrote

> Never mind... SET ERRORS OFF.. ;-)
> Hmmm, I guess it works but it feels wrong to let errors pass silently.
> �
>
> Regards,
> Albert-Jan
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> All right, but apart from the sanitation, the medicine, education, wine,
> public order, irrigation, roads, a
> fresh water system, and public health, what have the Romans ever done for
> us?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~�
>
>
>>________________________________
>> From: Bruce Weaver &lt;

> bruce.weaver@

> &gt;
>>To:

> SPSSX-L@.UGA

>
>>Sent: Friday, February 22, 2013 3:09 PM
>>Subject: Re: [SPSSX-L] Excel importing with missing data
>>
>>The solution David posted earlier in the thread can be made a bit more
>>general so that it works for any input file.�  E.g.,
>>
>>data list list / a b c (3F) d e f (3A).
>>begin data
>>1 2 3 a b c
>>4 5 6 d e f
>>
>>
>>end data.
>>
>>** Create two placeholder variables, make one the first variable
>>* in the file, the other the last variable.
>>
>>NUMERIC junk1 junk2 (f1).
>>MATCH FILES file = * / KEEP = junk1 all.
>>EXECUTE.
>>*
>>PRESERVE.
>>SET ERRORS OFF.
>>
>>** Initialize #numvars to -2, because junk1 and junk2 don't count.
>>COMPUTE #numvars = -2.
>>**------*.
>>COMPUTE #deadnum=0.
>>COMPUTE #deadstring=0.
>>DO REPEAT v=junk1 TO junk2.
>>+�  IF MISSING(v) #deadnum=#deadnum+1.
>>+�  IF (v EQ " ") #deadstring=#deadstring+1.
>>*+�  COMPUTE #numvars = #numvars + 1.*
>>END REPEAT.
>>SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
>>RESTORE.
>>*DELETE VARIABLES junk1 junk2.*
>>LIST.
>>
>>
>>
>>
>>Rajeshms wrote
>>> Thanks to all,
>>>
>>> But am looking for this not to happen. I am creating a syntax for future
>>> works, so that any person can work on it with out going to details of
>>> data
>>> pulling and manipulating,transforming and other tricks.
>>>
>>> I doubt some thing is wrong.Anyways am doing some research on
>>> this..........
>>>
>>> regards,
>>>
>>> Rajesh M S
>>>
>>>
>>> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett &lt;
>>
>>> mhewett@
>>
>>> &gt; wrote:
>>>
>>>> If I delete the whole row instead of the contents, it works for me.
>>>>
>>>> *Martha Hewett*�  |�  *Director of Research* | *612.335.5865* Center for
>>>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>>>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>>>
>>>> *
>>>>
>>>>
>>>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>>>
>>
>>> Henry.Ilian@.ny
>>
>>>> wrote:
>>>>
>>>>> ** ** ** ** ** ** **
>>>>>
>>>>> If you delete rows in a spreadsheet, Excel still counts the same
>>>>> number
>>>>> of rows as part of the data area of the spread sheet, and SPSS will
>>>>> still
>>>>> bring in empty rows. The best solution is to copy only what you want
>>>>> to
>>>>> bring into SPSS into a fresh spreadsheet—it can be a new tab in the
>>>>> current
>>>>> workbook—and bring the data in from the new worksheet. ****
>>>>>
>>>>> ** **
>>>>>
>>>>> Henry****
>>>>>
>>>>> ** **
>>>>>�  ------------------------------
>>>>>
>>>>> *From:* SPSSX(r) Discussion [mailto:**
>>
>>> SPSSX-L@.UGA
>>
>>> **] *On
>>>>> Behalf Of *Martha Hewett
>>>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>>>> *To:* **
>>
>>> SPSSX-L@.UGA
>>
>>> **
>>>>> *Subject:* Re: Excel importing with missing data****
>>>>>
>>>>> ** **
>>>>>
>>>>> You can also just "delete" all the rows in your spreadsheet that no
>>>>> longer have data.
>>>>> ****
>>>>>
>>>>> *Martha Hewett*�  | ****
>>>>>
>>>>> *Director of Research* | *612.335.5865*****
>>>>>
>>>>> Center for Energy and Environment****
>>>>>
>>>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN**
>>>>> **
>>>>> 55401********
>>>>>
>>>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>>>
>>>>> ** **
>>>>>
>>>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>>>
>>
>>> Henry.Ilian@.ny
>>
>>>> wrote:****
>>>>>
>>>>> Hi Rajesh****
>>>>>
>>>>>�  ****
>>>>>
>>>>> I’ve had that problem many times. It comes from the fact that if cells
>>>>> have been used at any time in the spreadsheet, Excel sees them as
>>>>> active
>>>>> cells, even if there is nothing currently in them. ****
>>>>>
>>>>>�  ****
>>>>>
>>>>> What I do is copy the data set, being careful to highlight the data
>>>>> and
>>>>> nothing else, into a fresh spreadsheet—an unused tab in the same
>>>>> workbook
>>>>> will work. I import the copied data into SPSS, and there are no
>>>>> phantom
>>>>> cases.****
>>>>>
>>>>>�  ****
>>>>>
>>>>> Henry****
>>>>>
>>>>>�  ****
>>>>>�  ------------------------------
>>>>>
>>>>> *From:* SPSSX(r) Discussion [mailto:
>>
>>> SPSSX-L@.UGA
>>
>>> ] *On Behalf
>>>>> Of *Rajeshms
>>>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>>>> *To:*
>>
>>> SPSSX-L@.UGA
>>
>>>>> *Subject:* Excel importing with missing data****
>>>>>
>>>>>�  ****
>>>>>
>>>>> Dear all,****
>>>>>
>>>>>�  ****
>>>>>
>>>>> I have an�  Excel data, with 832 cases and there is no other cases
>>>>> after
>>>>> case number 832 in excel.But when I import the same data into spss , I
>>>>> can
>>>>> see the full data imported with other lots of missing (dot cases)
>>>>> data.
>>>>> I
>>>>> don know why this is coming , how to overcome this. I don need to do
>>>>> manual
>>>>> deletion of the extra missing cases in spss.,Thanks to all.
>>>>> ****
>>>>>
>>>>>�  ****
>>>>>
>>>>> -- ****
>>>>>
>>>>> Regards,****
>>>>>
>>>>>�  ****
>>>>>
>>>>> Rajesh M S****
>>>>>
>>>>>�  ****
>>>>>
>>>>>�  ****
>>>>>
>>>>>�  ****
>>>>>
>>>>> ** **
>>>>>�  ------------------------------
>>>>>
>>>>> Confidentiality Notice: This e-mail communication, and any
>>>>> attachments,
>>>>> contains confidential and privileged information for the exclusive use
>>>>> of
>>>>> the recipient(s) named above. If you are not an intended recipient, or
>>>>> the
>>>>> employee or agent responsible to deliver it to an intended recipient,
>>>>> you
>>>>> are hereby notified that you have received this communication in error
>>>>> and
>>>>> that any review, disclosure, dissemination, distribution or copying of
>>>>> it
>>>>> or its contents is prohibited. If you have received this communication
>>>>> in
>>>>> error, please notify me immediately by replying to this message and
>>>>> delete
>>>>> this communication from your computer. Thank you.****
>>>>>
>>>>> ** **
>>>>>�  ------------------------------
>>>>>
>>>>> This e-mail transmission and any attachments accompanying it may
>>>>> contain
>>>>> confidential and/or proprietary information and is intended only for
>>>>> the
>>>>> person or entity to whom it was originally addressed. If you are not
>>>>> the
>>>>> intended recipient, you are hereby notified that any disclosure,
>>>>> copying,
>>>>> distribution or other use of this information is strictly prohibited.
>>>>> Any
>>>>> unauthorized interception of this transmission is illegal. If you have
>>>>> received this transmission in error, please notify the sender by reply
>>>>> e-mail, and then destroy all copies of this transmission****
>>>>>
>>>>
>>>>
>>>> ------------------------------
>>>> This e-mail transmission and any attachments accompanying it may
>>>> contain
>>>> confidential and/or proprietary information and is intended only for
>>>> the
>>>> person or entity to whom it was originally addressed. If you are not
>>>> the
>>>> intended recipient, you are hereby notified that any disclosure,
>>>> copying,
>>>> distribution or other use of this information is strictly prohibited.
>>>> Any
>>>> unauthorized interception of this transmission is illegal. If you have
>>>> received this transmission in error, please notify the sender by reply
>>>> e-mail, and then destroy all copies of this transmission
>>>>
>>>
>>>
>>>
>>> --
>>> Regards,
>>>
>>> Rajesh M S
>>
>>
>>
>>
>>
>>-----
>>--
>>Bruce Weaver
>>

> bweaver@

>>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/Excel-importing-with-missing-data-tp5718176p5718197.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
>>
>>
>>





-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Excel-importing-with-missing-data-tp5718176p5718206.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


Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

King Douglas
I don't disagree with your statement, " There's nothing wrong with making a simple task complicated so long as you recognize that that's what you're doing" but it is not the case that David, Bruce and others are doing that.  Instead, they are creating a new and elegant thing, a programmatic SPSS solution to a general problem, one which takes very little time in practice.  In addition, it is repeatable and can easily be checked for errors.

Come on, man!  Have you no poetry in your soul?

King Douglas
Independent consultant in survey research and data analysis

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Ilian, Henry (ACS)
Sent: Friday, February 22, 2013 3:34 PM
To: [hidden email]
Subject: Re: Excel importing with missing data

There's nothing wrong with making a simple task complicated so long as you recognize that that's what you're doing. I've encountered the empty row problem often. The simple solution, which works, is to copy only what you want to import into a fresh spreadsheet and import it. It works without problems. If there are numbers that Excel is storing as alphanumeric characters, then saving the spreadsheet as a csv file will take care of that. No doubt there are any number of Rube Goldberg devices that would also work, but why spend time that you could use analyzing data?

Henry


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso
Sent: Friday, February 22, 2013 12:30 PM
To: [hidden email]
Subject: Re: Excel importing with missing data

I feel dirty ;-)
--

Albert-Jan Roskam wrote

> Never mind... SET ERRORS OFF.. ;-)
> Hmmm, I guess it works but it feels wrong to let errors pass silently.
> ?
>
> Regards,
> Albert-Jan
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> All right, but apart from the sanitation, the medicine, education, wine,
> public order, irrigation, roads, a
> fresh water system, and public health, what have the Romans ever done for
> us?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~?
>
>
>>________________________________
>> From: Bruce Weaver &lt;

> bruce.weaver@

> &gt;
>>To:

> SPSSX-L@.UGA

>
>>Sent: Friday, February 22, 2013 3:09 PM
>>Subject: Re: [SPSSX-L] Excel importing with missing data
>>
>>The solution David posted earlier in the thread can be made a bit more
>>general so that it works for any input file.?  E.g.,
>>
>>data list list / a b c (3F) d e f (3A).
>>begin data
>>1 2 3 a b c
>>4 5 6 d e f
>>
>>
>>end data.
>>
>>** Create two placeholder variables, make one the first variable
>>* in the file, the other the last variable.
>>
>>NUMERIC junk1 junk2 (f1).
>>MATCH FILES file = * / KEEP = junk1 all.
>>EXECUTE.
>>*
>>PRESERVE.
>>SET ERRORS OFF.
>>
>>** Initialize #numvars to -2, because junk1 and junk2 don't count.
>>COMPUTE #numvars = -2.
>>**------*.
>>COMPUTE #deadnum=0.
>>COMPUTE #deadstring=0.
>>DO REPEAT v=junk1 TO junk2.
>>+?  IF MISSING(v) #deadnum=#deadnum+1.
>>+?  IF (v EQ " ") #deadstring=#deadstring+1.
>>*+?  COMPUTE #numvars = #numvars + 1.*
>>END REPEAT.
>>SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
>>RESTORE.
>>*DELETE VARIABLES junk1 junk2.*
>>LIST.
>>
>>
>>
>>
>>Rajeshms wrote
>>> Thanks to all,
>>>
>>> But am looking for this not to happen. I am creating a syntax for future
>>> works, so that any person can work on it with out going to details of
>>> data
>>> pulling and manipulating,transforming and other tricks.
>>>
>>> I doubt some thing is wrong.Anyways am doing some research on
>>> this..........
>>>
>>> regards,
>>>
>>> Rajesh M S
>>>
>>>
>>> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett &lt;
>>
>>> mhewett@
>>
>>> &gt; wrote:
>>>
>>>> If I delete the whole row instead of the contents, it works for me.
>>>>
>>>> *Martha Hewett*?  |?  *Director of Research* | *612.335.5865* Center for
>>>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>>>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>>>
>>>> *
>>>>
>>>>
>>>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>>>
>>
>>> Henry.Ilian@.ny
>>
>>>> wrote:
>>>>
>>>>> ** ** ** ** ** ** **
>>>>>
>>>>> If you delete rows in a spreadsheet, Excel still counts the same
>>>>> number
>>>>> of rows as part of the data area of the spread sheet, and SPSS will
>>>>> still
>>>>> bring in empty rows. The best solution is to copy only what you want
>>>>> to
>>>>> bring into SPSS into a fresh spreadsheetit can be a new tab in the
>>>>> current
>>>>> workbookand bring the data in from the new worksheet. ****
>>>>>
>>>>> ** **
>>>>>
>>>>> Henry****
>>>>>
>>>>> ** **
>>>>>?  ------------------------------
>>>>>
>>>>> *From:* SPSSX(r) Discussion [mailto:**
>>
>>> SPSSX-L@.UGA
>>
>>> **] *On
>>>>> Behalf Of *Martha Hewett
>>>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>>>> *To:* **
>>
>>> SPSSX-L@.UGA
>>
>>> **
>>>>> *Subject:* Re: Excel importing with missing data****
>>>>>
>>>>> ** **
>>>>>
>>>>> You can also just "delete" all the rows in your spreadsheet that no
>>>>> longer have data.
>>>>> ****
>>>>>
>>>>> *Martha Hewett*?  | ****
>>>>>
>>>>> *Director of Research* | *612.335.5865*****
>>>>>
>>>>> Center for Energy and Environment****
>>>>>
>>>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN**
>>>>> **
>>>>> 55401********
>>>>>
>>>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>>>
>>>>> ** **
>>>>>
>>>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>>>
>>
>>> Henry.Ilian@.ny
>>
>>>> wrote:****
>>>>>
>>>>> Hi Rajesh****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Ive had that problem many times. It comes from the fact that if cells
>>>>> have been used at any time in the spreadsheet, Excel sees them as
>>>>> active
>>>>> cells, even if there is nothing currently in them. ****
>>>>>
>>>>>?  ****
>>>>>
>>>>> What I do is copy the data set, being careful to highlight the data
>>>>> and
>>>>> nothing else, into a fresh spreadsheetan unused tab in the same
>>>>> workbook
>>>>> will work. I import the copied data into SPSS, and there are no
>>>>> phantom
>>>>> cases.****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Henry****
>>>>>
>>>>>?  ****
>>>>>?  ------------------------------
>>>>>
>>>>> *From:* SPSSX(r) Discussion [mailto:
>>
>>> SPSSX-L@.UGA
>>
>>> ] *On Behalf
>>>>> Of *Rajeshms
>>>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>>>> *To:*
>>
>>> SPSSX-L@.UGA
>>
>>>>> *Subject:* Excel importing with missing data****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Dear all,****
>>>>>
>>>>>?  ****
>>>>>
>>>>> I have an?  Excel data, with 832 cases and there is no other cases
>>>>> after
>>>>> case number 832 in excel.But when I import the same data into spss , I
>>>>> can
>>>>> see the full data imported with other lots of missing (dot cases)
>>>>> data.
>>>>> I
>>>>> don know why this is coming , how to overcome this. I don need to do
>>>>> manual
>>>>> deletion of the extra missing cases in spss.,Thanks to all.
>>>>> ****
>>>>>
>>>>>?  ****
>>>>>
>>>>> -- ****
>>>>>
>>>>> Regards,****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Rajesh M S****
>>>>>
>>>>>?  ****
>>>>>
>>>>>?  ****
>>>>>
>>>>>?  ****
>>>>>
>>>>> ** **
>>>>>?  ------------------------------
>>>>>
>>>>> Confidentiality Notice: This e-mail communication, and any
>>>>> attachments,
>>>>> contains confidential and privileged information for the exclusive use
>>>>> of
>>>>> the recipient(s) named above. If you are not an intended recipient, or
>>>>> the
>>>>> employee or agent responsible to deliver it to an intended recipient,
>>>>> you
>>>>> are hereby notified that you have received this communication in error
>>>>> and
>>>>> that any review, disclosure, dissemination, distribution or copying of
>>>>> it
>>>>> or its contents is prohibited. If you have received this communication
>>>>> in
>>>>> error, please notify me immediately by replying to this message and
>>>>> delete
>>>>> this communication from your computer. Thank you.****
>>>>>
>>>>> ** **
>>>>>?  ------------------------------
>>>>>
>>>>> This e-mail transmission and any attachments accompanying it may
>>>>> contain
>>>>> confidential and/or proprietary information and is intended only for
>>>>> the
>>>>> person or entity to whom it was originally addressed. If you are not
>>>>> the
>>>>> intended recipient, you are hereby notified that any disclosure,
>>>>> copying,
>>>>> distribution or other use of this information is strictly prohibited.
>>>>> Any
>>>>> unauthorized interception of this transmission is illegal. If you have
>>>>> received this transmission in error, please notify the sender by reply
>>>>> e-mail, and then destroy all copies of this transmission****
>>>>>
>>>>
>>>>
>>>> ------------------------------
>>>> This e-mail transmission and any attachments accompanying it may
>>>> contain
>>>> confidential and/or proprietary information and is intended only for
>>>> the
>>>> person or entity to whom it was originally addressed. If you are not
>>>> the
>>>> intended recipient, you are hereby notified that any disclosure,
>>>> copying,
>>>> distribution or other use of this information is strictly prohibited.
>>>> Any
>>>> unauthorized interception of this transmission is illegal. If you have
>>>> received this transmission in error, please notify the sender by reply
>>>> e-mail, and then destroy all copies of this transmission
>>>>
>>>
>>>
>>>
>>> --
>>> Regards,
>>>
>>> Rajesh M S
>>
>>
>>
>>
>>
>>-----
>>--
>>Bruce Weaver
>>

> bweaver@

>>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/Excel-importing-with-missing-data-tp5718176p5718197.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
>>
>>
>>





-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Excel-importing-with-missing-data-tp5718176p5718206.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


Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: Excel importing with missing data

David Marso
Administrator
Besides:
"The simple solution, which works, is to copy only what you want to import into a fresh spreadsheet and import it."
Assumes:
1.  That I have Excel installed.
2.  Given 1:  That I want to bother to open Excel, locate the sheet, scroll down... down...down ,
3. COPY > PASTE ...
4. Save.
5. Exit Excel ...
And
1.  NOT be able to reproduce at will.
2.  Have to do this for every file...
--------------------------------------------------
Rather than
1. Open SPSS,
2. Open Excel File
3. Run utterly trivial syntax.
-- Analyze data--
---

King Douglas wrote
I don't disagree with your statement, " There's nothing wrong with making a simple task complicated so long as you recognize that that's what you're doing" but it is not the case that David, Bruce and others are doing that.  Instead, they are creating a new and elegant thing, a programmatic SPSS solution to a general problem, one which takes very little time in practice.  In addition, it is repeatable and can easily be checked for errors.

Come on, man!  Have you no poetry in your soul?

King Douglas
Independent consultant in survey research and data analysis

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Ilian, Henry (ACS)
Sent: Friday, February 22, 2013 3:34 PM
To: [hidden email]
Subject: Re: Excel importing with missing data

There's nothing wrong with making a simple task complicated so long as you recognize that that's what you're doing. I've encountered the empty row problem often. The simple solution, which works, is to copy only what you want to import into a fresh spreadsheet and import it. It works without problems. If there are numbers that Excel is storing as alphanumeric characters, then saving the spreadsheet as a csv file will take care of that. No doubt there are any number of Rube Goldberg devices that would also work, but why spend time that you could use analyzing data?

Henry


-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso
Sent: Friday, February 22, 2013 12:30 PM
To: [hidden email]
Subject: Re: Excel importing with missing data

I feel dirty ;-)
--

Albert-Jan Roskam wrote
> Never mind... SET ERRORS OFF.. ;-)
> Hmmm, I guess it works but it feels wrong to let errors pass silently.
> ?
>
> Regards,
> Albert-Jan
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> All right, but apart from the sanitation, the medicine, education, wine,
> public order, irrigation, roads, a
> fresh water system, and public health, what have the Romans ever done for
> us?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~?
>
>
>>________________________________
>> From: Bruce Weaver <

> bruce.weaver@

> >
>>To:

> SPSSX-L@.UGA

>
>>Sent: Friday, February 22, 2013 3:09 PM
>>Subject: Re: [SPSSX-L] Excel importing with missing data
>>
>>The solution David posted earlier in the thread can be made a bit more
>>general so that it works for any input file.?  E.g.,
>>
>>data list list / a b c (3F) d e f (3A).
>>begin data
>>1 2 3 a b c
>>4 5 6 d e f
>>
>>
>>end data.
>>
>>** Create two placeholder variables, make one the first variable
>>* in the file, the other the last variable.
>>
>>NUMERIC junk1 junk2 (f1).
>>MATCH FILES file = * / KEEP = junk1 all.
>>EXECUTE.
>>*
>>PRESERVE.
>>SET ERRORS OFF.
>>
>>** Initialize #numvars to -2, because junk1 and junk2 don't count.
>>COMPUTE #numvars = -2.
>>**------*.
>>COMPUTE #deadnum=0.
>>COMPUTE #deadstring=0.
>>DO REPEAT v=junk1 TO junk2.
>>+?  IF MISSING(v) #deadnum=#deadnum+1.
>>+?  IF (v EQ " ") #deadstring=#deadstring+1.
>>*+?  COMPUTE #numvars = #numvars + 1.*
>>END REPEAT.
>>SELECT IF SUM(#deadnum,#deadstring) LT #numvars.
>>RESTORE.
>>*DELETE VARIABLES junk1 junk2.*
>>LIST.
>>
>>
>>
>>
>>Rajeshms wrote
>>> Thanks to all,
>>>
>>> But am looking for this not to happen. I am creating a syntax for future
>>> works, so that any person can work on it with out going to details of
>>> data
>>> pulling and manipulating,transforming and other tricks.
>>>
>>> I doubt some thing is wrong.Anyways am doing some research on
>>> this..........
>>>
>>> regards,
>>>
>>> Rajesh M S
>>>
>>>
>>> On Fri, Feb 22, 2013 at 12:06 AM, Martha Hewett <
>>
>>> mhewett@
>>
>>> > wrote:
>>>
>>>> If I delete the whole row instead of the contents, it works for me.
>>>>
>>>> *Martha Hewett*?  |?  *Director of Research* | *612.335.5865* Center for
>>>> Energy and Environment 212 Third Avenue North, Suite 560 | Minneapolis,
>>>> MN 55401 (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org
>>>>
>>>> *
>>>>
>>>>
>>>> On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <
>>>>
>>
>>> Henry.Ilian@.ny
>>
>>>> wrote:
>>>>
>>>>> ** ** ** ** ** ** **
>>>>>
>>>>> If you delete rows in a spreadsheet, Excel still counts the same
>>>>> number
>>>>> of rows as part of the data area of the spread sheet, and SPSS will
>>>>> still
>>>>> bring in empty rows. The best solution is to copy only what you want
>>>>> to
>>>>> bring into SPSS into a fresh spreadsheetit can be a new tab in the
>>>>> current
>>>>> workbookand bring the data in from the new worksheet. ****
>>>>>
>>>>> ** **
>>>>>
>>>>> Henry****
>>>>>
>>>>> ** **
>>>>>?  ------------------------------
>>>>>
>>>>> *From:* SPSSX(r) Discussion [mailto:**
>>
>>> SPSSX-L@.UGA
>>
>>> **] *On
>>>>> Behalf Of *Martha Hewett
>>>>> *Sent:* Thursday, February 21, 2013 12:31 PM
>>>>> *To:* **
>>
>>> SPSSX-L@.UGA
>>
>>> **
>>>>> *Subject:* Re: Excel importing with missing data****
>>>>>
>>>>> ** **
>>>>>
>>>>> You can also just "delete" all the rows in your spreadsheet that no
>>>>> longer have data.
>>>>> ****
>>>>>
>>>>> *Martha Hewett*?  | ****
>>>>>
>>>>> *Director of Research* | *612.335.5865*****
>>>>>
>>>>> Center for Energy and Environment****
>>>>>
>>>>> ****212 Third Avenue North, Suite 560**** | ****Minneapolis**, **MN**
>>>>> **
>>>>> 55401********
>>>>>
>>>>> (cell) 612.839.2358 | (fax) 612.335.5888 | *www.mncee.org*****
>>>>>
>>>>> ** **
>>>>>
>>>>> On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <
>>>>>
>>
>>> Henry.Ilian@.ny
>>
>>>> wrote:****
>>>>>
>>>>> Hi Rajesh****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Ive had that problem many times. It comes from the fact that if cells
>>>>> have been used at any time in the spreadsheet, Excel sees them as
>>>>> active
>>>>> cells, even if there is nothing currently in them. ****
>>>>>
>>>>>?  ****
>>>>>
>>>>> What I do is copy the data set, being careful to highlight the data
>>>>> and
>>>>> nothing else, into a fresh spreadsheetan unused tab in the same
>>>>> workbook
>>>>> will work. I import the copied data into SPSS, and there are no
>>>>> phantom
>>>>> cases.****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Henry****
>>>>>
>>>>>?  ****
>>>>>?  ------------------------------
>>>>>
>>>>> *From:* SPSSX(r) Discussion [mailto:
>>
>>> SPSSX-L@.UGA
>>
>>> ] *On Behalf
>>>>> Of *Rajeshms
>>>>> *Sent:* Thursday, February 21, 2013 6:51 AM
>>>>> *To:*
>>
>>> SPSSX-L@.UGA
>>
>>>>> *Subject:* Excel importing with missing data****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Dear all,****
>>>>>
>>>>>?  ****
>>>>>
>>>>> I have an?  Excel data, with 832 cases and there is no other cases
>>>>> after
>>>>> case number 832 in excel.But when I import the same data into spss , I
>>>>> can
>>>>> see the full data imported with other lots of missing (dot cases)
>>>>> data.
>>>>> I
>>>>> don know why this is coming , how to overcome this. I don need to do
>>>>> manual
>>>>> deletion of the extra missing cases in spss.,Thanks to all.
>>>>> ****
>>>>>
>>>>>?  ****
>>>>>
>>>>> -- ****
>>>>>
>>>>> Regards,****
>>>>>
>>>>>?  ****
>>>>>
>>>>> Rajesh M S****
>>>>>
>>>>>?  ****
>>>>>
>>>>>?  ****
>>>>>
>>>>>?  ****
>>>>>
>>>>> ** **
>>>>>?  ------------------------------
>>>>>
>>>>> Confidentiality Notice: This e-mail communication, and any
>>>>> attachments,
>>>>> contains confidential and privileged information for the exclusive use
>>>>> of
>>>>> the recipient(s) named above. If you are not an intended recipient, or
>>>>> the
>>>>> employee or agent responsible to deliver it to an intended recipient,
>>>>> you
>>>>> are hereby notified that you have received this communication in error
>>>>> and
>>>>> that any review, disclosure, dissemination, distribution or copying of
>>>>> it
>>>>> or its contents is prohibited. If you have received this communication
>>>>> in
>>>>> error, please notify me immediately by replying to this message and
>>>>> delete
>>>>> this communication from your computer. Thank you.****
>>>>>
>>>>> ** **
>>>>>?  ------------------------------
>>>>>
>>>>> This e-mail transmission and any attachments accompanying it may
>>>>> contain
>>>>> confidential and/or proprietary information and is intended only for
>>>>> the
>>>>> person or entity to whom it was originally addressed. If you are not
>>>>> the
>>>>> intended recipient, you are hereby notified that any disclosure,
>>>>> copying,
>>>>> distribution or other use of this information is strictly prohibited.
>>>>> Any
>>>>> unauthorized interception of this transmission is illegal. If you have
>>>>> received this transmission in error, please notify the sender by reply
>>>>> e-mail, and then destroy all copies of this transmission****
>>>>>
>>>>
>>>>
>>>> ------------------------------
>>>> This e-mail transmission and any attachments accompanying it may
>>>> contain
>>>> confidential and/or proprietary information and is intended only for
>>>> the
>>>> person or entity to whom it was originally addressed. If you are not
>>>> the
>>>> intended recipient, you are hereby notified that any disclosure,
>>>> copying,
>>>> distribution or other use of this information is strictly prohibited.
>>>> Any
>>>> unauthorized interception of this transmission is illegal. If you have
>>>> received this transmission in error, please notify the sender by reply
>>>> e-mail, and then destroy all copies of this transmission
>>>>
>>>
>>>
>>>
>>> --
>>> Regards,
>>>
>>> Rajesh M S
>>
>>
>>
>>
>>
>>-----
>>--
>>Bruce Weaver
>>

> bweaver@

>>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/Excel-importing-with-missing-data-tp5718176p5718197.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
>>
>>
>>





-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Excel-importing-with-missing-data-tp5718176p5718206.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


Confidentiality Notice: This e-mail communication, and any attachments, contains confidential and privileged information for the exclusive use of the recipient(s) named above. If you are not an intended recipient, or the employee or agent responsible to deliver it to an intended recipient, you are hereby notified that you have received this communication in error and that any review, disclosure, dissemination, distribution or copying of it or its contents is prohibited. If you have received this communication in error, please notify me immediately by replying to this message and delete this communication from your computer. Thank you.

=====================
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
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"
12