Import data from CSV to database from MVC5 using a form

Question

Import data from CSV to database from MVC5 using a form

Navigation

#1 by (3 votes)
#2 by (1 votes)

1

I am doing a project and at this moment I need to import data from a CSV file "Excel" Delimited by commas (,) certain information, I need that by means of a form they can upload the excel and that this excel is automatically imported in the table dbo.Collaborators I'm using MVC5 and SQL Server 2014.

My view is as follows:

@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 as follows:

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 to insert the comma delimited CSV file by means of this form, and when I upload the information that is in the file, it will be imported into the table Collaborators corresponding to the model .

I have been reviewing several examples in internet groups and StackOverFlow but I do not give a clear example with regard to what I need, any contribution is quite useful.

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

sql-server asp.net-mvc-5 visual-studio-2015

asked by ByGroxD 25.05.2017 в 17:56

source

2 answers

1

You can work in 2 parts

Upload the file to the server

here is an example of a driver

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 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:

link

Once you have uploaded the file, with this TSQL instruction you can upload it to 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

Greetings,

answered by 25.05.2017 в 18:19

Select the first value of a decimal jQuery modal window does not close

score 3 · Accepted Answer

The first thing is to receive the file you have uploaded to 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, every part[X] will be mapped to a column of the CSV, that part you have to adjust depending on the design of the CSV file.