Read an Excel field as string even though there are only numbers in this field

Question

I have an Excel spreadsheet with, among onther things, numbers that are identifiers.
My odbc reader should return me something like '55201562000016', but too often it returns me something like '5.52016e+008'.

I only do the following, but apparently it does not suffice to tell the reader that it should be treated as a string and not an exponential integer :

siren = rdrxls("entr_siren").ToString().Trim()
siret = rdrxls("entr_siret").ToString().Trim()

I have tried this :

siren = Convert.ToInt64(rdrxls("entr_siren")).ToString().Trim()
siret = Convert.ToInt64(rdrxls("entr_siret")).ToString().Trim()

It should work, but sometimes it throws exceptions, because sometimes the data is erroneous (with letters for example) ; but I still need to fetch it.

Any idea ?

Thanks

Answer 1

I think, the following way could help you to overcome the problem :

double converted=0;
double.TryParse("5.52016e+008", out converted);

Answer 2

IIRC, the Excel ODBC reader looks at the first eight rows to "infer" the data type for the columns.

The first row is taken as column headers, so putting text in the second row should be enough to force it to infer a string data type.

Once you've done that

rdrxls("entr_siren")

should return a string, not any kind of numeric.

Answer 3

You could use Int64.TryParse() to see if you can cast the result into an Int64. TryParse() returns a boolean, indicating whether the cast was successfull or not. If it failed, keep the value as is, if it was successfull, overwrite the previously stored value with the ToString() of the cast result.

double sirenValue;
double siretValue;

siren = rdrxls("entr_siren").ToString().Trim();
siret = rdrxls("entr_siret").ToString().Trim();


if (double.TryParse(siren, System.Globalization.NumberStyles.Float, System.Globalization.CultureInfo.InvariantCulture, out sirenValue))
{
    siren = sirenValue.ToString();
}

if (double.TryParse(siret, System.Globalization.NumberStyles.Float, System.Globalization.CultureInfo.InvariantCulture, out siretValue))
{
    siret = siretValue.ToString();
}

Hope this helps.