Topic
  • 1 reply
  • Latest Post - ‏2019-05-29T19:45:20Z by Mike.Scharnow
Christopher Cote
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
    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.
    */
    string pad8(string s) {
        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