Como baixar dados em planilha e arrumar para arquivo de texto

Suponha que você tenha feito um script que precisa de dados de entrada disponibilizados por um site. O problema é que esse site deixa disponível para download um arquivo de planilha Excel (xls), pdf ou documento de texto Word (doc), e ainda por cima compactado. Esses não são bons formatos para guardar muitos dados, já que são difíceis de manipular e ainda gravam um monte de informações a respeito de formatação (muitas vezes incompatíveis dependendo do sistema operacional ou versão do leitor), inúteis para quem vai trabalhar só com os valores e/ou texto puro (popularmente chamado de “ASCII”).

Já passou por isso? Então seus problemas acabaram! Chegou o novo script “baixa_arruma_dados_tabajara.sh”… tá, esse script pode não resolver seus problemas mas já é um começo para automatizar a tarefa. Para resolver esse problema foram criadas duas rotinas (uma em shell script e outra em R):

Atualização: dois scripts que abrem diretamente um arquivo XLSX para edição em python e em R estão disponíveis nos posts Baixando dados de tabelas e Como abrir planilhas Excel e arquivos de coluna fixa no R (respectivamente).

– Script “baixa_arruma_dados.sh”

Tem a função de baixar o arquivo XLS de um link, converter para csv e selecionar linhas que interessam. Como exemplo, está um arquivo do site do Operador Nacional do Sistema Elétrico (ONS) – não garanto que estará sempre lá esse arquivo, mas lá tem algumas linhas com valores médios, e são essas que interessam. O nome desse arquivo deve entrar como argumento. O script também chama a segunda rotina (veja mais abaixo) e depois remove os arquivos temporários.

#!/bin/bash
# Script para baixar XLS, converter para ASCII e extrair dados
# Ficar atento para possíveis alterações na formatação!

# Nomes de diretório e arquivo
DIR=$(readlink -f -- $0 | awk -F "/" 'sub(FS $NF,x)')/dados
file=$1
filename=$(echo $file | cut -d'.' -f1)
ext=$(echo $file | cut -d'.' -f2)
ano=$(echo $filename | cut -d'_' -f4)

#### FUNÇÕES ####

baixa_converte(){
	# Baixar arquivo XLS do site ONS
	wget -nv -P $DIR http://agentes.ons.org.br/download/operacao/hidrologia/$file
	# Converter XLS -> CSV (converter terminação de linha DOS para UNIX também)
	if [ "$ext" == "xlsx" ]; then
		xlsx2csv $DIR/$file $DIR/$filename.txt
	else
		xls2csv -q 0 $DIR/$file > $DIR/$filename.txt
	fi
	dos2unix $DIR/$filename.txt
}

extrai_tabela(){
	# Gravar arquivo temporário com linhas contendo "(", que possuem nome e as médias mensais, e substituir/retirar o que for preciso
	more $DIR/$filename.txt | grep "(" | sed "s/^,med(1931 - $ano),//" | sed "s/ [(][^)]*[)]//g" | sed "s/REE //g" | sed "s/ SE//g" | sed "s/ /_/g" | sed "s/)//g" > $DIR/temp
	# Montar cabeçalho: selecionar somente os nomes das bacias, passar para caixa baixa (exceto primeiro caractere), substituir caracteres especiais e espaços por _
	cat $DIR/temp | sed 's/[0-9]*//g' | sed 's/,//g' | tr -d '.' | grep [A-Z] | sed 's/\b\([[:alpha:]]\)\([[:alpha:]]*\)\b/\u\1\L\2/g' | sed 'y/áÁàÀãÃâÂéÉêÊíÍóÓõÕôÔúÚçÇ/aAaAaAaAeEeEiIoOoOoOuUcC/' | sed 's/Manaus-Amapa/Amazonas/g' | sed ':a;N;$!ba;s/\n/,/g' | sed "s/_//g" > $DIR/cabecalho.csv
	# Montar médias: pegar somente linhas e colunas com as médias
	cat $DIR/temp | grep '^[0-9]' | awk -F',' '{print $1 "," $3 "," $5 "," $7 "," $9 "," $11 "," $13 "," $15 "," $17 "," $19 "," $21 "," $23}' > $DIR/medias.csv
	# Inverter linhas e colunas, apagar espaços em branco e gravar arquivo final
	Rscript $(readlink -f -- $0 | awk -F "/" 'sub(FS $NF,x)')/transpose.R $DIR/medias.csv $DIR/mediast.csv
	# Juntar cabeçalho e medias
	cat $DIR/cabecalho.csv $DIR/mediast.csv > $DIR/MLT_Ree.csv
}

