c# sort a CSV file by date/time field

Hey,
I have a CSV file -
string file = "somefile.csv"

Open in new window

It contains data like this -
09/09/2013 07:13:37,1,Test2
09/09/2013 04:24:00,0,Test2
09/09/2013 20:52:41,0,Test2
09/09/2013 09:08:33,0,Test2
09/09/2013 06:10:05,1,Test2

Open in new window

In my C# application, I need to sort the file by the first column date/time.

Can anyone suggest the best method?
mhdiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

käµfm³d 👽Commented:
You could use LINQ for this.

e.g.

DateTime d = DateTime.MinValue;

var query = from line in File.ReadLines(@"C:\path\to\your\file.csv")
            let fields = line.Split(',')
            let dateParsed = DateTime.TryParse(fields[0], out d)
            orderby dateParsed, d
            select line;

List<string> sortedLines = query.ToList();

Open in new window

0
mhdiAuthor Commented:
Hi,

That looks ideal, however the output is the same as the input. No records are sorted.

Is something missing?
0
käµfm³d 👽Commented:
Add in one more line:

let dateObject = dateParsed ? d : DateTime.MinValue

e.g.

var query = from line in File.ReadLines(@"C:\path\to\your\file.csv")
            let fields = line.Split(',')
            let dateParsed = DateTime.TryParse(fields[0], out d)
            let dateObject = dateParsed ? d : DateTime.MinValue
            orderby dateParsed, dateObject
            select line;

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

mhdiAuthor Commented:
Same result unfortunately.

string file = @"test.csv";
string fileName = Path.GetFileName(file);

DateTime d = DateTime.MinValue;

var query = from line in File.ReadLines(file)
            let fields = line.Split(',')
            let dateParsed = DateTime.TryParse(fields[0], out d)
            let dateObject = dateParsed ? d : DateTime.MinValue
            orderby dateParsed, d
            select line;

List<string> sortedLines = query.ToList();
File.WriteAllLines(@"sorted\test.csv", sortedLines);

Open in new window

0
Fernando SotoRetiredCommented:
Hi mhdi;

I beelieve the issue with @kaufmed solution is that he is capturing an external variable and using it in the query which has side effects. I have modify his code to work. The code now only uses the external variable as an output variable and does not use it for anything else.

DateTime testDate = DateTime.MinValue;

var query = from line in File.ReadLines("C:/Working Directory/TestData.csv")
            let fields = line.Split(',')
            let dateParsed = DateTime.TryParse(fields[0], out testDate) == true ? DateTime.Parse(fields[0]) : DateTime.MinValue
            orderby dateParsed
            select line;

List<string> sortedLines = query.ToList();

Open in new window


Also any line in the csv file that has a invalid date will end up in the front of the list.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mhdiAuthor Commented:
Perfect.

Thanks to both of you. I better study LINQ now...
0
mhdiAuthor Commented:
Is it possible to use this method to remove duplicate records?

EDIT: This works..

File.WriteAllLines(fileName, sortedLines.Distinct().ToArray());
0
Fernando SotoRetiredCommented:
When you say duplicate do you mean all fields have the same values with the same number of spaces and capitalization? If so you can doing the following.


var query = (from line in File.ReadLines("C:/Working Directory/TestData.csv")
             let fields = line.Split(',')
             let dateParsed = DateTime.TryParse(fields[0], out testDate) == true ? DateTime.Parse(fields[0]) : DateTime.MinValue
             orderby dateParsed
             select line).Distinct();

Open in new window

0
käµfm³d 👽Commented:
I also changed line 5 in my last post, but I didn't mention it. That is why your updated version was not working. The side effects that FernandoSoto mentions were accounted for in that post.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.