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
sabbas gidarokostas <[email protected]>
To
[email protected]
Subject
Re: st: Mergining horozontally excel files under a particular patern
Date
Wed, 4 Jul 2012 16:35:47 +0200
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/