Different Ways to Generate Unique ID

We had a requirement to generate 32 character alpha numeric unique IDs. Many options came up - here is the summary of options we considered.

Java UUID:

With Java 1.5 UUID class was introduced. This would generate universally unique identifier (UUID). A UUID represents a 128-bit value. randomUUID() method in UUID class will generate 128-bit universally unique value every time the method is called. This is hash-based values separated by dashes. Java API available here: http://docs.oracle.com/javase/6/docs/api/index.html?java/util/UUID.html

Database Hash:

If system design and architecture allows the unique ID generation on database side, then database hash can be used to create unique IDs.

Composite Key in Database:

If there is a way to combine various non unique keys to form a primary key on database side, this might work as an option as well.

Apache Commons:

randomAlphanumeric(int) in RandomStringUtils class takes length as an arguments and generates alpha numeric values. API documentation is here: http://commons.apache.org/proper/commons-lang/javadocs/api-2.6/org/apache/commons/lang/RandomStringUtils.html

Conclusion:

What worked for us in first phase of implementation was an approach where we used the system provided columns to generate a composite key.

Later the requirement changed to use alphanumeric values at which point in time we used RandomStringUtils provided by Apache Commons. However, it came with an overhead to check the generated ID against the database. However, this didn't have impact on performance given the benchmarks. 

One click export from Excel to CSV

Many a times we want to modify and frequently export the content from Excel to CSV.

Here is a quick macro that anyone can use to export the data from Excel. 

The CSV file will be generated without any prompts. All the data on the current worksheet will be exported. The CSV will be generated in the directory where your excel file is placed. The name of the CSV combines the Excel File Name, the Worksheet Name and current date and time.

Here is the code:

Sub SaveAsCSV() 
    Dim fs As Object, a As Object, i As Integer, s As String, t As String, l As String, mn As String 
    Set fs = CreateObject("Scripting.FileSystemObject") 
    Set a = fs.CreateTextFile(Application.ActiveWorkbook.Path + "\" + Replace(ActiveWorkbook.Name, ".xlsm", "") + "_" + ActiveSheet.Name + "_" + Format(Now(), "yyyy-MM-dd-hh-mm-ss") + ".txt", True) 
      
    For r = 1 To Range("A65536").End(xlUp).Row 
        s = "" 
        c = 1 
        While Not IsEmpty(Cells(r, c)) 
            s = s & Cells(r, c) & "," 
            c = c + 1 
        Wend 
        a.writeline s 'write line 
    Next r 
    
End Sub

Hope this helps.