0

I want to use an XML file to facilitate data imports into an Oracle APEX application. The primary reason why I want to use XML is that some of the fields in the Excel spreadsheet could contain many commas which would plague a Comma-separated-value import. I was able to write a Python script using the xlrd and lxml libraries however this only works well if the scripts exist in a central location (server).

I was able to create an XML spreadsheet that could be used to convert Excel spreadsheets into an XML data file that could be used to import data into Oracle APEX. Unfortunately, I discovered that Microsoft Excel on the Macintosh does not appear to be able to export data as XML.

Is there any way that I can export the Excel data as an XML data file on the Macintosh?

2

If it's a .xlsx file like the Windows version uses, that's just a renamed .zip of XML and other content. For example, a spreadsheet with the following values in Sheet1:

enter image description here

If you rename the .xlsx to a .zip, you'll find the numeric data exposed in a file xl\worksheets\sheet1.xml in the following tree:

<worksheet>
  <sheetData>
    <row r="1" spans="1:2">
      <c r="A1">
        <v>1</v>
      </c>
      <c r="B1">
        <v>2</v>
      </c>
    </row>
    <row r="2" spans="1:2">
      <c r="A2" t="s">
        <v>0</v>
      </c>
      <c r="B2">
        <v>5</v>
      </c>
    </row>
  </sheetData>
</worksheet>

The string cells are stored in a shared string table xl\sharedStrings.xml in the following tree:

<sst>
  <si>
    <t>3,4</t>
  </si>
</sst>

I wouldn't want to write the code to piece all that back together, but it's certainly possible.

  • Bleh! Thanks for the reply! Oracle APEX wants the XML to have a tree in the following format: <rowset><row><COLUMN></COLUMN></row></rowset> where COLUMN is the actual name of the table column in the database. I'm almost better off using the Python script I wrote! – Chris Dix Apr 29 '11 at 22:59

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.