Home / Questions / 73135
Accepted
ByGroxD
Asked: 2020-05-26 07:56:37 +0800 CST 2020-05-26 07:56:37 +0800 CST

Import data from CSV to database from MVC5 using a form

  • 772

I am doing a project and at this moment I need to import data from an "Excel" CSV file Delimited by commas (,) certain information, I need that through a form they can upload the excel and that said excel is automatically imported into the table dbo.CollaboratorsI am using MVC5 and SQLServer 2014.

My view is the following:

@model SI_OldMutual.Models.Collaborators

@{
    ViewBag.Title = "UploadPlanta";
}

<h2>UploadPlanta</h2>

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    <div class="form-group">
        <input class="form-control" type="file" name="planta" /><br />
        <input class="btn btn-default" type="submit" value="Cargar" />
    </div>
}

Part of my controller is the following:

private SI_OldMutualContext db = new SI_OldMutualContext();

//  UploadPlanta
public ActionResult UploadPlanta()
{
    return View();
}

//POST UploadPlanta
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult UploadPlanta([Bind(Include = "planta")] Collaborators collaborators)
{
    return View();
}

Part of my model:

[Key]
public int CollaboratorID { get; set; }
[Required]
[Display(Name = "Codigo")]
public string codigo { get; set; }
[Display(Name = "Cedula")]
public string cedula { get; set; }
[Display(Name = "Nombres")]
public string nombres { get; set; }
[Display(Name = "Fecha Ingreso")]
[DataType(DataType.Date)]
[DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:yyyy-MM-dd}")]
public DateTime fecha_ingreso { get; set; }
[Display(Name = "Salario Basico")]
[DisplayFormat(DataFormatString = "{0:C0}", ApplyFormatInEditMode = false)]
public decimal salario_basico { get; set; }
[Display(Name = "Salario Cargo")]
[DisplayFormat(DataFormatString = "{0:C0}", ApplyFormatInEditMode = false)]
public decimal salario_cargo { get; set; }
[Display(Name = "Regimen")]
public string regimen { get; set; }
[Display(Name = "Tipo de Contrato")]
public string tipo_contrato { get; set; }
[Display(Name = "Ciudad")]
public string ciudad { get; set; }

Explanation: I need that through said form they can insert the CSV file delimited by commas and at the moment of automatically loading the information that is in said file, it is imported into the table Collaboratorscorresponding to the model.

I have been reviewing several examples in internet groups and StackOverFlow but I can't find a clear example regarding what I need, any input is quite useful.

It should be noted that I am using MVC5 in visual studio 2015.

sql-server

2 Answers

  1. Best Answer

    The first thing is to receive the file you have uploaded in the Controller, save it on the server, read it with Linq and store it in the database:

    [HttpPost]
public ActionResult UploadPlanta(HttpPostedFileBase file)
{
    try
    {
        if (file.ContentLength > 0)
        {
            //Guardado del archivo en el server
            string _FileName = Path.GetFileName(file.FileName);
            string _path = Path.Combine(Server.MapPath("~/Uploads"), _FileName);
            file.SaveAs(_path);

            //Leemos el CSV y lo pasamos a una lista
            List<Collaborators> listaColaboradores = (from p in File.ReadAllLines(_path)
                                    let parts = p.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries)
                                    select new Collaborators 
                                    {
                                        CollaboratorID = Convert.ToInt32(parts[0]),
                                        codigo = parts[1],
                                        cedula = parts[2],
                                        nombres = parts[3],
                                        fecha_ingreso = Convert.ToDateTime(parts[4]),
                                        salario_basico = Convert.ToDecimal(parts[5]),
                                        salario_cargo = Convert.ToDecimal(parts[6]),
                                        regimen = parts[7],
                                        tipo_contrato = parts[8],
                                        ciudad = parts[9]
                                    }).ToList();

            //Guardamos toda la información de esa lista en base de datos
            using (var context = new ApplicationDbContext())
            {
                foreach(var colaborador in listaColaboradores){
                    context.Collaborators.Add(colaborador);
                }

                context.SaveChanges();
            }
        }
        ViewBag.Message = "El archivo se subió y procesó correctamente";
        return View();
    }
    catch (Exception ex)
    {
        ViewBag.Message = "Error procesando la información: " + ex.Message;
        return View();
    }
}

    Take into account that when reading the file, each part[X]one will be mapping to a CSV column, that part you have to adjust depending on the design of the CSV file.

    • 5

  2. Can be worked in 2 parts

    1. Upload the file to the server

    here an example controller

    using System;  
usingSystem.Collections.Generic;  
using System.IO;  
usingSystem.Linq;  
usingSystem.Web;  
usingSystem.Web.Mvc;  
namespaceFileUpload.Controllers  
{  
    public class UploadController: Controller  
    {  
        // GET: Upload  
        publicActionResult Index()  
        {  
            return View();  
        }  
        [HttpGet]  
        publicActionResultUploadFile()  
        {  
            return View();  
        }  
        [HttpPost]  
        publicActionResultUploadFile(HttpPostedFileBase file)  
        {  
            try  
            {  
                if (file.ContentLength > 0)  
                {  
                    string _FileName = Path.GetFileName(file.FileName);  
                    string _path = Path.Combine(Server.MapPath("~/UploadedFiles"), _FileName);  
                    file.SaveAs(_path);  
                }  
                ViewBag.Message = "File Uploaded Successfully!!";  
                return View();  
            }  
            catch  
            {  
                ViewBag.Message = "File upload failed!!";  
                return View();  
            }  
        }  
    }  
}

    Here is an example of the view

    @{  
    ViewBag.Title = "UploadFile";  
}  

<h2>UploadFile</h2>  

@using(Html.BeginForm("UploadFile","Upload", FormMethod.Post, new { enctype="multipart/form-data"}))  
{  

    <div>  
        @Html.TextBox("file", "", new {  type= "file"}) <br />  

        <input type="submit" value="Upload" />  

        @ViewBag.Message  

    </div>  


}

    Here is a pretty clear tutorial:

    http://www.c-sharpcorner.com/article/upload-files-in-asp-net-mvc-5/

    Once you have uploaded the file, with this TSQL statement you can load it into your table

    BULK INSERT SchoolsTemp
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    )

    Of course you must adjust it to your file, table and csv structure

    Cheers,

    • 1