Re: st: Strings dropped when importing Excel file

[Sergiy Radyakin <serjradyakin@gmail.com>]

Friedrich,

to the best of my knowledge the importer would take the
'cached'/'computed' value for each cell. These are stored in the excel
file by Excel itself. Sometimes, if you open really old file, Excel
would tell you, that these values were saved after computing the
formulas using an older version of Excel, and it needs to recompute
them in the newer version (this warning has many reasons to be
crucial, as recomputing simply may not succeed at all).

Anyways, both values that you mention seem to be not 'values' but
representation - something that Excel shows you in the cell, based not
only on the genuine cell value, but also the formula, and other cell
attributes (note that you have TEXT separate from VALUE separate from
FORMULA for each cell). In this case these are representations of the
errors.

Depending on the complexity of the Excel file different suggestions
might be suitable. My first suggestion would be to use Excel's
functions ISERR() and IFERROR() to obtain values, that you can later
manage in Stata.
See the example here:
http://radyakin.org/statalist/2013110801/func.xlsx
(I expect you would have the problem you described with col B but not col C)

If you have errors of different types, use the ERROR.TYPE() function
to distinguish between them. Not sure which Excel versions have this
function, it might be fairly recent. Based on this reference, after
2000:
http://www.techonthenet.com/excel/formulas/error_type.php

Hope this helps. Don't hesitate to ask if you need more information.

Best, Sergiy Radyakin




On Fri, Nov 8, 2013 at 1:23 PM, Friedrich Huebler <fhuebler@gmail.com> wrote:
> I use Stata 12.1. When importing an Excel file with string variables
> (cell format "Text"), the variables are correctly imported as strings
> but the following entries are replaced by empty cells in Stata:
>
> #N/A
> #VALUE!
>
> Is it possible to keep these strings? I found no information on this
> in the documentation for -import excel-. The -allstring- option makes
> no difference.
>
> Thanks,
>
> Friedrich
> *
> *   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/