Upload CSV data into SQL database using ASP.NET Core MVC

Question

I am trying to insert data from a .csv file into my database, but anytime I upload data, the record is empty.

This is my code so far:

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile formFile)
{
    var data = new MemoryStream();
    await formFile.CopyToAsync(data);

    data.Position = 0;
    TextReader reader = new StreamReader(data);

    var csvReader = new CsvReader(reader, new CsvConfiguration(System.Globalization.CultureInfo.CurrentCulture)
                        {
                            HasHeaderRecord = true,
                            HeaderValidated = null,
                            MissingFieldFound = null
                        });

    var Name = csvReader.GetField(0).ToString();
    var dep = "cccccccccc";
    var pos = "bbbbbbbbbbb";

    await dcx.Participants.AddAsync(new Participant
                                        {
                                            Name = Name,
                                            Position = pos,
                                            Department = dep,
                                        });
    dcx.SaveChanges();
    
    return ViewComponent("ViewParticipants");
}

This is the sample data in my database table:

enter image description here

the csv is not saved on the server and i do not want to save it on the server either. i just want to upload and read data — Katakyie Kofi Poku, May 31 '22 at 03:16
You never call `csvReader.Read()` to read the first row. See [How to use CSV Helper to read line by line in c#?](https://stackoverflow.com/a/58670866). — dbc, May 31 '22 at 04:56
You should also dispose of your `StreamReader` and `CsvReader` properly via a `using` statement. — dbc, May 31 '22 at 04:57

score 0 · Answer 1 · edited Nov 21 '22 at 23:00

In my opinion, you should call csvReader.Read() to read the file row first.

You can refer to the following test code, it works fine.

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile formFile)
{
     var data = new MemoryStream();
     await formFile.CopyToAsync(data);

     data.Position = 0;
     using (var reader = new StreamReader(data))
     {
           var bad = new List<string>();
           var conf = new CsvConfiguration(CultureInfo.InvariantCulture)
           {
                HasHeaderRecord = true,
                HeaderValidated = null,
                MissingFieldFound = null,
                BadDataFound = context =>
                {
                     bad.Add(context.RawRecord);
                }
           };
           using (var csvReader = new CsvReader(reader, conf))
           {
                while (csvReader.Read())
                {
                     var Name = csvReader.GetField(0).ToString();
                     var pos = csvReader.GetField(1).ToString();
                     var dep = csvReader.GetField(2).ToString();
                        
                     await dcx.Participants.AddAsync(new Participant
                     {
                          Name = Name,
                          Position = pos,
                          Department = dep,
                     });
                     dcx.SaveChanges();
                }
           }
     }
     return ViewComponent("ViewParticipants");
}

Test Result:

Thank you @Chen your answer helped a lot. I am grateful. but does it have a limit to the data it can read? — Katakyie Kofi Poku, May 31 '22 at 10:48
In my opinion,there is no limitation imposed by `CsvHelper` though.If you run into problems,you can try not doing a `ToList()` and put it in a loop instead. This will pull one record at a time. Set a break point in the loop and see if you're getting anything that way. If so, make a counter and see how many records it pulls. — Chen, Jun 01 '22 at 00:53

score 0 · Answer 2 · answered May 31 '22 at 13:14

As long as the headers of your CSV match up to the names of the columns in your database, you should be able to do something like this. If the names don't match, you can use .Name("CsvColumnName") in ParticipantMap to add the name of the column in the CSV file. Example: Map(r => r.Description).Name("MyCsvDescription");.

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile formFile)
{
    var data = new MemoryStream();
    await formFile.CopyToAsync(data);

    data.Position = 0;

    var conf = new CsvConfiguration(CultureInfo.InvariantCulture)
    {
         HasHeaderRecord = true,
         HeaderValidated = null,
         MissingFieldFound = null,
         BadDataFound = context =>
         {
              bad.Add(context.RawRecord);
         }
    };
    using (TextReader reader = new StreamReader(data))
    using (var csvReader = new CsvReader(reader, config)) {

        csvReader.Context.RegisterClassMap<ParticipantMap>();
        var records = csvReader.GetRecords<Participant>().ToList();

        var dep = "cccccccccc";
        var pos = "bbbbbbbbbbb";

        records.ForEach(r =>
        {
            r.Department = dep;
            r.Position = pos;
        });

        await dcx.Participants.AddRangeAsync(records);
        dcx.SaveChanges();
    }
    
    return ViewComponent("ViewParticipants");
}

public class ParticipantMap : ClassMap<Participant>
{
    public ParticipantMap()
    {
        AutoMap(CultureInfo.InvariantCulture);
        Map(r => r.Department).Ignore();
        Map(r => r.Position).Ignore();
    }
}

Upload CSV data into SQL database using ASP.NET Core MVC

2 Answers2