Im new to ASP.NET Core and am tasked with creating an app that can import CSV files and basically convert the rows into Data Objects.

Ex.

Import CSV with Name Address and DOB Insert each record into Customer object

This needs to be done from a user uploading the CSV to the web app, so uploading the CSV straight to the SQL DB will not work.

Im curious how I approach this as I am new to ASP.NET Core and Razor pages.

CodePudding user response：

I'm assuming you already have a Customer class that represents your database entities. You can create a second class e.g. CustomerDto that will be used to serialize the CSV entries to objects. Then you can map CustomerDtos to Customers using AutoMapper.

public class CustomerDto
{
    [Name("Name")]
    public string Name { get; set; }

    [Name("Address")]
    public string Address { get; set; }

    [Name("DOB")]
    public DateTime? DateOfBirth { get; set; }
}

Use an IFormFile property in your PageModel to receive the file that will be posted from the form.

Import.cshtml.cs:

using CsvHelper;
...

public class ImportModel : PageModel
{
    private readonly IMapper _mapper;

    public ImportModel(IMapper mapper)
    {
        _mapper = mapper;
    }

    [BindProperty]
    public IFormFile CustomersCsv { get; set; }

    public async Task<IActionResult> OnPostAsync()
    {
        if (CustomersCsv != null)
        {
            try
            {
                using (var reader = new StreamReader(CustomersCsv.OpenReadStream()))
                using (var csvr = new CsvReader(reader))
                {
                    csvr.Configuration.Delimiter = "\t";
                    //csvr.Configuration.HeaderValidated = null;
                    //csvr.Configuration.MissingFieldFound = null;

                    var customerDtos = csvr.GetRecords<CustomerDto>();
                    var customers = _mapper.Map<IEnumerable<Customer>>(customerDtos);
                    
                    // save customers to database
                }               
            }
            catch (Exception ex)
            {
                // handle exception
            }
        }
    }
}

Import.cshtml:

<form method="post" enctype="multipart/form-data">
    <div asp-validation-summary="ModelOnly" ></div>
    <div >
        <label asp-for="CustomersCsv" ></label>
        <input asp-for="CustomersCsv"  />
        <span asp-validation-for="CustomersCsv" ></span>
    </div>
    <div >
        <input type="submit" value="Submit"  />
    </div>
</form>

CodePudding user response：

You may be able to use this out of the box or tweak it to your needs. Make sure you take a look at the specific settings being set here!

        public DataTable GetDataTabletFromCSVFile(string path)
    {

        DataTable csvData = new DataTable();

        try
        {
            using (TextFieldParser csvReader = new TextFieldParser(path))
            {
                csvReader.SetDelimiters(new string[] { "," }); //assuming comma delimeter
                csvReader.HasFieldsEnclosedInQuotes = true;
                string[] colFields = csvReader.ReadFields();

                foreach (string column in colFields)
                {
                    DataColumn serialno = new DataColumn(column);

                    serialno.AllowDBNull = true;
                    csvData.Columns.Add(serialno);
                }

                while (!csvReader.EndOfData)
                {
                    string[] fieldData = csvReader.ReadFields();
                    DataRow dr = csvData.NewRow();
                    //Making empty value as empty
                    for (int i = 0; i < fieldData.Length; i  )
                    {
                        if (fieldData[i] == null)
                            fieldData[i] = string.Empty;

                        dr[i] = fieldData[i];
                    }
                    csvData.Rows.Add(dr);
                }
            }
        }
        catch (Exception ex)
        {
            //write to error log
        }
        return csvData;
    }