Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: Mergining horozontally excel files under a particular patern
From
Nick Cox <[email protected]>
To
[email protected]
Subject
Re: st: Mergining horozontally excel files under a particular patern
Date
Wed, 4 Jul 2012 18:51:51 +0100
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
<[email protected]> 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 <[email protected]> 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
>> <[email protected]> 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 <[email protected]> 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
>>>> <[email protected]> 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 <[email protected]> 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: [email protected] [mailto:owner-
>>>>>>> [email protected]] On Behalf Of sabbas gidarokostas
>>>>>>> Sent: Tuesday, July 03, 2012 2:04 PM
>>>>>>> To: [email protected]
>>>>>>> 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 <[email protected]>
>>>>>> wrote:
>>>>>>> > thanks scott. I will try this. Many thanks
>>>>>>> >
>>>>>>> > On 7/3/12, Scott Merryman <[email protected]> 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
>>>>>>> >> <[email protected]> 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/