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

[Nick Cox <njcoxstata@gmail.com>]

Guess confirmed.

I imagine if you had Stat/Transfer you would have tried it.

You can save separate worksheets in Excel as .csv (e.g.), import them
into Stata and then -merge-. Much of Scott's code still applies, but
-import excel- is, as said, out of the question for you unless you
upgrade to 12.

Someone may be able to improve on this.

Nick

On Wed, Jul 4, 2012 at 5:59 PM, sabbas gidarokostas
<sabbasgidarokostas@googlemail.com> wrote:
> thank you both. Yes, indeed I did not mention which version I use. I
> use version 11 and not the latest one.
>
> Thanks again!
>
> On 7/4/12, Nick Cox <njcoxstata@gmail.com> wrote:
>> -import excel- is a valid command in Stata 12. See for example
>>
>> http://www.stata.com/help.cgi?import_excel
>>
>> My guess is this: You are not using Stata 12. A scan through the
>> thread indicates that you did not explain this. The Statalist FAQ
>> spells out at
>>
>> http://www.stata.com/support/faqs/resources/statalist-faq/#stata
>>
>> "The current version of Stata is 12.1. Please specify if you are using
>> an earlier version; otherwise, the answer to your question is likely
>> to refer to commands or features unavailable to you."
>>
>> Any further advice would need, at a minimum, you to explain what
>> version you are using.
>>
>> Nick
>>
>> On Wed, Jul 4, 2012 at 3:35 PM, sabbas gidarokostas
>> <sabbasgidarokostas@googlemail.com> wrote:
>>> 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/
*
*   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/