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

[Nick Cox <njcoxstata@gmail.com>]

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/