Topic
• Latest Post - ‏2019-05-29T19:45:20Z by Mike.Scharnow
Christopher Cote
68 Posts

# Pinned topic Sort a column within Excel

‏2019-05-29T18:38:27Z |

I have a script that creates an Excel file that gives the number of times each object attribute.  In addition, my customer would like to have the list of attributes to be sorted in descending order based on the number of times it's used, but I haven't been able to find anything through Google on how to do this.  I'm sure it's probably a single line of code that, when you have all the data in the file, you can then sort on a specific column in either ascending or descending order.  I just don't know what that function is.  Any help would be appreciated.

Chris

• Mike.Scharnow
589 Posts

#### Re: Sort a column within Excel

‏2019-05-29T19:45:20Z

Hello Chris,

just an idea:

There is a sort perm in DXL, which sorts arrays alphabetically, not numerically.

```void sort(string stringArray[])
```

To do a numerical sort of an integer, you could pad it with zeroes  (alphabetically sorted, 11 is less than 2, but 00011 is greater than 00002)

So, assuming that you have counted the object attribute occurrences in a Skip list with Key=Attribute name, Value=count of occurrences.

Then you could create an array from the skip list of which each entry is of CSV format (values seperated by comma) sort it and print the sorted array

Something like this:

```/**
* Created by: MSC
* Creation date: 29.05.2019
*
* DXL Editor is provided by SODIUS (www.sodius.com)
*/
pragma encoding, "UTF-8"

/*!
* \param[in] s String to possibly pad with zeros.
* \return    String padded to length 8 with zeros.
* \brief     Pads "s" with zeros if less than length 8.
*/
int len = length s
if (len == 7) return "0" s
if (len == 6) return "00" s
if (len == 5) return "000" s
if (len == 4) return "0000" s
if (len == 3) return "00000" s
if (len == 2) return "000000" s
if (len == 1) return "0000000" s
if (len == 0) return "00000000"
return s
}

// KEY: name of attribute (string) VALUE: number of occurrences (int)
Skip sk = createString()

put (sk, "Attribute A", 9)
put (sk, "Attribute B", 11)
put (sk, "Attribute C", 2)

// calculate the number of entries, create an array of the same size
int iTotals=0
for iCount in (sk) do {iTotals++}
string sSort[iTotals]

// put each entry onto the array
int i = 0
int iCount
for iCount in (sk) do {
string sAttrName = (string key sk)
string sEntry = (pad8(iCount"")) "," sAttrName
sSort[i] = sEntry
i++
}

sort sSort

Stream stm = write ("C:\\temp\\values.csv")
for (i=0;i<iTotals;i++){
print sSort[i] "\n"
stm << sSort[i] "\n"
}
close stm
```

Result of this script:

```[progress] Executing /DXL-Library/src/x.dxl
00000002,Attribute C
00000009,Attribute A
00000011,Attribute B

[progress] Done.

```

Updated on 2019-05-31T18:23:15Z at 2019-05-31T18:23:15Z by Mike.Scharnow