5
$\begingroup$

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?

$\endgroup$
  • $\begingroup$ Try in excel itself to change the column to be text instead of numerical. i.e. change the format of the column to text (in excel), save it, and try again the import to Mathematica. $\endgroup$ – Nasser Jan 13 '15 at 8:04
  • $\begingroup$ @Nasser I first did what you have mentioned but it did not work. $\endgroup$ – bkarpuz Jan 13 '15 at 8:07
  • $\begingroup$ This is caused by the frontend display truncating / displaying long (machine precision) numbers. You should be fine just Rounding or Rationalizeing your result. Related: mathematica.stackexchange.com/a/69470/131 $\endgroup$ – Yves Klett Jan 13 '15 at 8:19
4
$\begingroup$

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.

$\endgroup$
  • $\begingroup$ WouldRound be more robust than Floor (in case of ever so slightly underrepresented numbers)? $\endgroup$ – Yves Klett Jan 13 '15 at 8:25
  • $\begingroup$ @Yves Klett: I chose the brutal method Floor knowing that the source is integer numbers. But I don't understand your comment in brackets ... $\endgroup$ – Dr. Wolfgang Hintze Jan 13 '15 at 9:53
  • $\begingroup$ Not sure if this is likely to happen with Excel import, but I was wondering about somthing like 5 being represented as 4.99999999999` or similar... $\endgroup$ – Yves Klett Jan 13 '15 at 10:07
  • 1
    $\begingroup$ @Yves Klett: ok. BTW my extensive experience with Excel is that number formats is a feature "très special". For instance if there are leading zeroes which might very well appear in IDs. $\endgroup$ – Dr. Wolfgang Hintze Jan 13 '15 at 10:25
  • $\begingroup$ So, does Excel always store numbers in a floating-point format? I was just wondering if this could be partly a problem of the import converter. $\endgroup$ – Oleksandr R. Jan 13 '15 at 12:13
2
$\begingroup$

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.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.