Criar arquivo Txt no Excel com campos de tamanho fixo – Planilha grátis
Neste post você verá como criar um arquivo txt no Excel com campos de tamanho fixo.
É comum na comunicação entre empresas e bancos, planos de saúde e outras empresas o uso de arquivos de remessa e retorno.
Como por exemplo nesta tabela deste arquivo de layout do banco Bradesco:
Esses arquivos normalmente são de dois tipos, separados por ponto e vírgula, os arquivos chamados CSV, ou como no caso deste artigo, arquivos com tamanho fixo para cada campo.
No caso deste arquivo nós temos um exemplo de arquivo de remessa aonde cada tipo de campo tem um tamanho pré-definido que deve ser preenchido com espaços em branco no caso de campos de texto, com zeros à esquerda no caso de valores, e uma formatação de AAAAMMDD no caso de campos de data.
A melhor forma é que o arquivo seja gerado automaticamente por um sistema, mas às vezes o mesmo não atende, ou precisamos destas informações rapidamente e não podemos esperar o TI, como no caso de informações para o fiscalizações.
A seguir veremos como criar do zero uma planilha que gera arquivos txt com tamanhos fixos no Excel.
Criando a base de dados
A sua base de dados deve estar bem estruturada, a sua lista no Excel deverá conter os campos que você irá precisar no seu arquivo.
Um dos princípios básicos é que cada coluna só pode conter um tipo de dado. Se uma planilha é de CPF, ela só poderá conter CPFs, se é Nome, somente nomes e assim por diante, assim teremos uma lista normalizada de dados.
Selecione esta lista de dados e pressione ALT+T+TA, formatando a lista como uma tabela de dados, ou em Página Inicial -> Formatar como Tabela.
Clique na guia Design da Tabela e em Propriedades altere o nome da tabela para tbPlano, mas claro, pode escolher o nome que desejar. Esta será a nossa base de dados para a geração do arquivo Txt.
Lista DE PARA de caracteres para remover acentuações
É normal em arquivos delimitados que os campos sejam enviados sem acentuações ou caracteres como Ç.
Por isso iremos criar uma tabela DE PARA, aonde iremos dizer por qual caractere os campos deverão ser substituídos.
Como na imagem pressione ALT+T+TA para criar uma tabela com estes dados, altere o nome da tabela criada para tbCaracteres.
Para extrair os caracteres usei os valores nas células
- D1: àáâãäèéêëìíîïòóôõöùúûüÀÁÂÃÄÈÉÊËÌÍÎÒÓÔÕÖÙÚÛÜçÇñÑ
- D2: aaaaaeeeeiiiiooooouuuuAAAAAEEEEIIIOOOOOUUUUcCnN
E nas colunas da tabela usei:
- Caracter: =EXT.TEXTO($D$1;LIN()-1;1)
- Substituto: =EXT.TEXTO($D$2;LIN()-1;1)
Ao aumentar o tamanho da tabela essas fórmulas preenchem uma lista de DE PARA com cada um dos caracteres.
Com estas listas prontas já podemos tratar as informações no Editor do Power Query.
Remover acentuações e importar tabelas
Seleccione a tabela tbPlano, na planilha Dados e clique na guia Dados -> Da Tabela/Intervalo
Será aberto o Editor do Power Query com as informações da tabela que servirá de base para a geração do arquivo texto no Excel.
- Perceba o nome da Tabela importada do Excel na esquerda, Consultas, e também na direita as Etapas Aplicadas.
- Clique no X no Tipo Alterado nas Etapas Aplicadas, para excluir esta etapa, iremos formatar os campos conforme abaixo:
Nome: Tipo Texto
Valor: Tipo Texto
CPF: Tipo Texto
Matrícula: Tipo Texto
Inclusão: Tipo Data
- Esta atividade foi realizada para que os dados sejam mais facilmente tratados nas etapas seguintes.
- Clique em Fechar e Carregar Para, mas não carregue os dados para o Excel ainda.
- Clique na planilha Aux, aonde temos a Tabela tbCaracteres. Selecione a tabela e na guia Dados selecione a opção Da Tabela/Intervalo, iremos importar esta tabela para o PQ, ela servirá para removermos as acentuações do nome da tabela principal.
Agora vamos tratar essa tabela para formatar ela como uma lista, esta lista servirá para substituirmos os caracteres especiais por caracteres da coluna Substituto no campo Nome da consulta tbPlano.
- Clique em Adicionar Coluna -> Coluna Personalizada, mude o nome para Lista e coloque a fórmula M abaixo:
= {[Caracter],[Substituto]}
- Será criada uma coluna com o nome Lista que conterá uma List com a união dos dois campos.
- Clique em tbPlano
- Clique em Adicionar Coluna -> Coluna personalizada, digite a fórmula M abaixo:
= List.ReplaceMatchingItems(Text.ToList([Nome]),tbCaracteres[Lista])
- Analisando por partes temos a função List.ReplaceMatchingItens, que basicamente substitui os caracteres contidos na lista do parâmetro 1 : Text.ToList([Nome]), pelos caracteres do parâmetro 2, que também é uma lista tbCaracteres[Lista], acessando assim a lista que criamos do DE PARA de caracteres para remover os caracteres especiais. A função Text.ToList converte uma coluna de Texto em uma lista de caracteres, permitindo assim o uso da função List.ReplaceMatchingItens que trabalha apenas com listas nos parâmetros.
- Clique no ícone superior direito da coluna que foi criada com o nome Nome caracteres e clique na segunda opção, Extrair Valores
- Na tela seguinte não marque nenhum delimitador para concatenar valores de lista e clique em OK.
- Serão extraídos os dados da coluna e estes dados virão sem os caracteres especiais e acentuações do DE PARA.
- Clique novamente em Adicionar Coluna -> Coluna personalizada, chame-a de Nome formatado. Com a fórmula abaixo iremos colocar as letras todas em maiúsculo e também iremos preencher com espaços em branco até o tamanho de caracteres à direita do nome, ficando este campo então sempre com caracteres.
= Text.Upper([Nome caracteres]&Text.Repeat(” “,50-Text.Length([Nome caracteres])))
- A função Text.Upper recebe como parâmetro uma coluna de Texto e a transforma em caracteres maiúsculos.
- A função Text.Repeat recebe dois parâmetros, qual o caractere que será repetido, no caso ” “, espaço em branco, e quantas vezes será repetido, no caso – o tamanho Text.Length do campo Nome caracteres.
Veja que a coluna exibe … pois os dados não estão todos aparecendo, são os espaços em branco.
Agora iremos tratar os outros campos para preencher cada campo com um exemplo diferente de formatação com tamanho fixo de caracteres que é comum em arquivos texto, para que possamos gerar facilmente no Excel este arquivo.
Formatar Valores para arquivos Texto no Excel
No formato do arquivo que estamos usando como exemplo, os valores serão preenchidos com 0 à esquerda do número, totalizando a coluna com 10 caracteres sem vírgula, a vírgula deve ser excluída.
Por exemplo: 135,47 ficaria 0000013547
Desta forma, criaremos uma coluna personalizada para este campo e deixaremos neste formato.
- Clique em Adicionar Coluna -> Coluna personalizada
- Coloque o nome de Valor Formatado e inclua a fórmula M abaixo:
=Text.Repeat(“0”, 10-Text.Length(Text.Replace([Valor], “,”, “”)))&Text.Replace([Valor], “,”, “”)
- A função Text.Repeat como vimos repete um caractere x vezes, e a função Text.Replace realiza a substituição de um caractere por outro, ela recebe 3 parâmetros, o texto com os caracteres à substituir, o segundo é o caractere que será substituído, e o terceiro o caractere que o substituirá, no caso o texto é a coluna [Valor], “,” é a vírgula que será substituída e “” vazio que substituirá a vírgula.
- Concatenado a esta fórmula com o & temos Text.Replace novamente, agora apenas com o valor, então temos 10 menos o tamanho do campo valor sem a vírgula, concatenado com o campo valor sem a vírgula, totalizando assim os 10 caracteres.
Formatação do CPF para arquivo de texto com tamanho fixo
No caso do CPF temos na nossa tabela de dados um CPF formatado com todos os pontos e o traço característicos, eles são caracteres na coluna, temos que removê-los.
Exemplo: 135.447.895-21 ficaria 13544789521
Para isso iremos criar uma coluna personalizada e remover os caracteres que não precisamos.
- Clique em Adicionar Coluna -> Coluna Personalizada
- Adicione a fórmula M abaixo, que irá substituir os caracteres . e –
= Text.Replace(Text.Replace([CPF], “.”, “”), “-“, “”)
- Assim como vimos anteriormente a função Text.Replace realiza a substituição de caracteres. Perceba que assim como no Excel, colocamos uma função usando a outra como parâmetro, realizando assim primeiro a substituição do “.” pelo “” e depois do “-” pelo “” criando a coluna CPF Formatado.
Formatando o campo matrícula para a geração do arquivo Txt no Excel
O campo Matrícula é um campo do tipo Texto no arquivo Txt, por isso devemos preencher com espaços em branco à direita conforme o layout que estamos seguindo como padrão.
Este campo possui o tamanho fixo de 10 caracteres, por exemplo:
A matrícula “13245” ficaria ” 13245″
- Clique em Adicionar Coluna -> Coluna Personalizada
- Coloque o nome de Matrícula formatada e a fórmula abaixo:
= [Matrícula]&Text.Repeat(” “, 10-Text.Length([Matrícula]))
- Esta fórmula concatena a coluna Matrícula com o espaço, preenchendo com o restante de caracteres necessários à direita usando as funções Text.Repeat e Text.Length como visto anteriormente.
Criando a coluna de Data formatada para a geração do arquivo texto com campos de tamanhos fixos
O campo de data precisa ser expresso com os 4 dígitos do ano, 2 do mês e os 2 do dia nesta ordem e sem as barras, por exemplo:
A data 01/06/2019 ficaria 20190601
Para isso iremos criar uma nova coluna personalizada e remover os caracteres e colocar a coluna neste formato de apresentação de Texto.
- Clique em Adicionar Coluna -> Coluna personalizada
- No nome da coluna coloque Data Formatada
- Na fórmula M digite o seguinte texto:
= Date.ToText([Inclusão], “yyyymmdd”)
- Na fórmula M usamos a função Date.ToText, ela realiza a conversão de datas para o formato de texto e ainda permite que este formato seja exibido como desejar, como no caso colocamos “yyyymmdd”, que já coloca o formado de ano, mês e dia como descrito no começo desta etapa.
Criando a linha Detalhe do arquivo
Nesta etapa iremos criar a coluna que conterá o detalhe completo do arquivo, ele totalmente formatado.
A ordem dos campos é a seguinte: Data, CPF, Matrícula, Nome e Valor.
Para isso iremos criar uma coluna formatada que concatenará todos estes campos.
- Clique em Adicionar Coluna -> Coluna personalizada
- No campo de nome da nova coluna coloque Linhas e na fórmula digite o seguinte texto:
= [Data Formatada]&[CPF Formatado]&[Matrícula formatada]&[Nome formatado]&[Valor formatado]
- O caractere & irá juntar todas as colunas que formatamos e formar o detalhe do arquivo texto.
- Agora que temos os dados formatados e prontos, exclua as outras colunas, deixe apenas a coluna Linhas. Para isso, selecione as colunas e pressione Delete, deixando apenas a coluna Linhas.
- Clique em Fechar e em Manter, para que as modificações sejam gravadas.
- Voltando ao Excel clique na guia Dados -> Consultas e Conexões
- Clique sobre tbPlano e com o botão direito clique em Carregar para e selecione a opção Tabela
- Selecione a coluna Resultado na célula A1 e clique em OK, aguarde a tabela carregar.
Gerando o arquivo txt com o VBA
Com os dados formatados já é possível gerar o arquivo txt, bastaria por exemplo copiar os dados da tabela da planilha Resultados e colar no Notepad. Mas podemos automatizar esta tarefa também.
- Clique sobre a guia Desenvolvedor (Como habilitar a guia desenvolvedor no Excel) e depois clique no botão Visual Basic ou pressione ALT+F11
- Clique em Inserir -> Módulo
- No módulo cole o seguinte código:
Sub lExportarTxt()
On Error GoTo Sair
Application.ScreenUpdating = False
Dim lWorkBook As Workbook
Dim lPlan As Worksheet
Set lWorkBook = Workbooks.Add
wResultado.Copy Before:=lWorkBook.Sheets(1)
lWorkBook.SaveAs Filename:= _
ThisWorkbook.Path & "\Remessa " & Format(Now(), "yyyymmdd-hhmmss") & ".txt", _
FileFormat:=xlTextMSDOS, CreateBackup:=False
lWorkBook.Close SaveChanges:=False
MsgBox "Processo concluído!"
Sair:
Set lWorkBook = Nothing
Application.ScreenUpdating = True
End Sub
- O código acima realiza a exportação da planilha Resultado no formato Txt
- Retorne para o Excel e crie uma imagem qualquer clicando na guia Inserir -> Ilustrações e qualquer imagem ou forma geométrica, por exemplo um retângulo e crie um botão.
- Clique com o botão direto sobre ela e em Atribuir Macro, selecione a macro lExportarTxt como na imagem
- Clique no botão para testar, se funcionar irá mostrar a mensagem abaixo.
Ao clicar sobre o botão criado a macro é acionada e é criado um arquivo na pasta aonde está a sua planilha Excel, este arquivo é delimitado com os espaços em branco, zeros á esquerda e remoção de caracteres conforme definimos no layout, gerando assim o arquivo Txt no Excel.
Download da planilha
Para o download do exemplo utilizado neste artigo preencha o seu nome e e-mail no formulário abaixo.
Abraço
Marcos Rieper
Curso Excel Completo – Do Básico ao VBA
Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:
