Excel importing with missing data
Locked
12
12
Re: Excel importing with missing data
 
|
Well, there's e.e. cummings and there's John Lyly. You may see what David, Bruce and others are doing as poetry, and you may be right, but if so, it looks a bit euphuistic to me.
-----Original Message----- From: King Douglas [mailto:[hidden email]] Sent: Friday, February 22, 2013 5:39 PM To: Ilian, Henry (ACS); [hidden email] Subject: 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 |
Automatic reply: Excel importing with missing data
|
|
I will be taking time off during the week of February 25; returning to regular schedule on March 4. I will check email daily and can be reached
by phone (410-258-4623). |
Re: Excel importing with missing data
Administrator
|
In reply to this post by henryilian
And there is Crowley, Goethe, Gira, Morrison, Blake, Lennon....
What's your point? I actually had to look up that word: euphuistic ?? Hardly: The code solves a specific requirement with no more/no less than necessary and does NOT require manual intervention!! ---
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 henryilian
I agree with David. And I wish you good luck and all the strength you need when the day comes that you need to import a couple THOUSAND spreadsheets.
XLConnect (R, with Java under the hood) and xlrd (Python) also offer really flexible ways to deal with Excel data. The ultimate solution, though, would be to burn all the Excel files of the world and chant! *wish, wish* 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
|
|
No, I think the ultimate solution would be an SPSS import function that actually worked properly! Garry From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Albert-Jan Roskam I agree with David. And I wish you good luck and all the strength you need when the day comes that you need to import a couple THOUSAND spreadsheets. Regards, ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ From: "Ilian, Henry (ACS)" <[hidden email]>
|
Re: Excel importing with missing data
|
Administrator
|
How would any import function KNOW that the blank lines aren't there for entertainment/purpose/shits and giggles?
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
|
|
Same way it KNOWS whether the first row contains column labels or data. Because you have an option to tell it.
-----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: 24 February 2013 18:48 To: [hidden email] Subject: Re: Excel importing with missing data How would any import function KNOW that the blank lines aren't there for entertainment/purpose/shits and giggles? Garry Gelade wrote > No, I think the ultimate solution would be an SPSS import function > that actually worked properly! > > > > Garry > > > > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of Albert-Jan Roskam > Sent: 23 February 2013 08:52 > To: > SPSSX-L@.UGA > Subject: Re: Excel importing with missing data > > > > I agree with David. And I wish you good luck and all the strength you > need when the day comes that you need to import a couple THOUSAND spreadsheets. > > XLConnect (R, with Java under the hood) and xlrd (Python) also offer > really flexible ways to deal with Excel data. > > The ultimate solution, though, would be to burn all the Excel files of > the world and chant! *wish, wish* > > > > 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: "Ilian, Henry (ACS)" < > Henry.Ilian@.ny > > > To: > SPSSX-L@.UGA > > Sent: Friday, February 22, 2013 10:33 PM > Subject: Re: [SPSSX-L] 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: > SPSSX-L@.UGA > ] On Behalf Of David Marso > Sent: Friday, February 22, 2013 12:30 PM > To: > SPSSX-L@.UGA > 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-mis > sing-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-mis > sing-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 > 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 > > > 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 > 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-tp5718176p5718229.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD |
Re: Excel importing with missing data
|
|
Yes, I was thinking something like that too. A new option for GET TRANSLATE, DITCHBLANKLINES, with a default value of YES.
� 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? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~� ----- Original Message ----- > From: Garry Gelade <[hidden email]> > To: [hidden email] > Cc: > Sent: Sunday, February 24, 2013 8:24 PM > Subject: Re: [SPSSX-L] Excel importing with missing data > > Same way it KNOWS whether the first row contains column labels or data. Because > you have an option to tell it. > > -----Original Message----- > From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David > Marso > Sent: 24 February 2013 18:48 > To: [hidden email] > Subject: Re: Excel importing with missing data > > How would any import function KNOW that the blank lines aren't there for > entertainment/purpose/shits and giggles? > > Garry Gelade wrote >> No, I think the ultimate solution would be an SPSS import function >> that actually worked properly! >> >> >> >> Garry >> >> >> >> From: SPSSX(r) Discussion [mailto: > >> SPSSX-L@.UGA > >> ] On Behalf Of Albert-Jan Roskam >> Sent: 23 February 2013 08:52 >> To: > >> SPSSX-L@.UGA > >> Subject: Re: Excel importing with missing data >> >> >> >> I agree with David. And I wish you good luck and all the strength you >> need when the day comes that you need to import a couple THOUSAND > spreadsheets. >> >> XLConnect (R, with Java under the hood) and xlrd (Python) also offer >> really flexible ways to deal with Excel data. >> >> The ultimate solution, though, would be to burn all the Excel files of >> the world and chant! *wish, wish* >> >> >> >> 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: "Ilian, Henry (ACS)" < > >> Henry.Ilian@.ny > >> > >> To: > >> SPSSX-L@.UGA > >> >> Sent: Friday, February 22, 2013 10:33 PM >> Subject: Re: [SPSSX-L] 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: > >> SPSSX-L@.UGA > >> ] On Behalf Of David Marso >> Sent: Friday, February 22, 2013 12:30 PM >> To: > >> SPSSX-L@.UGA > >> 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-mis >> sing-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-mis >> sing-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 > >> 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 >> >> >> 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 > >> 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-tp5718176p5718229.html > Sent from the SPSSX Discussion mailing list archive at Nabble.com. > > ===================== > To manage your subscription to SPSSX-L, send a message to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > > ===================== > To manage your subscription to SPSSX-L, send a message to > [hidden email] (not to SPSSX-L), with no body text except the > command. To leave the list, send the command > SIGNOFF SPSSX-L > For a list of commands to manage subscriptions, send the command > INFO REFCARD > ===================== 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
|
|
In reply to this post by David Marso
>& quot;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. I just realized that this syntax can be made a teeny weeny bit nicer/ more generic by using the ALL keyword. * replaces all terminating null bytes in any dataset. preserve. set errors none. do repeat s = all. +compute s = replace(s, string(0, pib1)," "). end repeat. restore. ===================== 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 |
Automatic reply: Excel importing with missing data
|
|
Thank you for your email. Please note that I am offsite at the Beyond Reporting Workshop. I will respond to your email upon my return to the office on Wednesday February 27.
Valerie Villella Education Coordinator & Policy and Program Analyst OANHSS 905-851-8821 ext. 228 ===================== 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
|
In reply to this post by Albert-Jan Roskam
Nice use of ALL, Albert-Jan. That beats the "junk1 to junk2" method I posted earlier!
--
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 Garry Gelade
When I import from excel, and it pulls in blank rows that I want to delete,
My first syntax command is one that selects only the records with data in a primary key. SELECT IF NOT missing(key variable/ID). Or SELECT IF [IDvariable] >0. Then Save the clean file. M -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Garry Gelade Sent: Sunday, February 24, 2013 1:25 PM To: [hidden email] Subject: Re: [SPSSX-L] Excel importing with missing data Same way it KNOWS whether the first row contains column labels or data. Because you have an option to tell it. -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of David Marso Sent: 24 February 2013 18:48 To: [hidden email] Subject: Re: Excel importing with missing data How would any import function KNOW that the blank lines aren't there for entertainment/purpose/shits and giggles? Garry Gelade wrote > No, I think the ultimate solution would be an SPSS import function > that actually worked properly! > > > > Garry > > > > From: SPSSX(r) Discussion [mailto: > SPSSX-L@.UGA > ] On Behalf Of Albert-Jan Roskam > Sent: 23 February 2013 08:52 > To: > SPSSX-L@.UGA > Subject: Re: Excel importing with missing data > > > > I agree with David. And I wish you good luck and all the strength you > need when the day comes that you need to import a couple THOUSAND spreadsheets. > > XLConnect (R, with Java under the hood) and xlrd (Python) also offer > really flexible ways to deal with Excel data. > > The ultimate solution, though, would be to burn all the Excel files of > the world and chant! *wish, wish* > > > > 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: "Ilian, Henry (ACS)" < > Henry.Ilian@.ny > > > To: > SPSSX-L@.UGA > > Sent: Friday, February 22, 2013 10:33 PM > Subject: Re: [SPSSX-L] 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: > SPSSX-L@.UGA > ] On Behalf Of David Marso > Sent: Friday, February 22, 2013 12:30 PM > To: > SPSSX-L@.UGA > 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-mis > sing-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-mis > sing-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 > 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 > > > 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 > 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-tp5718176p5718229.html Sent from the SPSSX Discussion mailing list archive at Nabble.com. ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD ===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD PRIVILEGED AND CONFIDENTIAL INFORMATION This transmittal and any attachments may contain PRIVILEGED AND CONFIDENTIAL information and is intended only for the use of the addressee. If you are not the designated recipient, or an employee or agent authorized to deliver such transmittals to the designated recipient, you are hereby notified that any dissemination, copying or publication of this transmittal is strictly prohibited. If you have received this transmittal in error, please notify us immediately by replying to the sender and delete this copy from your system. You may also call us at (309) 827-6026 for assistance. ===================== 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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |