Bookmark and Share

Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down at the end of May, and its replacement, statalist.org is already up and running.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: st: Mergining horozontally excel files under a particular patern


From   sabbas gidarokostas <sabbasgidarokostas@googlemail.com>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: Mergining horozontally excel files under a particular patern
Date   Wed, 4 Jul 2012 16:35:47 +0200

Thank you Scott and Nick
see below what I obtain


clear

tempfile tmp
local j = 1
forv i = 39(-1)1 {
        import excel "C:\wer.xlsx", sheet("Sheet`i'") firstrow clear
       if mod(`i',3) !=0 {
                qui merge 1:1 _n using `tmp', nogenerate
                save `tmp' ,replace
        }
        else {
                save `tmp', replace
        }
        if mod(`j', 3) == 0 {
                save gr`i', replace
                local j = 1
        }
        else {
                local j = `j' + 1
        }
}


unrecognized command:  import
r(199);


Thanks in advance


On 7/4/12, Nick Cox <njcoxstata@gmail.com> wrote:
> You did not try what Scott suggested.
>
> He suggested -import excel- using an .xlsx file. You changed that to
> -insheet- using a .csv file. -insheet- has no notion of separate work
> sheets. You can't mush up two separate commands like tbis.
>
> Nick
>
> On Wed, Jul 4, 2012 at 8:59 AM, sabbas gidarokostas
> <sabbasgidarokostas@googlemail.com> wrote:
>> I made the correction but
>>
>>  tempfile tmp
>>
>> .
>> . local j = 1
>>
>> .
>> . forv i = 39(-1)1 {
>>   2.
>> . insheet using "wer.csv", sheet("Sheet`i'") firstrow clear
>>   3.
>> . if mod(`i',3) !=0 {
>>   4.
>> .                 qui merge 1:1 _n using `tmp', nogenerate
>>   5.
>> .                 save `tmp' ,replace
>>   6.
>> .         }
>>   7.
>> .         else {
>>   8.
>> .                 save `tmp', replace
>>   9.
>> .         }
>>  10.
>> .         if mod(`j', 3) == 0 {
>>  11.
>> .                 save gr`i', replace
>>  12.
>> .                 local j = 1
>>  13.
>> .         }
>>  14.
>> .         else {
>>  15.
>> .                 local j = `j' + 1
>>  16.
>> .         }
>>  17.
>> . }
>> option sheet() not allowed
>> r(198);
>>
>> Why do I receive this mistake?
>>
>> thanks in advance
>>
>> On 7/4/12, David Radwin <dradwin@mprinc.com> wrote:
>>> I think you lost a comma and broke the line right before "sheet."
>>> -sheet-
>>> is an option of the -import excel- command. The fourth and fifth lines
>>> should be one line like this:
>>>
>>> . insheet using "wer.csv", sheet("Sheet`i'") firstrow clear
>>>
>>>
>>> David
>>> --
>>> David Radwin
>>> Senior Research Associate
>>> MPR Associates, Inc.
>>> 2150 Shattuck Ave., Suite 800
>>> Berkeley, CA 94704
>>> Phone: 510-849-4942
>>> Fax: 510-849-0794
>>>
>>> www.mprinc.com
>>>
>>>
>>>> -----Original Message-----
>>>> From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-
>>>> statalist@hsphsun2.harvard.edu] On Behalf Of sabbas gidarokostas
>>>> Sent: Tuesday, July 03, 2012 2:04 PM
>>>> To: statalist@hsphsun2.harvard.edu
>>>> Subject: Re: st: Mergining horozontally excel files under a particular
>>>> patern
>>>>
>>>> Hi scott
>>>>
>>>>
>>>> I tried what you said
>>>>
>>>> clear
>>>>
>>>>
>>>> tempfile tmp
>>>> local j = 1
>>>> forv i = 39(-1)1 {
>>>> insheet using "wer.csv"
>>>> sheet("Sheet`i'") firstrow clear
>>>>         if mod(`i',3) !=0 {
>>>>                 qui merge 1:1 _n using `tmp', nogenerate
>>>>                 save `tmp' ,replace
>>>>         }
>>>>         else {
>>>>                 save `tmp', replace
>>>>         }
>>>>         if mod(`j', 3) == 0 {
>>>>                 save gr`i', replace
>>>>                 local j = 1
>>>>         }
>>>>         else {
>>>>                 local j = `j' + 1
>>>>         }
>>>> }
>>>>
>>>>
>>>> but I get the mistake
>>>>
>>>> unrecognized command:  sheet
>>>>
>>>> thanks
>>>>
>>>> On 7/3/12, sabbas gidarokostas <sabbasgidarokostas@googlemail.com>
>>> wrote:
>>>> > thanks scott. I will try this. Many thanks
>>>> >
>>>> > On 7/3/12, Scott Merryman <scott.merryman@gmail.com> wrote:
>>>> >> Something like:
>>>> >>
>>>> >> clear
>>>> >> tempfile tmp
>>>> >> local j = 1
>>>> >> forv i = 39(-1)1 {
>>>> >>   import excel "C:\Users\scott.merryman\Desktop\Book1.xlsx",
>>>> >> sheet("Sheet`i'") firstrow clear
>>>> >>   if mod(`i',3) !=0 {
>>>> >>           qui merge 1:1 _n using `tmp', nogenerate
>>>> >>           save `tmp' ,replace
>>>> >>   }
>>>> >>   else {
>>>> >>           save `tmp', replace
>>>> >>   }
>>>> >>   if mod(`j', 3) == 0 {
>>>> >>           save gr`i', replace
>>>> >>           local j = 1
>>>> >>   }
>>>> >>   else {
>>>> >>           local j = `j' + 1
>>>> >>   }
>>>> >> }
>>>> >>
>>>> >>
>>>> >>
>>>> >> Scott
>>>> >>
>>>> >> On Mon, Jul 2, 2012 at 12:31 PM, sabbas gidarokostas
>>>> >> <sabbasgidarokostas@googlemail.com> wrote:
>>>> >>> I have an excel file that contains 39 sheets
>>>> >>> 1,2,3,4,5,6,7,8,9,10,11,..
>>>> >>>
>>>> >>> I want tfirst o merge sheet 3 with 2 and then merge the new sheet
>>> that
>>>> >>> results from this merging  with sheet 1. So 3+2+1
>>>> >>> Similarly, I want to merge first sheet 6 with 5 and then merge the
>>> new
>>>> >>> sheet that results from this merging with sheet 4. So. 6+5+4
>>>> >>> Similarly,  I want to merge first sheet 9 with 8 and then merge the
>>>> >>> new sheet that results from this merging with sheet 7. So,  9+8+7
>>>> >>> And so forth
>>>> >>> To be more specific,
>>>> >>> My goal is to merge horizontally all the data contained in sheet 3
>>>> >>> with the data contained in the matrix [C5 up to the C-end and C5 up
>>> to
>>>> >>> End-5] of Sheet 2. The outcome of this merging is then merged
>>>> >>> horizontally with the data contained in the matrix [C5 up to the
>>> C-end
>>>> >>> and C5 up to End-5] of Sheet 1
>>>> >>>
>>>> >>> Similarly, I  want to merge horizontally all the data contained in
>>>> >>> sheet 6 with the data contained in the matrix [C5 up to the C-end
>>> and
>>>> >>> C5 up to End-5] of Sheet 5. The outcome of this merging is then
>>> merged
>>>> >>> horizontally with the data contained in the matrix [C5 up to the
>>> C-end
>>>> >>> and C5 up to End-5] of Sheet 4
>>>> >>>
>>>> >>> and so forth.
>>>> >>>
>>>> >>> Is there any way to do this particular merging
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/
>
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/


© Copyright 1996–2014 StataCorp LP   |   Terms of use   |   Privacy   |   Contact us   |   Site index