LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Programming (https://www.linuxquestions.org/questions/programming-9/)
-   -   create multi-tab ods from tab delimited files (https://www.linuxquestions.org/questions/programming-9/create-multi-tab-ods-from-tab-delimited-files-4175591129/)

schneidz 10-10-2016 11:07 AM
create multi-tab ods from tab delimited files
 
[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 (Post 5616154)
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 (Post 5616160)
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 (Post 5616178)
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 (Post 5616190)
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.


All times are GMT -5. The time now is 04:08 PM.