[SOLVED] Re: Problem cron job not converting excel files

[Raja Iskandar Shah <rajaiskandars gmail com>]

yes ! solved the problem

amended the cron job to treat ssconvert like a gui application by adding export DISPLAY=:0 && 

refer to the following amended cron job

30 18 11 * * export DISPLAY=:0 && ssconvert --import-type=Gnumeric_Excel:excel --export-type=Gnumeric_stf:stf_assistant -O 'sheet="2.3" separator=, format=raw quote=""' /opt/eids_etl/2.3.xls /opt/eids_etl/2_3_xls.csv
35 18 11 * * export DISPLAY=:0 && ssconvert --import-type=Gnumeric_OpenCalc:openoffice --export-type=Gnumeric_stf:stf_assistant -O 'sheet="2.3" separator=, format=raw quote=""' /opt/eids_etl/2.3.ods /opt/eids_etl/2_3_ods.csv

now both the .xls and .ods gets converted to .csv format, previously only .ods gets converted

got the tip from here:
http://ubuntuforums.org/archive/index.php/t-105250.html

On Sat, Dec 11, 2010 at 5:24 PM, Raja Iskandar Shah <rajaiskandars gmail com> wrote:

the file with the .sh extension is the output file that ssconvert creates.

the .sh is to execute a long list of etl jobs to import time-series xls files into a mysql database. this is then executed by cron on a monthly basis.

because the parameters for the etl job varies between years and months, i created a spreadsheet file to calculate these parameters, then early of the month, ssconvert will convert it into an executable .sh file. another cron job will then execute the .sh file for the etl jobs.

i am using gnumeric spreadsheet 1.10.1 on ubuntu 10.04 ( i have also been testing it out on ubuntu server 10.04 with gnumeric 1.10.1)

here is another file that you can test:
http://www.bnm.gov.my/files/publication/msb/2010/10/xls/2.3.xls

download the file, then open it using openoffice / gnumeric then save it as openoffice spreadsheet .ods

make a directory /opt/eids_etl and copy the file 2.3.xls and 2.3.ods to the folder /opt/eids_etl/

copy the following cron job script (which will execute ssconvert at 00 min 5.00pm 11th of month / 05 min 5.00pm 11th of month respectivley):
00 17 11 * * ssconvert --import-type=Gnumeric_Excel:excel --export-type=Gnumeric_stf:stf_assistant -O 'sheet="2.3" separator=, format=raw quote=""' /opt/eids_etl/2.3.xls /opt/eids_etl/2_3.csv
05 17 11 * * ssconvert --import-type=Gnumeric_OpenCalc:openoffice --export-type=Gnumeric_stf:stf_assistant -O 'sheet="2.3" separator=, format=raw quote=""' /opt/eids_etl/2.3.ods /opt/eids_etl/2_3_ods.csv

now go to a terminal and logon as root (in ubuntu it is sudo -i)

edit your cron jobs (in ubuntu it is crontab -e), if you selected to edit using vi then press the [i] key to insert mode

paste the copied cron script (right-click on the mouse then select paste)

if needed, edit the minutes / hour / day

save your cron job and exit (if you are using vi, then type :wq then press the [Enter] key)

check your cron job (if you are using ubuntu it is crontab -l)