From Excel to XML

[Cicorione]

Hi @pgundlach

I am trying to wrap my head around this issue and I am not doing a great figure.

I have a stupid basic excel table:

month,day,weekday
January,1,Wednesday
January,2,Thursday
January,3,Friday
January,4,Saturday
January,5,Sunday
January,6,Monday
[...]

I want to convert it in:

<data>
	<month name="january">
		<day weekday="Wednesday"/>1</day>
	</month>
       [...]
</data>

I tried with schema (I am not sure I did it properly):

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="data">
        <xs:complexType>
            <xs:sequence>
               <xs:element name="month" minOccurs="0" maxOccurs="unbounded">
               <xs:attribute name="month" type="xs:string"/>
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="day" type="xs:string"/>
                            <xs:attribute name="weekday" type="xs:string"/>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

However Excel XML mapping is not accepting XS schema but only XSD, and for what understood it does not accept attributes.

What are the workarounds to convert and Excel document into a viable XML file?

Thanks.

[pgundlach]

How are you converting the Excel sheet to XML? I have written a small XLSX to XML converter at https://github.com/speedata/examples/blob/master/technical/xlsxreader/xlsx2xml.lua - but I guess you have seen this?

[Cicorione]

Yes I did, however without mapping the XML the default one is just awful... Well I am going to figure out to export a decent XML though...

[pgundlach]

Can you post an excel file here?

[Cicorione]

Hi Patrick it is a very dumb table for testing purposes:

Month,Day,Weekday
January,1,Wednesday
January,2,Thursday
January,3,Friday
January,4,Saturday
January,5,Sunday
January,6,Monday
January,7,Tuesday
January,8,Wednesday
January,9,Thursday
January,10,Friday
January,11,Saturday
January,12,Sunday
January,13,Monday
January,14,Tuesday
January,15,Wednesday
January,16,Thursday
January,17,Friday
January,18,Saturday
January,19,Sunday
January,20,Monday
January,21,Tuesday
January,22,Wednesday
January,23,Thursday
January,24,Friday
January,25,Saturday
January,26,Sunday
January,27,Monday
January,28,Tuesday
January,29,Wednesday
January,30,Thursday
January,31,Friday
February,1,Saturday
February,2,Sunday
February,3,Monday
February,4,Tuesday
February,5,Wednesday
February,6,Thursday
February,7,Friday
February,8,Saturday
February,9,Sunday
February,10,Monday
February,11,Tuesday
February,12,Wednesday
February,13,Thursday
February,14,Friday
February,15,Saturday
February,16,Sunday
February,17,Monday
February,18,Tuesday
February,19,Wednesday
February,20,Thursday
February,21,Friday
February,22,Saturday
February,23,Sunday
February,24,Monday
February,25,Tuesday
February,26,Wednesday
February,27,Thursday
February,28,Friday

calendar-test.xlsx

I found a lot of very ancient threads on how to map a CSV table into an XML file, I hope to come out with a viable workflow.

[pgundlach]

The output is not very beautiful, but perhaps I can show you one way to use the data.

0. copy the xlsx file into your local folder

1. create a file filter.lua:

xlsx = require("xlsx")
xml = require("xml")

spreadsheet, err = xlsx.open("calendar-test.xlsx")

if not spreadsheet then
    print(err)
    os.exit(-1)
end

-- get the number of spreadsheets with the # operator (#spreadsheet)
ws = spreadsheet[1]
if not ws then
    os.exit(-1)
end


local data = {}
local row, cell

data._name = "data"

for y=ws.minrow,ws.maxrow do
    row = {}
    row._name = "row"
    for x=ws.mincol,ws.maxcol do
        cell = {}
        cell._name = "cell"
        -- ws() gets the contents of the worksheet at position x,y
        -- where the top left is at 1,1
        cell[1] = ws(x,y)
        row[#row + 1] = cell
    end
    data[#data + 1] = row
end

ok, err = xml.encode_table(data)
if not ok then
    print(err)
    os.exit(-1)
end

(from the linked example)

2. Then create this layout file:

<Layout xmlns="urn:speedata.de:2009/publisher/en"
    xmlns:sd="urn:speedata:2009/publisher/functions/en">

    <Record element="data">
        <PlaceObject>
            <Table>
                <ForAll select="row">
                    <Tr>
                        <ForAll select="cell">
                            <Td><Paragraph><Value select="." /></Paragraph></Td>
                        </ForAll>
                    </Tr>
                </ForAll>
            </Table>
        </PlaceObject>
    </Record>
</Layout>

4. and run

   sp --filter filter.lua

and voilà, the most gorgeous document that exists:

(seriously, you need to adapt everything to your needs)

[Cicorione]

Thank you Patrick.
The lua filter you provided doesn't map the columns, does it?

In this case, but also in the future, what I need is to convert these Excel tables into CSV tables and then mapping the fields and convert it into a XML data; but it looks like you don't need to convert into CSV at all.

For instance my goal is to change these records:

Data merging in InDesign is pretty basic but I had to split the table in right and left page; with Publisher I don't need to split the data for left and right however in XML data is structured differently that is why I came out with:

<data>
  <month name="January">
    <day weekday="Wednesday"/>1</day>
    <day weekday="Thursday"/>2</day>
    [...]
  </month>
  [...]
</data>

I wonder if it is better pursuing the path XLSX -> CSV -> XSLT -> XML or LUA Filter -> Layout.xml, in both case does exist a boilerplate that I can use to understand how to convert the data.

Thanks.

[pgundlach]

I'd go for Lua Filter -> XSLT -> data.xml, this needs XSLT knowledge.

You can also use Lua Filter -> data.xml directly, but this requires Lua knowledge

Try this for example:

xlsx = require("xlsx")
xml = require("xml")

spreadsheet, err = xlsx.open("calendar-test.xlsx")

if not spreadsheet then
    print(err)
    os.exit(-1)
end

-- get the number of spreadsheets with the # operator (#spreadsheet)
ws = spreadsheet[1]
if not ws then
    os.exit(-1)
end


local data = {}
local row, cell

data._name = "data"


prev_month = ""
for y = ws.minrow + 1, ws.maxrow do
    local month = ws(1, y)
    if prev_month ~= month then
        row = {}
        row._name = "month"
        row.name = month
        data[#data + 1] = row
    end
    cell = {}
    cell._name = "day"
    cell[1] = ws(2, y)
    cell.weekday = ws(3, y)
    row[#row + 1] = cell

    prev_month = month
end


ok, err = xml.encode_table(data)
if not ok then
    print(err)
    os.exit(-1)
end

This should result in a data.xml which looks like your example.

<data>
    <month name="January">
        <day weekday="Wednesday">1</day>
        <day weekday="Thursday">2</day>
        <day weekday="Friday">3</day>
        <day weekday="Saturday">4</day>
        <day weekday="Sunday">5</day>
        <day weekday="Monday">6</day>
        <day weekday="Tuesday">7</day>

[Cicorione]

Thank you Patrick, I'll look into XSLT better...