##### PROGRAMA #####

# Baixar arquivo do ONS e converter para txt
baixa_converte

# Acertos e extrair tabela com valores
extrai_tabela

# Apagar arquivos temporários
rm -f $DIR/$file $DIR/$filename.txt $DIR/temp $DIR/cabecalho.csv $DIR/medias.csv $DIR/mediast.csv

O comando dos2unix serve converter a quebra de linha do formato DOS para o UNIX. Para conversão dos formatos da Microsoft, foram utilizados os programas xls2csv e xlsx2csv, instalados através de “sudo apt install catdoc xlsx2csv” (pra trabalhar com xls e xlsx, respectivamente). Outras opções são usar o libreoffice (1ª linha) ou o gnumeric (2ª linha):

libreoffice --headless --convert-to csv $DIR/$file --outdir $DIR
ssconvert $DIR/$file -O quoting-mode=never $DIR/$filename.txt

O comando do libreoffice só funciona corretamente se não tiver nenhuma janela do Office aberta.

– Script transpose.R

A transposição de uma matriz faz com que as linhas sejam transformadas em colunas (e vice-versa). A linguagem R é muito mais elegante para transpor uma matriz do que o shell script (procure transposição de matrizes em linguagem C para ter uma ideia). O script a seguir recebe como argumento os nomes dos arquivos de entrada de dados e onde serão gravados os valores. Além de ler o arquivo csv, guardar os valores em uma matriz e transformá-la na matriz transposta, ele seleciona somente algumas linhas que interessam – no caso, as que tem o valor médio.

# Script para ler arquivo CSV, trocar linhas por colunas e apagar o que não interessa
# Recebe variáveis
args <- commandArgs(trailingOnly = TRUE)
file_in = args[1]
file_out = args[2]

# Ler arquivo
matriz = read.csv(file_in, header=FALSE, sep=",")
# Transposição de matriz
transposta=t(matriz)
# Copiar somente as linhas com valores médios
temp=transposta[c(1,2,4,6,8,10,12,14,16,18,20,22,24),]
dir = paste(file_out, sep = '')
write.table(temp, dir, quote = FALSE, row.names = FALSE, col.names = FALSE, sep = ',')

Uma outra opção, para o caso de ter opções que exijam a interação do usuário através de menu HTML, pode ser vista no post sobre Python. Veja mais opções no outro post com Exemplos de scripts para reorganizar dados. Caso queira extrair dados de um arquivo PDF, clique no link para ver como fazer isso via shell script.

(Extra) Script para baixar arquivo mais recente

Uma dica: caso esse arquivo a ser baixado seja o mais recente dentro de uma sequência de diretórios, segue um script em PHP para encontrar e baixas esse arquivo – como exemplo, segue novamente o site do ONS:

#!/usr/bin/php
<?php

$base_url = "http://www.ons.com.br";
//$html = file_get_contents($base_url."/publicacao/PrevisaoVazoes/EntradaSaida/".Date('Y'));
$html = file_get_contents($base_url."/publicacao/PrevisaoVazoes/EntradaSaida/");

//Pegar último diretório do ano
if(preg_match_all('/<a\s+href=["\']([^"\']+)["\']/i', $html, $links_ano, PREG_PATTERN_ORDER))
    $all_hrefs = array_unique($links_ano[1]);

$html = file_get_contents($base_url.end($all_hrefs));

//Pegar último diretório do mês
if(preg_match_all('/<a\s+href=["\']([^"\']+)["\']/i', $html, $links, PREG_PATTERN_ORDER))
    $all_hrefs = array_unique($links[1]);

$html = file_get_contents($base_url.end($all_hrefs));

//Pegar último arquivo
if(preg_match_all('/<a\s+href=["\']([^"\']+)["\']/i', $html, $links_download, PREG_PATTERN_ORDER))
    $all_hrefs = array_unique($links_download[1]);

$data_link = $base_url.end($all_hrefs);
exec("wget $data_link");

?>

Caso você seja o programador da página, existe uma forma bem fácil de criar um botão para o usuário exportar uma tabela HTML para um arquivo CSV a ser baixado diretamente para o computador do usuário, sem tem que gerar arquivo no servidor, somente usando javascript/jQuery – clique no link para saber como usar.

Posts relacionados:

Como extrair dados de arquivo XML Exemplos de scripts para reorganizar dados Conda e ambientes virtuais Terminal de texto