Extract data from Excel table spreadsheet table on Linux

Question

I have an Excel spreadsheet based on a pivot table that is periodically updated (monthly) and uploaded to my server (generated by a group that is very hesitant about changing anything in the output). I would like to be able to write a script that I could execute through a cron job to process and load raw data from a pivot table into my database.

However, I can’t understand how to get the basic data without manually navigating to the windows, open the file in excel, double-click the totals cell, get a new sheet with all the raw data that went into filling this cell and save this sheet as csv, which I can upload to your database via some language (in my case python). It appears that they should be scriptable in order to extract the underlying data.

I have only Linux machines (running Windows / Office in VM, but I would prefer a solution that is not related to windows). I am familiar with tools like xls2csv (which doesn't have access to raw data) and with tools like python-unoconv to edit openoffice documents from python. However, even manually using openoffice, I see no way to get the baseline data.

EDIT: After spending several hours not making any progress (before posting this post), I don’t start doing it, converting it to ODS via unoconv and probably can use something with python-odf extract the last sheet (Called 'DPCache).

So, now the problem is to get the sheet from ODS converted to CSV; which should not be too complicated for me to understand (although help is much appreciated).

Answer 1

Have you tried xlrd ? See Also tutorial available from python-excel website .

It's simple:

>>> import xlrd
>>> book = xlrd.open_workbook('pivot_table_demo.xls')
>>> sheet = book.sheet_by_name('Summary')
>>> for row_index in xrange(sheet.nrows):
...     print sheet.row_values(row_index)
...
[u'Sum of sales', u'qtr', '', '', '', '']
[u'person', 1.0, 2.0, 3.0, 4.0, u'Grand Total']
[u'dick', 100.0, 99.0, 95.0, 90.0, 384.0]
[u'harriet', 100.0, 110.0, 121.0, 133.1, 464.1]
[u'tom', 100.0, 101.0, 102.0, 103.0, 406.0]
[u'Grand Total', 300.0, 310.0, 318.0, 326.1, 1254.1]
>>>

Answer 2

. , xlsx / xml. .

• /pivotCache/pivotCacheDefinition 1.xml
• /pivotCache/pivotCacheRecords 1.xml

pivotCacheRecords1.xml, , , , pivotCacheRecords1.xml <x> pivotCacheDefinition1.xml <x>, xml .

pivotCacheDefinition1.xml

    <?xml version="1.0" encoding="UTF-8"?>
<pivotCacheDefinition xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" r:id="rId1" refreshedBy="ADNLatam" refreshedDate="42972.64919178241" createdVersion="5" refreshedVersion="6" recordCount="1923161">
   <cacheSource type="external" connectionId="1" />
   <cacheFields count="26">
      <cacheField name="C - Cadenas" numFmtId="0" sqlType="-9">
         <sharedItems count="3">
            <s v="superA" />
            <s v="superB" />
            <s v="superC" u="1" />
         </sharedItems>
      </cacheField>
      <cacheField name="C - Locales" numFmtId="0" sqlType="-9"><span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>
         <sharedItems count="80">
            <s v="Itaugua" />
            <s v="Denis Roa" />
            <s v="Total" />
            <s v="Los Laureles" />
            <s v="CDE" />
            <s v="S6 Fdo." />
            <s v="Central" u="1" />
            <s v="Unicompra" u="1" />
            <s v="San Lorenzo Centro" u="1" />
         </sharedItems>
      </cacheField>
   </cacheFields>
</pivotCacheDefinition>
</xml>

pivotCacheRecords1.xml

<pivotCacheRecords
xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" count="246209">
<r>
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <s v="PAÐAL &quot;PAMPERS&quot; BABYSAN REGULAR GDE 9UN"/> #Z - Sku / Descripcion
    <s v="07501006720341"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <n v="1"/> 
    <n v="11990"/> 
    <n v="2.3199999999999998"/> 
    <n v="10900"/> 
    <n v="11990"/> 
    <n v="1"/> 
    <d v="2012-02-03T00:00:00"/> 
    <x v="0"/> 
    <x v="0"/> 
    <n v="3"/> 
    <n v="6"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
    <x v="0"/> 
</r>

, <x> CacheRecords1 <s> CacheDefinition1, , , dict .

      definitions = '/tmp/scantrack_tmp/xl/pivotCache/pivotCacheDefinition1.xml'
      defdict = {}
      columnas = []
      e = xml.etree.ElementTree.parse(definitions).getroot()
      for fields in e.findall('{http://schemas.openxmlformats.org/spreadsheetml/2006/main}cacheFields'):
          for cidx, field in enumerate(fields.getchildren()):
              columna = field.attrib.get('name')
              defdict[cidx] = []
              columnas.append(columna)
              for value in field.getchildren()[0].getchildren():
                  tagname = value.tag
                  defdict[cidx].append(value.attrib.get('v', 0))

.

{
  0: ['supera', 'superb', u'superc'],
  1: ['Terminal',
     'CDE',
     'Brasilia',
     ]
  3: ['PANTENE', 'DOVE']
  ...
}

, , - CacheRecords1 defdict, <x>

  dfdata = []


  bdata = '/tmp/scantrack_tmp/xl/pivotCache/pivotCacheRecords1.xml'

  for event, elem in xml.etree.ElementTree.iterparse(bdata, events=('start', 'end')):
    if elem.tag == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}r' and event == 'start':
       tmpdata = []
       for cidx, valueobj in enumerate(elem.getchildren()):
           tagname = valueobj.tag
           vattrib = valueobj.attrib.get('v')
           rdata = vattrib
           if tagname == '{http://schemas.openxmlformats.org/spreadsheetml/2006/main}x':
                try:
                  rdata = defdict[cidx][int(vattrib)]
                except:
                  logging.error('this it not should happen index cidx = {} vattrib = {} defaultidcts = {} tmpdata for the time = {} xml raw {}'.format(
                                                                                                                                                cidx, vattrib, defdict, tmpdata,
                                                                                                                                                xml.etree.ElementTree.tostring(elem, encoding='utf8', method='xml')
                                                                                                                                                ))
           tmpdata.append(rdata)
       if tmpdata:
           dfdata.append(tmpdata)
       elem.clear()

dfdata dataframe

df = pd.DataFrame(dfdata).

- , , .

!!!