Automatize o fluxo de trabalho da planilha (Excel / CSV)

Question

Estou procurando uma ferramenta ou processo para ajudar a automatizar tarefas de fluxo de trabalho repetitivas que exigem que um arquivo Excel ou CSV esteja em um formato específico.

Por exemplo, trabalhamos com fornecedores que nos enviam uma lista de clientes. Os dados da planilha que recebemos são os mesmos, mas podem ter uma forma ligeiramente diferente. Por exemplo, podemos receber um arquivo com estas colunas:

[ID][FName][LName][Birthday]

No entanto, vamos supor que nosso sistema precisa que ele tenha a seguinte forma:

[ID][FirstName][LastName][Birthdate]

Além disso, os dados podem estar em um formato ligeiramente diferente do que o nosso sistema excede. Por exemplo, suponha que a coluna de datas de nascimento precisa estar em um formato específico (MM / DD / AAAA), onde o fornecedor pode fornecê-lo como (AAAA-MM-DD).

Supondo que o arquivo que recebemos do fornecedor sempre é enviado no mesmo formato, gostaríamos de transformar esses arquivos rapidamente para serem compatíveis com nosso sistema.

Na minha opinião, idealmente, seria um aplicativo com os seguintes recursos:

• Capacidade de trabalhar com arquivos Excel ou CSV
• Definir como renomear colunas
• Defina o formato de dados para cada coluna
• Limpar dados incorretos (às vezes, os clientes têm a string # N / A em um campo, que deve ser tratado como em branco em nosso sistema)
• Saída como CSV
• Mesclar linhas por colunas correspondentes (às vezes, são fornecidos vários arquivos que precisam ser mesclados por ID)
• Uma interface GUI seria interessante para definir as regras, depois executá-la usando uma CLI provavelmente seria útil para automação / execuções futuras
• A capacidade de salvar essas configurações para que possamos executá-las rapidamente no futuro, carregando novos dados com as configurações definidas anteriormente.

Existe uma ferramenta ou fluxo de trabalho que faria isso com muito pouco atrito? Podemos desenvolver algo internamente, se necessário, mas se já existe uma ferramenta que facilite isso, seria ideal.

    

Answer 1

Sua pergunta é como uma descrição do produto do Power Query. Este é um suplemento gratuito da Microsoft que funciona com o Excel 2010 e 2013.

Obtenha aqui: link

Você pode abrir um arquivo CSV, renomear colunas, substituir valores, definir formatos de dados, etc. Toda atividade é gravada e pode ser editada. A consulta pode ser salva e repetida a qualquer momento. A saída pode ser colocada em uma planilha do Excel e pode ser salva como CSV.

Editar: o Power Query agora está disponível para todas as edições do Office 2013. Citando os requisitos do sistema na página de download

The following Office versions are supported:

• Microsoft Office 2010 Professional Plus with Software Assurance
• Microsoft Office 2013:
• Power Query Premium: All Power Query features available for: Professional Plus, Office 365 ProPlus or Excel 2013 Standalone
• Power Query Public: Available for all other Office 2013 Desktop SKUs. Includes all Power Query features, except the following ones: Corporate Power BI Data Catalog, Azure-based data sources, Active Directory, HDFS, SharePoint Lists, Oracle, DB2, MySQL, PostgreSQL, Sybase, Teradata, Exchange, Dynamics CRM, SAP BusinessObjects, Salesforce.

Microsoft Power Query for Excel requires Internet Explorer 9 or greater.