0
$\begingroup$

Preferable without reading and then rewriting an excel(vs the COMLink) file how might I sort an excel file by Natural Order based on a column? This question is similar although probably not as complex.

In addition how can I ignore a heading like so?

Heading
Ie4 01
Ie4!01
Ie4_01
Ie4_128
Ie5
Ie6
Ie401sp2
Ie501sp2

EDIT: I have added a bounty although I'm okay with this question being closed if it is to broad.

In addition I will add another 200 bounty rep if this question is answered although I would prefer not having bounty points disappearing for being unanswered.

$\endgroup$
  • 2
    $\begingroup$ I have a hard time understanding what this has to do with Mathematica. Can't you do it in Excel using the same solution offered in the linked thread? $\endgroup$ – Oleksandr R. Jan 8 '16 at 21:48
  • $\begingroup$ @OleksandrR. I meant to leave a footnote that this might be better fit for another or no site at all. Mathematica is simply used as an aid. You might be able to use visual basic but otherwise I will be surprised if Excel can do such. Here is a similarish question. mathematica.stackexchange.com/questions/38891/… $\endgroup$ – William Jan 8 '16 at 21:51
  • $\begingroup$ Well, you'll need to write basically the same script regardless of whether you do it in Mathematica or VBA. I can't see how introducing Mathematica helps you here; it just adds extra layers of indirection and another dependency. SuperUser is probably a reasonable place for this question. $\endgroup$ – Oleksandr R. Jan 8 '16 at 22:00
  • $\begingroup$ @OleksandrR. I feel like the question will get more attention here especially once I add a bounty here. I am going to delete it for now and reopen with bounty if the superuser gets no interest. Also VBA might be easier. Although I despise VBA VBA is often not even included in programming language lists for various reasons because many people don't consider it a language. superuser.com/questions/1023726/sort-excel-file-natural-order $\endgroup$ – William Jan 8 '16 at 22:11
  • $\begingroup$ I think it will be difficult to attract attention on SuperUser if you just repost the exact same question complete with the hastily constructed first sentence and Mathematica context. Try to find some relevant questions on SU and link to those instead, and remember to use the proper tags (e.g.: vba rather than mathematica), if you want to enthuse the SU community. $\endgroup$ – Oleksandr R. Jan 8 '16 at 22:23
5
+50
$\begingroup$

Let's walk through this solution to see how it works. First, we define some groups of characters. The characters that are in spChar list are characters like "!" and "_". They will be sorted in the order they appear in the list. This does not include all the special characters, because I do not know what order they should have. The characters in the numChar list are numeric characters. They will be sorted numerically instead of in dictionary order. The last group is alphaChar, the alphabetic characters. They will be sorted in the alphabetic order, ignoring case.

spChar = {" ", "!", "_"};
numChar = Characters["0123456789"];
alphaChar = Join[CharacterRange["A", "Z"],
    CharacterRange["a", "z"]];

Now we define a function that classifies the characters in a string. The function returns a list of integers: 1 for a special character, 2 for a digit, 3 for an alphabetic character, and zero for unknown characters.

classify::unknownchar = 
  "Unknown character `1`.  Please add to spChar list in the correct order.";

classify[c_String /; StringLength[c] == 1] :=
 Which[
  MemberQ[spChar, c], 1,
  MemberQ[numChar, c], 2,
  MemberQ[alphaChar, c], 3,
  True, Message[classify::unknownchar, c]; 1]

classify[str_String] := Map[classify, Characters@str]

Now define functions to compare strings composed of characters all of the same type. If the characters are special, the comparison is based on the order given in the spChar list. If the characters are numeric, convert the strings to numbers and compare numerically. If the characters are alphabetic, convert to upper case and compare. If the strings are identical, return 0. If the strings are in the proper order, return -1 and if the strings need to be swapped, return 1.

cmpSpecial[str1_String, str2_String] := Block[
  {c1, c2, cmp = 0, p1, p2, s1 = str1, s2 = str2},
  While[cmp == 0 && StringLength[s1] > 0 && StringLength[s2] > 0,
   c1 = StringTake[s1, 1]; c2 = StringTake[s2, 1];
   p1 = First@Flatten@Position[spChar, c1];
   p2 = First@Flatten@Position[spChar, c2];
   cmp = Sign[p1 - p2];
   s1 = StringDrop[s1, 1]; s2 = StringDrop[s2, 1];
   ];
  If[cmp == 0 , cmp = StringLength[s1] - StringLength[s2]];
  cmp
  ]

cmpNumeric[str1_String, str2_String] := Block[{cmp},
  cmp = ToExpression[str1 <> "-" <> str2];
  If[cmp == 0, cmp = StringLength[str2] - StringLength[str1]];
  Sign[cmp]
  ]

cmpAlpha[str1_String, str2_String] := Block[
  {s1 = ToUpperCase[str1], s2 = ToUpperCase[str2]},
  Which[str1 == str2, 0,
   s1 == s2 , If[OrderedQ[{str1, str2}], -1, 1],
   True, If[OrderedQ[{s1, s2}], -1, 1]
   ]
  ]

Now define a function that examines two strings and compares them according to their substrings. The numbers get sorted behind the special characters and in front of the alphabetic characters.

compare::badtype = "Invalid type value `1`.";

