Compare TXT with Table in database

Question

I have a TXT file generated by SISOBI, where it contains a list of people who died.

This txt is generated, and I need to read the file and compare the CPF, which is the only key present in the file, with my database and check if you have any employee who owns the same CPF.

I upload the file, separate people by the amount of characters (210) and remove the CPF through the SubString.

My problem is how to compare the CPF with the data returned from my table.

Man Controller that carries out these actions is like this:

public ActionResult Index(HttpPostedFileBase file)
        {
            //verifica se o arquivo está nulo
            if (file == null)
            {
                TempData["MensagemError"] = "Erro ao realizar o upload do arquivo!";
                return View("Index");
            }

            //Salvar o arquivo txt
            string path = Path.Combine(Server.MapPath("~/App_Data/Uploads/" + Path.GetFileName(file.FileName)));
            file.SaveAs(path);

            //Realiza a leitura do arquivo txt
            var fileContents = System.IO.File.ReadAllText(path);

            //Separa o texto em blocos de 210 caracteres, conforme o Layout
            var partes = SplitBlocks(fileContents, 212);
            foreach (var parte in partes)
            {
                var Data = parte.Substring(155, 8);
                var Cpf = parte.Substring(163, 11);   
            }


                        //Separa os dados pelo substring e salva em suas variáveis
        var DtObito = fileContents.Substring(155, 8);
        var CPF = fileContents.Substring(163, 11);

        //converte data para o formato dd/MM/yyyy
        var dtMorte = DtObito.Substring(6, 2) + "/" + DtObito.Substring(4, 2) + "/" + DtObito.Substring(0, 4);

        //Converte o CPF para int
        var cpfcerto = Convert.ToInt64(CPF);

        //Consulta dos usuários com a variável cpf
        var usuarios = usuarioRepository.Lista.Where(u => u.NrCpf == cpfcerto).ToList();


            if (usuarios.Count > 0)
            {
                TempData["UsuarioEncontrado"] = "Existe funcionário.";
                return View(usuarios);
            }

            TempData["Usuario"] = "Nenhum funcionário encontrado.";
            return View();

        }

This way is using only the Substring to check if you are actually reading the file.

I thought I’d put the consult inside For, but the consultation is very time consuming, because the file txt has more than 100 thousand people and on average 22mb in size.

Each person with their respective data has a size of 210 characters. To perform this separation I use this method.

 public static List<String> SplitBlocks(string texto, int tamanho)
        {
            var partes = new List<String>();
            var posicao = 0;
            var total = texto.Length;
            while (total >= posicao + tamanho)
            {
                partes.Add(texto.Substring(posicao, tamanho));
                posicao += tamanho;
            }
            return partes;
        }

Tabela Funcionários para comparação.

 [Key]
        [Column("CdPessoa")]
        public double iUsuarioID { get; set; }
        public string Cod_Lotacao { get; set; }
        public string Descricao { get; set; }
        public string NmFuncionario { get; set; }
        public string nmMunicipio { get; set; }
        public string NaTipoLogradouro { get; set; }
        public string nmLogradouro { get; set; }
        public int CdCep { get; set; }
 [DataType(DataType.Date)]
        [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)]
        public DateTime DtNascimento { get; set; }
  public double NrCpf { get; set; }

I wonder if there is a way to do this. Using ViewModel to save data from txt and compare the two. Save the data from txt in the database, and after comparing the two.

Answer 1

If I were you, I’d make two KeyedCollection to index the CPF: one to your Model, another for extraction from the archive:

...
using System.Collections.ObjectModel;
...

public class MortoCollection : KeyedCollection<String, Morto>
{
    protected override string GetKeyForItem(Morto item)
    {
        return item.NrCpf.ToString();
    }
}

This allows you to do something like this:

var colecao = new MortoCollection();
// Adicione aqui um item com CPF = 12345678901
var morto = colecao['12345678901'];

KeyedCollections only allow you to add items one by one, so it is interesting that you implement an extension to be able to add multiple objects at once:

namespace SeuProjeto.Common.Extensions
{
    public static class KeyedCollectionExtensions
    {
        public static void AddRange<T>(this ICollection<T> destination,
                                   IEnumerable<T> source)
        {
            foreach (T item in source)
            {
                destination.Add(item);
            }
        }
    }
}

Then you can do it this way:

var colecaoMortos = new MortoCollection { usuarioRepository.Lista.ToList() };
var morto = colecao['12345678901'];

As for the file elements, I believe that the logic already deserves to go to a Helper separately, or at least a part of it. I would make a method that turns the text document into a list of objects:

public static class LeituraArquivoHelper
{
    public static IEnumerable<LinhaMortoArquivo> ConverterTxtParaLista(String arquivoBruto) 
    {
        foreach (var parte in SplitBlocks(arquivoBruto, 212))
        {
            //Separa os dados pelo substring e salva em suas variáveis
            var DtObito = parte.Substring(155, 8);
            var CPF = parte.Substring(163, 11);

            //converte data para o formato dd/MM/yyyy
            var dtMorte = DtObito.Substring(6, 2) + "/" + DtObito.Substring(4, 2) + "/" + DtObito.Substring(0, 4);

            //Converte o CPF para int
            var cpfcerto = Convert.ToInt64(CPF);

            yield return new LinhaMortoArquivo 
            {
                DataObito = dtMorte,
                Cpf = cpfcerto
            };
        }
    }
}

LinhaMortoArquivo can be such a class:

public class LinhaMortoArquivo
{
    public String DataObito { get; set; }
    public String Cpf { get; set; }
}

Your respective KeyedCollection could be so:

...
using System.Collections.ObjectModel;
...

public class LinhaMortoArquivoCollection : KeyedCollection<String, LinhaMortoArquivo>
{
    protected override string GetKeyForItem(LinhaMortoArquivo item)
    {
        return item.Cpf.ToString();
    }
}

Therefore, to popular a collection with the lines of the file, you could do simply like this:

//Realiza a leitura do arquivo txt
var fileContents = System.IO.File.ReadAllText(path);
var linhasArquivo = new LinhaMortoArquivoCollection { LeituraArquivoHelper.ConverterTxtParaLista(fileContents) };

A comparison loop would be something like:

foreach (linha in linhasArquivo)
{
    var linhaDatabase = colecaoMortos[linha.NrCpf];
}

I don’t know if it’s enough, but by now you’d have code to compare the two collections in your View. If you need me I’ll extend the answer.