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

[Nick Cox <njcoxstata@gmail.com>]

-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/