Re: st: import excel

["Jenniffer Solorzano Mosquera" <Jenniffer.Solorzano.Mosquera@jibs.hj.se>]

Thanks, but actually the problem is not how to do the loop. The problem is that several lines of different information are contained in the same cell in excel. Is there any way to make STATA understand please keep it as different cells, even if they aren't in excel but are tabulated one below the other one and so on?

>>> Eric Booth <eric.a.booth@gmail.com> 2012-04-12 15:16 >>>

<>



On Apr 12, 2012, at 5:22 AM, Jenniffer Solorzano Mosquera wrote:

> Hi,
> 
> Is there any way to import several files from excel with a loop in such a =
> way I preserve the original format of the data?
> 

Yes, I would use the techniques discussed in these threads for importing a large # of files using a loop and then converting to .dta format (and possibly combining/appending the data at the same time):


http://www.stata.com/statalist/archive/2012-04/msg00317.html "st: Re: Concatenate files"
http://www.stata.com/statalist/archive/2010-01/msg00239.html "Re: global macro for system directory?"
http://www.stata.com/statalist/archive/2010-11/msg00111.html "Re: st: repeat same commands over hundreds of files"


> I have 100 excel reports of 100 firms each one. The problem is that every =
> single row is a firm but the firm has many observations by date in the =
> same cell. In excel you can see that with format the information is =
> possible to read because they are separated in terms of format but they =
> are in the same cell.=20
> For example:
> This list is information of firm 1, and it is only in one cell:
> 09/01/2010
> 09/01/2009
> 09/01/2008
> 12/31/2007
> 12/31/2006
> This list is for firm 2, in the next row and so on:
> 04/01/2010
> 04/01/2009
> 04/01/2008
> 12/31/2007
> 12/31/2006
> When I import everything appears in only one cell in the dta file. The =
> problem is that there is no regular pattern of the variables. Some columns =
> are value of investments (not only dates) so you can not separate by =
> programming a regular loop to identify the information imported. I can =
> identify the dates because they are made from 10 digits always, but the =
> same doesn't happen with other type of information.
> Any recommendation?


You first need to inspect whether all your data in those excel long cells are making it into Stata when you -import- the file -- Stata has a string length lim. of 244 chars, so if any of those combined cells are too long, you'll have truncated data and need to consider pre-processing the file.  You can do that in Excel or possibly use -filefilter- (-help filefilter-).

If all the data in these long cells are making it into Stata, then your goal is to -split- the cells using either -split- or some combination of string functions (-help string_functions-).  
If all your cells looked like the data example you gave, then you could run:  

split v1

and it would create separate vars from this space-delimited string.  However, where you have  other delimiters in a cell/string, you'd need to adjust -split-'s parse() option (or some other string_function) accordingly.  Finally, if you have different numbers of elements within a string, you'll get different numbers of variables created by -split-, so you'll have to do some work to organize your new variables, but without seeing that data I couldn't guess about the strategy.

- Eric



__
Eric A. Booth
Public Policy Research Institute 
Texas A&M University
ebooth@ppri.tamu.edu 
+979.845.6754





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