1
$\begingroup$

Is it possible to import data from Excel file so that imported values keeps their original format? I mean if cell formatted in Excel as text import it as String, if cell is integer, than import it as integer. If it is not possible I would like be able at least force to import columns in specific format. For example columns 1,2 I import as integers and 3,4 as strings.

In my case the column with "ids" which is generally the string but could be also a string of digits interpreted by Mathematica as real number in last case. While I want "ids" always as string.

$\endgroup$
3
  • $\begingroup$ I think mathematica does not know how excel had cells formatted, so there is no way to do this automatically. You can of course apply ToString to the desired column. $\endgroup$
    – george2079
    Nov 4, 2016 at 19:24
  • $\begingroup$ If I apply ToString there is an extra point at the end of the string because Mathematica interpreted the value as real number. And I cannot apply Floor to the column because the are also string values. So I should iterate trough all imported values. $\endgroup$ Nov 4, 2016 at 20:01
  • $\begingroup$ Try the documentation on SemanticImport $\endgroup$ Nov 5, 2016 at 0:36

1 Answer 1

3
$\begingroup$

One way is to save the Excel worksheet as "XML Table" and then Import this table in Mathematica as "XML" with option "IncludeNamespaces" -> "Unparsed":

XMLData = Import["test.xml", "IncludeNamespaces" -> "Unparsed"]

The XMLData contains all the information about formatting of the worksheet. It can be analyzed and modified inside Mathematica, and further Exported as Excel-compatible XML file:

Export["test-from MMa.xml", XMLData]

The exported file is recognized by Excel as its native format.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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