Importing numbers from Excel

Question

I guess my problem is very simple but I could not find the solution by searching it.

I have a column in excel sheet (Sheet1 in Book1.xlsx) including student numbers as the following.

Student ID
2014512060
2013512024
2013512028

When I import these numbers from Excel to Mathematica by

Import["C:\\Book1.xlsx", {"Sheets", "Sheet1"}]

what I get is the following output.

{{"Student ID"}, {2.01451*10^9}, {2.01351*10^9}, {2.01351*10^9}}

However, I want to get the following output.

{{"Student ID"}, {2014512060}, {2013512024}, {2013512028}}

Question. How can I get the exact student numbers?

Answer 1

Get data

x = Import["C:\\Book1.xlsx", {"Sheets", "Sheet1"}]

Recover the integers for instance like this

y = Floor /@ x[[1]] // Flatten

(* Out[202]= {Floor["Student ID"], 2014512060, 2013512024, 2013512028} *)

Just the data are then

z = Drop[y,1]

(* Out[203]= {2014512060, 2013512024, 2013512028} *)

EDIT #1

Taking up Yves Klett's remark I entered in Excel below the IDs the number 4,99999999999999 typing it in from the Keyboard.

The import gives

x1 = {{{"Student ID"}, {2.01451*10^9}, {2.01351*10^9}, {2.01351*10^9}, {5.}}}

Extracting the number in question

x2 = x1[[1, -1, 1]]

(* Out[218]= 5. *)

It looks at first sight that the decimal positions have been lost. But this is not the case:

{Floor[#], Round[#]} &@x[[1, -1, 1]]

(* Out[216]= {4, 5} *)

Floor[] shows that they might be still there. And FullForm[] shows them explicitly:

FullForm[x2]

(* Out[222]//FullForm= \!\(\*
TagBox[
StyleBox["4.99999999999999`",
ShowSpecialCharacters->False,
ShowStringCharacters->True,
NumberMarks->True],
FullForm]\) *)

Remark: I could not persuade N[] to exhibit the decimal places but I'm sure there are better methods than FullForm.

Answer 2

A potential workaound is to use CSV as an intermediate format.

Some observations:

Excel does not internally distinguish an integer from a "real" integer. If you key in "1.00" it drops the decimal and stores "1"

Excel does not store integers with leading zeros per se, you can however apply a display format that specifies a fixed width left padded with zeros.

The largest integer excel can store is 999999999999999 (15 digits), but the default "general" format displays numbers larger then 11 digits in scientific notation.

If you export excel as csv what you get in the csv is the formatted number as you see displayed in the spreadsheet, that is by default numbers larger than 11 digits are rounded and converted to scientific notation, ie:

     99999999999->99999999999
    999999999999->1E12   (no decimal)
    999999900000->1E12
    999999000000->9.99999E11

(you can get all 15 digits exported to CSV by fixing the display format in excel)

Numbers formatted to have lead zeros go in the csv with lead zeros.

Importing the csv gives you want you want (integers are integers), except leading zeros are dropped.

It appears to be a feature of the "xls" import filter to convert all numbers to reals, and for the record the largest integer you evidently can import w/o loosing precision is 999999. The xls import doesn't preserve at all any effect of excel display formatting.