Re: st: Strings dropped when importing Excel file

[Friedrich Huebler <fhuebler@gmail.com>]

Thank you, Sergiy.

The Excel file that I am trying to import contains only text, no
formulas. The entries #DIV/0! (which is also replaced by an empty cell
as I discovered), #N/A and #VALUE! are stored as text in their
respective cells, together with other text in the same columns. The
Excel file that you created contains a formula that yields a #DIV/0!
error and this formula (=7/A2) can be seen when one clicks on the
cell. My file is different, there are no formulas, and therefore I
would expect all variables to be imported without changes.

Friedrich

On Fri, Nov 8, 2013 at 1:52 PM, Sergiy Radyakin <serjradyakin@gmail.com> wrote:
> 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/