Converter planilha para CSV usando o libreoffice

converter-para-csv.sh

#!/usr/bin/bash
#
# Converte um ou mais arquivos denominados INPUT_FILE para CSV.
#
# O primeiro arquivo informado na linha de comando eh o OUTPUT_FILE; todos os outros sao INPUT_FILE.
#
# Os arquivos INPUT_FILE devem estar num destes formatos: ODS, XLS ou XSLX.
#
# Se houver mais de um arquivo INPUT_FILE, estes serao combinados para formar um unico arquivo OUTPUT_FILE.
# 
# Como usar:
#
#     converter-para-csv.sh OUTPUT_FILE INPUT_FILE [INPUT_FILE ...]
#
# Autor: Fabio Lima
#

if [[ -n "$1" ]];
then
	if [[ -f "$1" ]];
	then
		echo "Output already exists.";
		exit 1;	
	fi;
	if [[ ! -d $(dirname "$1") ]];
	then
		echo "Invalid output file directory: '$(dirname "$1")'.";
		exit 1;	
	fi;
	if [[ ! "${1^^}" =~ \.CSV$ ]];
	then
		echo "Invalid output file extension: '${1##*.}'.";
		exit 1;	
	fi;
else
	echo "Output file is required.";
	exit 1;
fi;

if [[ -z "$2" ]];
then
	echo "Input file is required.";
	exit 1;
fi;

suffix="csv"
output_file=${1}
temporary_dir=$(mktemp --directory);
	
function detectar-cabecalho-csv {
	
	local file_csv="${1}";
	
	head -n 1000 "${file_csv}" \
	| awk 'BEGIN { FS=","; header=0; lines=0; columns=0; } NF > 0 && NF == columns {lines += 1} NF != columns && $NF {header = NR; columns = NF; lines = 0} END {if (lines > 0) {print header} else {print 1}}';
}

function converter-arquivo-individual-para-csv {

	local file="${1}"
	local type=${file##*.}
	local name=$(basename "$file" .$type)
		
	if [[ ! -f "$file" ]];
	then
		echo "Invalid input file: '${file}'.";
		exit 1;
	fi;

	if [[ ! "${type^^}" =~ ^(ODS|XSLX?)$ ]];
	then
		echo "Invalid input file type: '${type^^}'."
		exit 1;
	fi;
	
	echo "Convertendo para CSV: '${file}'";
	
	libreoffice --convert-to $suffix --outdir "$temporary_dir" "$file"; # &> /dev/null;
	
    local file_csv="${temporary_dir}/${name}.${suffix}";
	if [[ ! -f "${file_csv}" ]];
	then
		echo "Conversion to CSV failed: '${file}'."
		exit 1;
	fi;
	
	local header=$(detectar-cabecalho-csv "${file_csv}");
	local encoding=$(file --brief --mime-encoding "${file_csv}");
	
	tail -n +${header} "${file_csv}" \
	| iconv --silent -f "${encoding}" -t "utf-8" \
	>> "${file_csv}".tmp;
	
	mv "${file_csv}".tmp "${file_csv}";
	
	echo "CSV intermediario: '${file_csv}'";
}

function produzir-arquivo-unificado-csv {
			
	find "${temporary_dir}" -name "*.$suffix" | while read file;
	do
	
		if [[ ! -s "${output_file}" ]];
		then
			# if empty print only header line
			head -n 1 "${file}" > "${output_file}";
		fi;
		
		tail -n +2 "${file}" >> "${output_file}";
	done;
	
	echo "CSV final: '${output_file}'";
}

shift;
for file in "$@";
do
    converter-arquivo-individual-para-csv "${file}";
done

produzir-arquivo-unificado-csv;

executar.sh

#!/usr/bin/bash
#
# Executa os scripts de conversão de relatório.
#

verificar-dependencias.sh

processar-relatorios.sh Relatorios Resultados

processar-relatorios.sh

#!/usr/bin/bash
#
# Processa todos os relat\u00f3rios ODS contidos na pasta INPUT_DIR.
#
# Gera na pasta OUTPUT_DIR um arquivo unico com o nome neste formato: "processar-relatorios-YYYYMMDD_HHMM.csv".
# 
# Como usar:
#
#     processar-relatorios.sh [INPUT_DIR] [OUTPUT_DIR]
#
# Autor: Fabio Lima
#

input_dir=${1-$PWD}
output_dir=${2-$PWD}

suffix="csv"
version=$(date +"%Y%m%d_%H%M");
output_file="processar-relatorios-${version}.$suffix";

$(dirname "$0")/converter-para-csv.sh "${output_dir}/${output_file}" "${input_dir}"/*.[Oo][Dd][Ss];

verificar-dependencias.sh

#!/usr/bin/bash
#
# Verifica se as dependencias estao instaladas.
# 
# Instalar dependencias:
#
# 	sudo apt install libreoffice
# 	sudo apt install r-base r-cran-tidyverse r-cran-plotly r-cran-patchwork r-cran-rmarkdown r-cran-knitr
#   Rscript -e 'install.packages("readODS")' # nao disponivel via apt-get.
#
# Autor: Fabio Lima
#

which soffice &> /dev/null || (echo -e "The installation of LibreOffice Calc is required.\nsudo apt install libreoffice-calc"; exit 1;)
which R &> /dev/null || (echo -e "The installation of R is required.\nsudo apt install r-base"; exit 1;)