create multi-tab ods from tab delimited files

schneidz · 10-10-2016, 11:07 AM

[aix]
i am in the midst of editing some ms-excel macros (yuk !) and i just realized that the latest version of microsoft excel now supports open-document-spreadsheets.

does anyone have any pointers on how to generate ods's from the command-line ?

firstfire · 10-10-2016, 12:12 PM

Hi!

You can manipulate ODS using python, take a look at pyexcel-ods3 for instance. For ODF there is odfpy.

Turbocapitalist · 10-10-2016, 12:21 PM

Interesting. I was under the impression that M$ only supported it in name only and that it would still break a lot of documents.

Be that as it may, you could just generate tab-delimited text files and name them .csv and import them one spreadsheet tab at a time.

Or you might look at something like this:
http://search.cpan.org/dist/OpenOffi...ODoc/Intro.pod
Perl is pretty quick to work with and to get something usable.

schneidz · 10-10-2016, 01:24 PM

Quote:

Originally Posted by firstfire

Hi!

You can manipulate ODS using python, take a look at pyexcel-ods3 for instance. For ODF there is odfpy.

pyexcel-ods3 looks good but unfortunately my aix installation doesnt have python installed.

Quote:

Originally Posted by Turbocapitalist

Interesting. I was under the impression that M$ only supported it in name only and that it would still break a lot of documents.

Be that as it may, you could just generate tab-delimited text files and name them .csv and import them one spreadsheet tab at a time.

Or you might look at something like this:
http://search.cpan.org/dist/OpenOffi...ODoc/Intro.pod
Perl is pretty quick to work with and to get something usable.

i do have perl but not use OpenOffice::OODoc;. still reading...

firstfire · 10-10-2016, 01:58 PM

Quote:

Originally Posted by schneidz

pyexcel-ods3 looks good but unfortunately my aix installation doesnt have python installed.
i do have perl but not use OpenOffice::OODoc;. still reading...

Then you may unzip some .ods file and hack content.xml (see description of file format here and in references), then compress again. An XML parser / XSLT interpreter would help.

schneidz · 07-24-2017, 11:06 AM

Quote:

Originally Posted by firstfire

Then you may unzip some .ods file and hack content.xml (see description of file format here and in references), then compress again. An XML parser / XSLT interpreter would help.

hi, this is what i came up with:

Code:

mkdir tmp-schneidz
for ods in *.tab
do
 lines=`grep -c . $ods`
 echo -e '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>\015\012' > tmp-schneidz/content.xml
 echo -e '<office:document-content xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula"><office:font-face-decls><style:font-face style:name="Calibri" svg:font-family="Calibri"/></office:font-face-decls><office:automatic-styles><style:style style:name="ce1" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N0"/><style:style style:name="ce2" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N30"><style:table-cell-properties fo:background-color="#B8CCE4"/><style:text-properties fo:font-weight="bold" style:font-weight-asian="bold" style:font-weight-complex="bold"/></style:style><style:style style:name="ce3" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N30"><style:text-properties fo:font-weight="bold" style:font-weight-asian="bold" style:font-weight-complex="bold"/></style:style><style:style style:name="ce4" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N0"><style:text-properties fo:font-weight="bold" style:font-weight-asian="bold" style:font-weight-complex="bold"/></style:style><style:style style:name="ce5" style:family="table-cell" style:parent-style-name="Default" style:data-style-name="N30"/><style:style style:name="co1" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.29645833333333cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co2" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="2.19604166666667cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co3" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="2.06375cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co4" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.349375cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co5" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="3.28083333333333cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co6" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="2.51354166666667cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co7" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="3.96875cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co8" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.37583333333333cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co9" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.42875cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co10" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.53458333333333cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co11" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="0.79375cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co12" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="0.978958333333333cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co13" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.508125cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co14" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.40229166666667cm" style:use-optimal-column-width="true"/></style:style><style:style style:name="co15" style:family="table-column"><style:table-column-properties fo:break-before="auto" style:column-width="1.69333333333333cm"/></style:style><style:style style:name="ro1" style:family="table-row"><style:table-row-properties style:row-height="15pt" style:use-optimal-row-height="true" fo:break-before="auto"/></style:style><style:style style:name="ta1" style:family="table" style:master-page-name="mp1"><style:table-properties table:display="true" style:writing-mode="lr-tb"/></style:style></office:automatic-styles><office:body><office:spreadsheet><table:calculation-settings table:case-sensitive="false" table:search-criteria-must-apply-to-whole-cell="false"/><table:table table:name="shh-ods-parser" table:style-name="ta1"><table:table-column table:style-name="co1" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co2" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co3" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co4" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co5" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co6" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co7" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co8" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co9" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co10" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co11" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co12" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co13" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co14" table:default-cell-style-name="ce1"/><table:table-column table:style-name="co15" table:number-columns-repeated="16370" table:default-cell-style-name="ce1"/><table:table-row table:style-name="ro1"><table:table-cell office:value-type="string" table:style-name="ce2"><table:cell-range-source table:name="Connection1" table:last-column-spanned="14" table:last-row-spanned="11" xlink:href="C:\stuff\aug\c2\sr-9992-1543\sr-a9992-1543-cycle.xls"/><text:p>PREFIX</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>hello</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>world</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>SEGNO</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>l33t</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>h4x0rz</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>FILEID</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>chun-li</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>STATUS</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>REASON</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>akuma</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce2"><text:p>TYPE</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce3"><text:p>SOURCE</text:p></table:table-cell><table:table-cell office:value-type="string" table:style-name="ce3"><text:p>CTL-NO</text:p></table:table-cell><table:table-cell table:number-columns-repeated="16370" table:style-name="ce4"/></table:table-row>' > tmp-schneidz/content.schneidz
 awk -F '\t' '{print "<table:table-row table:style-name=\"ro1\"> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$1"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$2"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$3"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$4"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$5"<text:s text:c=\"3\"/></text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$6"<text:s text:c=\"8\"/></text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$7"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$8"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$9"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$10"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$11"<text:s text:c=\"3\"/></text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$12"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$13"</text:p></table:table-cell> \
                      <table:table-cell office:value-type=\"string\" table:style-name=\"ce5\"><text:p>"$14"</text:p></table:table-cell> \
                      <table:table-cell table:number-columns-repeated=\"16370\"/></table:table-row>"}' $ods >> tmp-schneidz/content.schneidz
 echo '<table:table-row table:number-rows-repeated="'  >> tmp-schneidz/content.schneidz
 expr 1048575 - $lines >> tmp-schneidz/content.schneidz
 echo '" table:style-name="ro1"><table:table-cell table:number-columns-repeated="16384"/></table:table-row></table:table></office:spreadsheet></office:body></office:document-content>' >> tmp-schneidz/content.schneidz
 cat tmp-schneidz/content.schneidz | tr -d '\n' >> tmp-schneidz/content.xml
 cp /etc/ods/manifest.xml /etc/ods/meta.xml /etc/ods/mimetype /etc/ods/styles.xml tmp-schneidz
 cd tmp-schneidz
 rm content.schneidz
 pkzip $ods; mv \"$ods\".zip ../`echo $ods | sed s/.tab/.ods/`
 cd ..
 rm -rf $ods tmp-schneidz/
done

the cr-lf's are very sensitive and would result in a workbook is corrupted error in ms-excel (havent tried it in open-office). still trying to figure out how to combine certain worksheets into the same spreadsheet.