Sort data like in excel

Question

1.14/5 (4 votes)

See more:

Hello everyone.
I have data in the CSV file.

ELBO;ELBO-17375;90;80;4;0;1,5;109
TUBI;TUBE-8732;0;150;6;0;11,54;184,01
ELBO;ELBO-17375;90;200;8;0;20;17
REDU;REDU-17378-K;0;200;10;8;7,2;2
ELBO;ELBO-17375;90;150;6;0;8,1;25
TUBI;TUBE-8732;0;25;3,5;0;2,15;40,11
ELBO;ELBOW-36-42-81;90;25;3,5;0;0,2;9
TUBI;TUBE-8732;0;200;8;0;41,63;153,06
TUBI;TUBE-8732;0;250;8;0;52,28;262,35
ELBO;ELBO-17375;90;250;8;0;31;25
TUBI;TUBE-8732;0;80;4;0;6,36;716,17

To sort the data in the correct order I am using Microsoft.Office.Interop.Excel
The data code is approximate.

C#

Range range = sheets["A1", string.Concat("L", count)];
 Range ranges = sheets[string.Concat("B1:B", count), Missing.Value];
 sheets.Sort.SortFields.Add(ranges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
 ranges = sheets[string.Concat("C1:C", count), Missing.Value];
 sheets.Sort.SortFields.Add(ranges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
 ranges = sheets[string.Concat("D1:D", count), Missing.Value];
 sheets.Sort.SortFields.Add(ranges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
 ranges = sheets[string.Concat("E1:E", count), Missing.Value];
 sheets.Sort.SortFields.Add(ranges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
 ranges = sheets[string.Concat("F1:F", count), Missing.Value];
 sheets.Sort.SortFields.Add(ranges, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
 sheets.Sort.SetRange(range);
 sheets.Sort.Orientation = XlSortOrientation.xlSortColumns;
 sheets.Sort.SortMethod = XlSortMethod.xlPinYin;
 sheets.Sort.Apply();

The output is as follows

ELBO	ELBO-17375	90	80	4	0	1,5	109
ELBO	ELBO-17375	90	150	6	0	8,1	25
ELBO	ELBO-17375	90	200	8	0	20	17
ELBO	ELBO-17375	90	250	8	0	31	25
ELBO	ELBOW-36-42-81	90	25	3,5	0	0,2	9
REDU	REDU-17378-K	0	200	10	8	7,2	2
TUBI	TUBE-8732	0	25	3,5	0	2,15	40,11
TUBI	TUBE-8732	0	80	4	0	6,36	716,17
TUBI	TUBE-8732	0	150	6	0	11,54	184,01
TUBI	TUBE-8732	0	200	8	0	41,63	153,06
TUBI	TUBE-8732	0	250	8	0	52,28	262,35

Sorting is sequentially one column after another.

Is it possible to do the same sort without excel?
Maybe there are some libraries for these sorts?

Thanks

Posted 16-Sep-13 2:27am

Serge from Minsk

Updated 16-Sep-13 2:44am

v3

Add a Solution

Comments

Maciej Los 16-Sep-13 8:36am

What you mean: Is it possible to do the same sort without excel?

ZurdoDev 16-Sep-13 8:41am

Yes, you could write a sorting algorithm yourself.

Serge from Minsk 16-Sep-13 8:45am

Help )
Give an example for a two-column

ZurdoDev 16-Sep-13 9:00am

google.com

Sergey Alexandrovich Kryukov 16-Sep-13 9:30am

Are those files small enough to fit in memory?
—SA

4 solutions

Solution 2

The easiest way to do it is to uses Link to objects.

C#

myContainer
   .OrderBy(x => x.FirstColumn)
   .ThenBy(x => x.SecondColumn);

Alternatively, you can define your own comparison operator that will compare first column and if there is a match it will compare next column until the answer is found or all comapred fields are identical.

The comparison function will then look like this:

C#

int Compare(T first, T second)
{
  int result = first.ColumnOne.Compare(second.ColumnOne);
  if (result == 0)
  {
    result = first.ColumnTwo.Comapre(second.ColumnTwo);
    if (result == 0)
    {
      result = ...
    }
  }
  return result;
}

To avoid deed nesting, you can uses early returns and the opposite condition.

Posted 16-Sep-13 3:02am

Philippe Mori

Comments

Sergey Alexandrovich Kryukov 16-Sep-13 9:29am

5ed.
—SA

Solution 3

I would suggest you to use OLEDB[^] drivers with T-SQL command like this:

SQL

SELECT Field1, Field2, ... FieldN
FROM MyFile.csv
ORDER BY Field1

How to: Use OleDb to import text files (tab, csv, custom)[^]
Read Text File (txt, csv, log, tab, fixed length)[^]

Posted 16-Sep-13 3:04am

Maciej Los

Comments

Sergey Alexandrovich Kryukov 16-Sep-13 9:30am

I suspect OP does not use a database at all, then it would be something else, which is not actually a problem.
—SA

Maciej Los 16-Sep-13 9:42am

Sergey, is it matter that OP doesn't use database? Or i misunderstand you? He can treat csv files as a "database".
Sorry for my stupid question, but i would to understand you well.

Sergey Alexandrovich Kryukov 16-Sep-13 9:53am

Maybe I'm missing something. You code sample is SQL, but if OP treats csv files as database, it may not be a relational database where SLQ can be used. At the same time, it's not a problem, say, to sort out the rows in memory...
—SA

Maciej Los 16-Sep-13 10:04am

OK, i got it... Thank you for clarification. You're right, the simplest answer is to sort out data in a memory.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CPallini · Accepted Answer · 2013-09-16T02:50:00

Solution 1

Of course it is possible.
You might, for instance, import the CVS file data into a (in-memory) DataTable and then sort it with a standard SQL query.

Posted 16-Sep-13 2:50am

CPallini

Comments

Serge from Minsk 16-Sep-13 8:54am

Example, please.

BulletVictim 16-Sep-13 8:59am

Another yet not so commonly used way is using a datagridview(depends on if he is using it). Select the csv data into the datagridview and then sort the datagridview

Sergey Alexandrovich Kryukov 16-Sep-13 9:28am

Commonly used? Well, if the DataGridView is not actually needed in UI, then it is a really bad idea. Who even told you that System.Windows.Forms is used?
—SA

BulletVictim 17-Sep-13 1:45am

Like I said. NOT SO COMMONLY USED, and DEPENDS ON IF HE IS USING IT

Sergey Alexandrovich Kryukov 17-Sep-13 1:54am

Perhaps I misread you, sorry and thanks. But I would not mention it, as we don't know what OP uses and what not (do we?). Still, I think this is a bad idea.
—SA

BulletVictim 17-Sep-13 2:13am

No problem. Its not the best option to do it I know, but it has worked for me on occasion when I could not sort my data differently from sql because the SP was used else where with different sorting. I just mentioned it as a last resort basically.

Sergey Alexandrovich Kryukov 17-Sep-13 2:27am

I see.
Thank you.
—SA

Sergey Alexandrovich Kryukov 16-Sep-13 9:27am

Right, a 5.
—SA

Serge from Minsk · Accepted Answer · 2013-09-19T00:06:00

Solution 4

Thank you all very much!

http://www.dotnetperls.com/dataview[^]

Posted 19-Sep-13 0:06am

Serge from Minsk

Updated 19-Sep-13 0:08am

v2