compare[str1_String, str2_String] := Block[{
   class1 = SplitBy@classify[str1], class2 = SplitBy@classify[str2],
   cmp = 0, f1, f2, n1, n2, s1 = str1, s2 = str2, type1, type2},
  While[cmp == 0 && Length[class1] > 0 && Length[class2] > 0,
   type1 = First@Flatten[First@class1];
   type2 = First@Flatten[First@class2];
   cmp = Sign[type1 - type2];
   If[cmp == 0,
    n1 = Length[First@class1]; n2 = Length[First@class2];
    f1 = StringTake[s1, n1]; f2 = StringTake[s2, n2];
    cmp = Switch[type1,
      1, cmp = cmpSpecial[f1, f2],
      2, cmp = cmpNumeric[f1, f2],
      3, cmp = cmpAlpha[f1, f2],
      _, Message[compare::badtype, type1]; 0
      ];
    If[cmp == 0, class1 = Drop[class1, 1]; class2 = Drop[class2, 1];
     s1 = StringDrop[s1, n1]; s2 = StringDrop[s2, n2]]
    ]
   ];
  If[cmp == 0, cmp = Sign[Length[class1] - Length[class2]]];
  Not[cmp == 1]
  ]

Now define a function ssSort that uses the above comparison function to sort a single Excel spreadsheet. This function takes 3 arguments: the spreadsheet data array, the index of the column to sort on and the number of rows to skip for headings. If there are headings, the last argument can be omitted. If there are no headings and we want to sort on the first column, then only the first argument is required.

ssSort[
  data_List /; ArrayDepth[data] == 2,
  nColumn_Integer /; nColumn > 0,
  nSkip_Integer /; nSkip >= 0] :=
 If[nSkip == 0, Sort[data,
   compare[Part[#1, nColumn], Part[#2, nColumn]] &],
  Join[data[[;; nSkip]],
   Sort[data[[nSkip + 1 ;;]],
    compare[Part[#1, nColumn], Part[#2, nColumn]] &]]]

ssSort[
  data_List /; ArrayDepth[data] == 2,
  nColumn_Integer /; nColumn > 0] := ssSort[data, nColumn, 0]

ssSort[data_List /; ArrayDepth[data] == 2] := ssSort[data,1,0]

We are all set and ready to test it. We read the first spreadsheet of an Excel workbook like this: ss = First@Import["tmp.xls"]; But let's use the following test data instead. We can always export this data to an Excel workbook to make a more complete test.

ss = {
{"Heading 1", "Heading 2", "Heading 3"},
{"Col 1 row 1", "Ie4 01", "Col 3 row 1"},
{"Col 1 Row 7", "Ie4_128", "Col 3 Row 2"},
{"Col 1 row 3 ", "Ie5", "Col 3 row 3 "},
{"Col 1 row 4", "Ie4!01", "Col 3 row 4"},
{"Col 1 row 5 ", "Ie501sp2", "Col 3 row 5 "},
{"Col 1 row 6 ", "abC", "Col 3 row 6 "},
{"Col 1 row 2 ", "Ie6","Col 3 row 7 "},
{"Col 1 row 8 ", "abcde","Col 3 row 8 "},
{"Col 1 row 9 ", "Ie401sp2", "Col 3 row 9 "},
{"Col 1 row 10 ", "Ie4_01","Col 3 row 10 "},
{"Col 1 row 11 ", "abc", "Col 3 row 11 "}};

Let's display the unsorted data, then set the sort index to 2 and the number of heading rows to 1, then display the sorted data:

ss//TableForm
ndxSortColumn = 2;
nSkipRows = 1;
ssSort[ss, ndxSortColumn, nSkipRows] // TableForm

So, that's it. The spreadsheet rows are sorted in the "natural order" given in the original question. However, there are other special characters that still need to be added to spChar list in this code.

One final note about reading Excel spreadsheets. We can read the workbook like this wkb=Import["tmp.xls"];ArrayDepth[wkb]. This should return 3 which means there are one or more spreadsheets in the workbook. We can take the second spreadsheet like this ss2=wkb[[2]]; ArrayDepth[ss2]. This should return 2, meaning the spreadsheet has both rows and columns. This is important, because if we try pass an entire workbook to ssSort instead of just a spreadsheet, ssSort should return unevaluated.

$\endgroup$
  • $\begingroup$ I suggest replacing If[cond1,If[cond2,If[cond3]]] with Which[] or Switch[] $\endgroup$ – xyz Jan 19 '16 at 8:21
  • $\begingroup$ @ShutaoTANG: Yes, that's a good idea. I see 2 places where Which[] or Switch[] would be better. There may be more. I almost never think use Which[] or Switch[] in my code. Also, completely ignoring case when comparing alphabetic is not exactly right, so that is a bug that I also need to fix. Hopefully I'll collect a few more beneficial comments like yours and put them all in during the same edit. Thanks for your useful contribution. $\endgroup$ – LouisB Jan 19 '16 at 20:53
  • $\begingroup$ I gave you the bounty because it is about to expire although you didn't use COMLink which was the intent of the question. I'm trying create an answer myself. $\endgroup$ – William Jan 19 '16 at 22:10
  • $\begingroup$ @William: Hey, that's pretty funny! I had actually read the original question as specifying "without reading ... via the COMLink". Of course, now that you have pointed it out, I see it say "vs" the COMLink. Not the first time I've misread a question. Please share your thoughts. How do you envision the solution? What, exactly, is the COMLink that you want to use? $\endgroup$ – LouisB Jan 20 '16 at 2:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.