Excel importing with missing data
Locked
12
12
 
|
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
|
Re: Excel importing with missing data
|
|
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 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 |
Re: Excel importing with missing data
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.
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?" |
Re: Excel importing with missing data
|
|
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.
On Thu, Feb 21, 2013 at 7:57 AM, Maguin, Eugene <[hidden email]> wrote:
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
|
Re: Excel importing with missing data
|
|
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: 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. |
Re: Excel importing with missing data
|
|
You can also just "delete" all the rows in your spreadsheet that no longer have data.
On Thu, Feb 21, 2013 at 9:55 AM, Ilian, Henry (ACS) <[hidden email]> wrote:
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
|
Re: Excel importing with missing data
|
|
If I delete the whole row instead of the contents, it works for me.
On Thu, Feb 21, 2013 at 11:43 AM, Ilian, Henry (ACS) <[hidden email]> wrote:
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
|
|
|
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. Regards, Rajesh M S |
|
|
I am currently out. Please contact Fran Lucero ([hidden email]) for immediate assistance. |
Re: Excel importing with missing data
|
|
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? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
Re: Excel importing with missing data
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.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Excel importing with missing data
|
|
.... + 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? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
Re: Excel importing with missing data
|
|
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? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
Re: Excel importing with missing data
|
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 ;-) --
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?" |
Re: Excel importing with missing data
|
Administrator
|
In reply to this post by Albert-Jan Roskam
I feel dirty ;-)
--
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?" |
Re: Excel importing with missing data
|
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.
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Excel importing with missing data
|
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
--
Bruce Weaver bweaver@lakeheadu.ca http://sites.google.com/a/lakeheadu.ca/bweaver/ "When all else fails, RTFM." PLEASE NOTE THE FOLLOWING: 1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above. 2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/). |
Re: Excel importing with missing data
|
|
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 < > 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 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 |
Re: Excel importing with missing data
|
|
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 |
Re: Excel importing with missing data
|
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-- ---
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?" |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |