Compare TXT file with table in database

Question

Compare TXT file with table in database

Navigation

#1 by (3 votes)

3

I have a TXT file generated by SISOBI , which 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 it has any employee who has the same% / p>

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

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

My CPF that performs these actions looks 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();

        }

In this way you are only using Controller to check if you are actually reading the file.

I thought of putting the query inside Substring , but the query is very time consuming, because the For file has more than 100 thousand people and an average of 22mb in size.

Each person with their respective data is 210 characters in length. 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 txt to save the ViewModel data and compare the two. Save the data of txt in the Database, and after comparing the two.

asp.net-mvc-5

asked by anonymous 20.03.2015 / 18:26

1 answer

What is the best way to save files, Bank or File System? When you click on the smaller div, open the

score 3 · Accepted Answer

In its place, I would do two KeyedCollection to index the CPF: one for your Model , another for the extraction coming from the file:

...
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'];

KeyedCollection s only allow you to add items one by one, so it's interesting 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);
            }
        }
    }
}

Here you can do the following:

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

In the case of the elements of the file, I believe that the logic already deserves to go to a separate Helper , or at least a part of it. I would do a method that transforms 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 a class like this:

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

Your respective KeyedCollection might look like this:

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

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

So, to populate a collection with the lines of the file, you could do it 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 look like this:

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

I do not know if that's enough, but now you would already have code to compare the two collections in your View . If I need to, I'll give you the answer.