Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

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.

share|improve this answer
    
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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