C#•6mo ago

Seeding data from csv file

I have a model like this:

    public class Record
    {
        public int Id { get; set; }
        public long caller_id { get; set; }
        public long recipient { get; set; }
        public DateTime call_date { get; set; }
        public DateTime endtime { get; set; }
        public int duration { get; set; }
        public double cost { get; set; }
        public string reference { get; set; }
        public string currency { get; set; }
    }

    public class Record
    {
        public int Id { get; set; }
        public long caller_id { get; set; }
        public long recipient { get; set; }
        public DateTime call_date { get; set; }
        public DateTime endtime { get; set; }
        public int duration { get; set; }
        public double cost { get; set; }
        public string reference { get; set; }
        public string currency { get; set; }
    }

I try to seeding data from my csv file like this:

        public void SeedDataContext()
        {
            if (!_ctx.Record.Any())
            {
                var filePath = "C:/techtest_cdr.csv"; 

                var csvData = System.IO.File.ReadAllLines(filePath).Skip(1);

                var datas = new List<Record>();

                foreach (var line in csvData)
                {
                    var values = line.Split(',');

                    var record = new Record
                    {
                        caller_id = long.Parse(values[0]),
                        recipient = long.Parse(values[1]),
                        call_date = DateTime.ParseExact(values[2], "dd/MM/yyyy",CultureInfo.InvariantCulture),
                        endtime = DateTime.ParseExact(values[3], "HH/mm/ss", CultureInfo.InvariantCulture),
                        duration = int.Parse(values[4]), 
                        cost = double.Parse(values[5]), 
                        reference = values[6],
                        currency = values[7].ToUpper() 
                    };

                    datas.Add(record);
                }

                _ctx.Record.AddRange(datas);
                _ctx.SaveChanges();
            }
        }

        public void SeedDataContext()
        {
            if (!_ctx.Record.Any())
            {
                var filePath = "C:/techtest_cdr.csv"; 

                var csvData = System.IO.File.ReadAllLines(filePath).Skip(1);

                var datas = new List<Record>();

                foreach (var line in csvData)
                {
                    var values = line.Split(',');

                    var record = new Record
                    {
                        caller_id = long.Parse(values[0]),
                        recipient = long.Parse(values[1]),
                        call_date = DateTime.ParseExact(values[2], "dd/MM/yyyy",CultureInfo.InvariantCulture),
                        endtime = DateTime.ParseExact(values[3], "HH/mm/ss", CultureInfo.InvariantCulture),
                        duration = int.Parse(values[4]), 
                        cost = double.Parse(values[5]), 
                        reference = values[6],
                        currency = values[7].ToUpper() 
                    };

                    datas.Add(record);
                }

                _ctx.Record.AddRange(datas);
                _ctx.SaveChanges();
            }
        }

When I run the program, I got the error: Unhandled exception. System.FormatException: String '' was not recognized as a valid DateTime. The format for date time for call_date in my csv file is dd/mm/yyyy. The format for endtime in my csv file is HH/mm/ss.

15 Replies

yatta•6mo ago

How should I modify the Seeder to get rid of this error and have the data in csv file written in my database ?

Jimmacle•6mo ago

i assume the formats you've specified for your datetime parsing don't match some or all of the values in your CSV file specifically it looks kinda like some entires don't have dates/times specified

yatta•6mo ago

this is how the format in my csv file looks like, could you suggest a way to make it match ?

Jimmacle•6mo ago

does every row have values for those columns?

yatta•6mo ago

yes

Jimmacle•6mo ago

because String '' was not recognized means there is an empty string getting in there somewhere

yatta•6mo ago

there're some records that looks like this, maybe they're realized as an empty string ?

Jimmacle•6mo ago

i can't tell if that's actually what it looks like or if that's just formatting in that application, but maybe if it was me, i'd add code to check values[2] and values[3] to see if they're empty and use that to find which row is causing problems or put the record creation in a try/catch and set a breakpoint in the catch

yatta•6mo ago

so I guess formating the csv file is an option, but more advisable to use some trim function better ?

Jimmacle•6mo ago

you're getting ahead of yourself we haven't established that this is actually the problem

yatta•6mo ago

wait we dont?

Jimmacle•6mo ago

the first step is to find out for sure which rows are not working you're guessing, and you might be right, but i'd rather find out for sure than spend time on something that ends up not being the problem

yatta•6mo ago

aight, i'll check as you suggest then

.aimless.little.moth•6mo ago

As you are not getting an ArgumentOutOfRangeException there is probably a line in your csv looking kind of like this ",,,, ,,,". I would open csv file in a notepad and check if this kind of entry exists. It would not be visible in excel but notepad will show this kind of entry

yatta•6mo ago

I did as you said too, and pretty much I've solved this problem by changed to just using csv helper instead. For the csv format, I download the new one and opened it in notepad too, if I open in excel, it will be mutated and the program cant read it probly anymore basicall, i've solved my problem in this post already now

Gaming

Programming

Seeding data from csv file