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

[sabbas gidarokostas <sabbasgidarokostas@googlemail.com>]

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/