Notice: On March 31, it was **announced** that Statalist is moving from an email list to a **forum**. The old list will shut down on April 23, and its replacement, **statalist.org** is already up and running.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

From |
Nick Cox <njcoxstata@gmail.com> |

To |
statalist@hsphsun2.harvard.edu |

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

**References**:**st: Mergining horozontally excel files under a particular patern***From:*sabbas gidarokostas <sabbasgidarokostas@googlemail.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*Scott Merryman <scott.merryman@gmail.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*sabbas gidarokostas <sabbasgidarokostas@googlemail.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*sabbas gidarokostas <sabbasgidarokostas@googlemail.com>

**RE: st: Mergining horozontally excel files under a particular patern***From:*"David Radwin" <dradwin@mprinc.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*sabbas gidarokostas <sabbasgidarokostas@googlemail.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*Nick Cox <njcoxstata@gmail.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*sabbas gidarokostas <sabbasgidarokostas@googlemail.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*Nick Cox <njcoxstata@gmail.com>

**Re: st: Mergining horozontally excel files under a particular patern***From:*sabbas gidarokostas <sabbasgidarokostas@googlemail.com>

- Prev by Date:
**st: RE: xtivreg2: orthog option** - Next by Date:
**st: RE: on interactions of endogenous variables with an exogenous one and xtivreg2** - Previous by thread:
**Re: st: Mergining horozontally excel files under a particular patern** - Next by thread:
**st: storing/retrieving regression coefficient estimates (xtreg)** - Index(es):