Precisa adicionar números de id de iteração ausentes a uma série no texto ou no Excel. Como conseguir isso sem codificação?

Question

Eu tenho um arquivo de texto, csv ou excel que parece

||--ID-----||--Name--||--Date of birth--||
    1            Jo          1/1/11
    32           Mo          2/2/12
    3382         Ro          3/3/10
    21,252       Do          4/4/09

O conjunto real contém 280.000 destes do total de 1.000.000 linhas. Eu preciso adicionar de alguma forma todos os números de ID que faltam (que são todos iteração +1 sequencial) e apenas campos vazios para nome e dob. Para que eu consiga algo que pareça:

||--ID-----||--Name--||--Date of birth--||
    1,           "Jo",     "1/1/11"
    2,           "",         ""
    3,           "",         ""
    4,           "",         ""

todo o caminho até 32, então, novamente, até que todo número inteiro esteja lá. Existe uma maneira fácil de fazer isso? sem escrever um bloco de código em um loop? Qualquer coisa do Excel, algum aplicativo ou qualquer truque do editor de texto do Windows seria apreciado.

Editar: Por favor, ignore as vírgulas, aspas, etc. Os números de identificação perdidos são a única coisa crítica.

    

Answer 1

Uma abordagem seria:

1. importe seu CSV para o Excel como folha 1.
2. na Folha 2, crie sua lista completa de números de ID com uma fórmula como a2: = a1 + 1 e copie para baixo.
3. use fórmulas vlookup nas outras 2 colunas, referindo-se à sua tabela na planilha 1. como b2: =VLOOKUP(A2,Sheet1!A2:C13,2,false) e c2: =VLOOKUP(A2,Sheet1!A2:C13,3,false) , ou para corresponder exatamente a sua solicitação, permite que isso seja colocado em uma instrução IFNA para retornar "" se não houver nenhum valor. b2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,2,FALSE),"") e c2: =IFNA(VLOOKUP(A2,Sheet1!A2:C13,3,FALSE),"") (essas fórmulas agora podem ser copiadas na coluna.)
4. Uma vez preenchida, salve a planilha 2 como CSV.

Observação: para que isso funcione corretamente, a tabela na planilha 1 precisará ser classificada na coluna 1. A partir das informações fornecidas, parece ser assim, mas se não for possível classificar as informações na primeira coluna.

Com um milhão de linhas, isso vai ficar muito lento em muitos computadores, eu perdi isso antes de escrever as fórmulas. Eu fiz algo semelhante uma vez com um grande conjunto de dados, desligando o auto relcalc para obter todas as fórmulas no lugar, em seguida, fiz um recálculo manual. que levou horas, mas completou corretamente.

    

Answer 2

Eu resolveria isso com o complemento do Power Query.

Eu criei um protótipo que você pode ver ou baixar - é a "demonstração do Power Query - Adicione números de ID ausentes a uma série.xlsx" no meu One Drive:

link

São necessários alguns passos para chegar lá e um pouco de codificação na linguagem Power Query (M) para chamar a função List.Numbers (ela não está exposta na interface de usuário do Power Query). Ainda assim, essa é apenas uma linha simples de código - o restante pode ser construído clicando em Power Query.

Basicamente, minha técnica era usar List.Numbers para gerar uma tabela de números de ID, então eu adicionei uma Merge para obter as colunas dos dados de entrada (onde o número de ID existe).

A documentação do List.Numbers está aqui:

link

Enorme "dica de chapéu" para Matt Masson para a técnica "Configurações".

link

Observação O Power Query pode ler diretamente de um arquivo CSV, que eu usaria como fonte para a consulta "Dados de entrada". É provavelmente mais fácil excluir essa Consulta, depois criar uma nova no arquivo CSV e nomeá-la como "Dados de Entrada". Desmarque a opção Carregar na planilha para salvar recursos.