1

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.

Alexey Popkov
  • 61,809
  • 7
  • 149
  • 368
Филипп Цветков
  • 3,461
  • 1
  • 15
  • 34
  • 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. – george2079 Nov 04 '16 at 19:24
  • 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. – Филипп Цветков Nov 04 '16 at 20:01
  • Try the documentation on SemanticImport – Mike Colacino Nov 05 '16 at 00:36

1 Answers1

3

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.

Community
  • 1
Alexey Popkov
  • 61,809
  • 7
  • 149
  • 368