Bookmark and Share

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: import excel Command: Automatic Adjustment of cellrange Option in Loop


From   Roberto Liebscher <roberto.liebscher@ku.de>
To   statalist@hsphsun2.harvard.edu
Subject   Re: st: import excel Command: Automatic Adjustment of cellrange Option in Loop
Date   Fri, 21 Feb 2014 22:41:33 +0100

It is indeed motivated by the fact that the "real" data starts in the 3rd row. It is the simple way Scott pointed out that works best. I did not expect it to be that easy.

Thanks to you two!

Am 21.02.2014 21:48, schrieb Sergiy Radyakin:
Roberto, isn't this what Stata is doing all by itself when you don't
specify the range of cells to be imported? Note that both the syntax
and the first examples for import excel demonstrate that cellrange is
an _optional_ option. You can skip it!

If that is motivated by the data starting in the 3rd row, then the
simple solution would be to call import, count the number of lines,
subtract 2, discard the result, import again, but now you know the
exact number of rows.

Best, Sergiy Radyakin

On Fri, Feb 21, 2014 at 3:36 PM, Roberto Liebscher
<roberto.liebscher@ku.de> wrote:
Hello Statalisters,

I have got several excel files at hand which I would like to convert into
*.dta files. I would like to write a loop importing all the excel files and
save them as *.dta. However, the problem is that the cellrange is not the
same for every file. So I would be happy to know if there is a way to
"automate" the adjustment for the cellrange option?

In the end the loop should look like this:

forvalues i=1/100 {
        import excel "C:\`i'.xlsx", cellrange(A3:XX) firstrow clear
         save `i'.dta
}

where XX is supposed to be the last cell in the bottom right of the
worksheet that has a value in it. Is there any chance to do this?

Any help is highly appreciated.

Best,
Roberto

--
Roberto Liebscher
Catholic University of Eichstaett-Ingolstadt
Department of Business Administration
Chair of Banking and Finance
Auf der Schanz 49
D-85049 Ingolstadt
Germany
Phone:  (+49)-841-937-1929
FAX:    (+49)-841-937-2883
E-mail:   roberto.liebscher@ku-eichstaett.de
Internet: http://www.ku.de/wwf/lfb/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/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/faqs/resources/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/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/


© Copyright 1996–2018 StataCorp LLC   |   Terms of use   |   Privacy   |   Contact us   |   Site index