2

I am reading CSV file using LINQ and applying a filter then I am getting data but the problem is my count data is not always matching when comparing data after applying the same filter. I'm opening that CSV file with Excel but I could not determine why my count and excel data is not matching. so here I am pasting my full code with CSV file link. if possible please some one run my code after downloading the CSV file.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;

namespace WindowsFormsApplication1
{

    public partial class Form1 : Form
    {
        DataTable oData = null;
        public Form1()
        {
            InitializeComponent();

            oData = new DataTable();
            oData.Columns.Add("Date", typeof(string));
            oData.Columns.Add("Time", typeof(string));
            oData.Columns.Add("Incomming", typeof(string));
            oData.Columns.Add("Outgoing", typeof(string));
            oData.Columns.Add("Miss Call", typeof(string));
        }

        private void button1_Click(object sender, EventArgs e)
        {
            TimeSpan StartTime;
            TimeSpan EndTime;
            bool flag=true;
            TimeSpan tsStart = new TimeSpan(09, 00, 00);
            TimeSpan tsEnd = new TimeSpan(17, 30, 0);

            List<PhoneData> oPhoneData = GetPhoneData(@"d:\report.csv");
            bool isFirstTime = false;

            oData.Rows.Clear();
            while (tsStart < tsEnd)
            {
                if (!flag)
                {
                    tsStart = new TimeSpan(tsStart.Hours, tsStart.Minutes, int.Parse("01"));
                }
                flag = false;

                StartTime = tsStart;
                tsStart = tsStart.Add(new TimeSpan(00, 30, 00));
                EndTime = TimeSpan.Parse((tsStart.Hours >= 10 ? tsStart.Hours.ToString() : ("0" + tsStart.Hours.ToString())) 
                    + ":" + (tsStart.Minutes >= 10 ? tsStart.Minutes.ToString() : ("0" + tsStart.Minutes.ToString())) + ":00");


                int incomingCount = (from row in oPhoneData
                                     where row.direction == "I"
                                     && row.Call_Start.TimeOfDay >= StartTime
                                     && row.Call_Start.TimeOfDay <= EndTime
                                     && row.Is_Internal == 0
                                     && row.continuation == 0
                                     && row.call_duration.TotalSeconds > 0
                                     && !row.party1name.Contains("Voice Mail")
                                     && !row.party1name.Contains("VM")
                                     select 1).Count();

                int outgoingCount = (from row in oPhoneData
                                     where row.direction == "O"
                                     && row.Call_Start.TimeOfDay >= StartTime
                                     && row.Call_Start.TimeOfDay <= EndTime
                                     && row.Is_Internal == 0
                                     && row.continuation == 0
                                     && row.party1name != "Voice Mail"
                                     && !row.party1name.StartsWith("VM")
                                     select 1).Count();

                int misscallCount = (from row in oPhoneData
                                     where row.direction == "I"
                                     && row.continuation == 0
                                     && row.Caller.Trim() != string.Empty
                                     && row.Call_Start.TimeOfDay >= StartTime
                                     && row.Call_Start.TimeOfDay <= EndTime
                                     && row.party1name != "Voice Mail"
                                     && !row.party1name.StartsWith("VM")
                                     && !row.party1name.StartsWith("Line")
                                     && row.Park_Time == 0
                                     && row.Called_number == "687220"
                                     select 1).Count();


                DataRow dr = oData.NewRow();
                dr["Date"] = "12-04-2016";
                dr["Time"] = StartTime + "-" + EndTime;
                dr["Incomming"] = incomingCount;
                dr["Outgoing"] = outgoingCount;
                dr["Miss Call"] = misscallCount;
                oData.Rows.Add(dr);
            }
            dgList.DataSource = oData;
            MessageBox.Show("Job Done");
        }

        public List<PhoneData> GetPhoneData(string strFileName)
        {
            return File.ReadLines(strFileName)
                .Skip(1)
                .Where(s => s != "")
                .Select(s => s.Split(new[] { ',' }))
                .Select(a => new PhoneData
                {
                    Call_Start = DateTime.Parse( a[0]),
                    call_duration = TimeSpan.Parse(a[1]),
                    Ring_duration = int.Parse(a[2]),
                    direction = a[4],
                    Is_Internal =Convert.ToInt32( a[8]),
                    continuation = int.Parse( a[10]),
                    party1name = a[13],
                    Caller = a[3],
                    Park_Time = Convert.ToInt32(a[16]),
                    Called_number = a[5]
                })
                .ToList();
        }
    }

    public class PhoneData
    {
        public DateTime Call_Start { get; set; }
        public TimeSpan call_duration { get; set; }
        public Int32 Ring_duration { get; set; }
        public string direction { get; set; }
        public Int32 Is_Internal { get; set; }
        public Int32 continuation { get; set; }
        public string party1name { get; set; }
        public string Caller { get; set; }
        public Int32 Park_Time { get; set; }
        public string Called_number { get; set; }
    }
}
9
  • 1
    I would look at your csv, but after playing some stupid how many animals are there, i lost interest in trying to get through ads to get it. Commented May 3, 2016 at 14:45
  • i gave a link for csv file and when u click on that link then csv will download. i just do not understand what is so stupid things here? Commented May 3, 2016 at 14:47
  • 2
    that csv sure looks like it has sensitive information. Commented May 3, 2016 at 14:47
  • 1
    The download link for me wasnt a direct download link, it pulled up a screen with click to download for free, on clicking took me to some how many animals are there.. I thought it was a capture thing, then an ad.. After that.. I lost interest. Commented May 3, 2016 at 14:49
  • 1
    Doesnt on my pc, takes me off as described. Commented May 3, 2016 at 15:17

2 Answers 2

3

Why not just use CSVhelper to parse the file to a list and then just do the necessary LINQ on the list?

Here is the Documentation to read the CSV.

Sign up to request clarification or add additional context in comments.

Comments

1

I checked your code, you must change the index of column Party1Name, because the column position is 12 and not 13.

So the conditions on this column are wrong

party1name = a[12]

5 Comments

column position start from 1 and it should be 13 not 12. better come with reason why do u think it should be 12?
But why you start with 0 in Call_Start = DateTime.Parse( a[0])
You can assign the data source oPhoneData to gridview and you can see the result, the data in the column Party2Device on the csv file that are displayed on the Party1Name column of oPhoneData.
Sorry you are right. i made the mistake. yes party1name should be taken from 12 position.
Good, I am glad to hear that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.