I want to import data from CSV file to SQL database using Entity Framework

Question

I want insert data from CSV file to SQL table. Here is my code I don't know how to go further

var readcsv = File.ReadAllText(filepath);
string[] csvfilerecord = readcsv.Split('\n');

foreach (var row in csvfilerecord)
{
  if (!string.IsNullOrEmpty(row))
  {
    foreach (var cell in row.Split(','))
    {
      var cards = new Cards
      {
        //What to do here to assign data to each column 
      };
    }
  }
}

here is my csv file data

12345,cvv,18-april,name,country,address,state,city,dob,zipcode,phone,email,10
12345,cvv,18-april,name,country,address,state,city,dob,zipcode,phone,email,10

class Definition

        public string nummber { get; set; }
    public string cvv { get; set; }
    public string expDate { get; set; }
    public string name { get; set; }
    public string country { get; set; }
    public string address { get; set; }
    public string state { get; set; }
    public string dob { get; set; }

Use tools like https://github.com/JoshClose/CsvHelper - you can map row directly to proxy class. — pwas, Jun 29 '16 at 10:00
it has different columns like number cvv , dob , name , country etc — Kamran Khan, Jun 29 '16 at 10:03
Don't dig deep into cell.. Just with `row` map it each class property.. — Guruprasad J Rao, Jun 29 '16 at 10:04
Possible duplicate of [Reading a CSV file in .NET?](https://stackoverflow.com/questions/1405038/reading-a-csv-file-in-net) — Michael Freidgeim, Nov 06 '18 at 12:50
I know this is an old post, but I wonder if this is the correct way to go. I think the fastest way is to insert the CSV data into a Datatable and then insert the Datatable to the database, am I wrong? — Yogi_Bear, Jan 03 '21 at 17:24

score 2 · Answer 1 · answered Jun 29 '16 at 10:06

You need to know which column of the CSV maps to which property. With this information you can map values into properties.

Assuming you have a dictionary of indexes columnMap you can use

var cells = row.Split(',');
var cards = new Cards {
  prop = cells[columnMap["prop"]],
  nextProp = cells[columnMap["nextProp"]],
  …
}

Note I do not iterate over the separate values from one row in the CSV.

Also note you need a proper CSV parser to handle the escaping/quoting necessary for when values contain commas or quotes.

score 2 · Accepted Answer · answered Jun 29 '16 at 10:08

2

var readcsv = File.ReadAllText(filepath);
string[] csvfilerecord = readcsv.Split('\n');

foreach (var row in csvfilerecord)
{
  if (!string.IsNullOrEmpty(row))
  {
    var cells = row.Split(','))
    var card = new Cards
      {
         number = cells[0], // number is in first cell
         cvv = cells[1],   // cvv is in second cell
         // ...
      };
  }
}

answered Jun 29 '16 at 10:08

Georg Patscheider

9,357
1
26
36

1

In order to tackle [newline problem in different OS](https://stackoverflow.com/q/3720612/428061), I suggest using `File.ReadAllLines(filepath)`, instead of `.Split('\n')` method. – Søren Feb 09 '21 at 12:26

I want to import data from CSV file to SQL database using Entity Framework

2 Answers2

Linked

Related