Programming

Python script to parse the GAM / Google Workspace CSV that contains all members of all groups

 in Google / Programming / Python  tagged csv / dataframe / google / google workspace / panda / python / to_excel / xlsx / xlsxwriter by

This script will parse the data generated by GAM and create an XLSX out of them.

Each sheet will be named by the group, containing all the emails of that group, including the sub-groups.

emails.py_Download
# This script will parse the data generated by GAM and create an XLSX out of them.
# Each sheet will be named by the group and it will contain all the emails of that group, including the sub-groups.

# Using sys to get command line arguments for the input file
import sys
# Using CSV to parse the input CSV file that GAM (GAMADV-XTD3) created after using the following command:
# gam print group-members > emails.2022.csv;
# Source: https://bytefreaks.net/google/rough-notes-on-using-gam-to-print-all-members-of-all-groups-in-google-workspace
import csv
# Using pandas to create an XLSX file with multiple sheets
import pandas as pd

# Creating an empty dictionary.
dictionary = {}

# Opening the CSV file that is the first command line argument
with open(sys.argv[1], newline='') as csvfile:
    reader = csv.reader(csvfile, delimiter=',', quotechar='"')
    # For each row, we are getting only the first column which is the group and the last which is the email
    for row in reader:
        dictionary.setdefault(row[0], []).append(row[5])

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter(sys.argv[1]+'.xlsx', engine='xlsxwriter')

# Iterating in Sorted Order
for key in sorted(dictionary):
    # Create a Pandas dataframes to add the data.
    df = pd.DataFrame({'Members': dictionary[key]})
    # Write each dataframe to a different worksheet.
    # To avoid the following exception:
    # xlsxwriter.exceptions.InvalidWorksheetName: Excel worksheet name '[email protected]' must be <= 31 chars.
    # We are truncating the domain from the group.
    group = key.split("@")[0]
    # In case the name is still to big, we truncate it further and append an ellipsis to indicate the extra truncation
    sheet = (group[:29] + '..') if len(group) > 31 else group
    # We are also removing the header and index of the data frame
    df.to_excel(writer, sheet_name=sheet, header=False, index=False)

# Close the Pandas Excel writer and output the Excel file.
writer.close()
emails.py_Download

C++ Source code to encrypt and decrypt the Ceasar cipher

 in C++ / Programming  tagged C++ / Ceasar / Cipher / cpp / decrypt / encrypt by

The following code accepts one line from the standard input and uses the functions encrypt and decrypt to break the Ceasar cipher when the key is known. In this example, the key was the number 12, the day of birth of Julius Ceasar.

[download id=”11738″]

#include <iostream>
#include <string> 

using namespace std;

#define OFFSET_SMALL (97)
#define OFFSET_BIG (65)
#define LATIN_CHARACTERS (26)

// Text and offset for the shift are sent into this function, which returns the text's encrypted version.
string encrypt(string text, int offset)
{
    string result = "";

    for (long unsigned int i = 0; i < text.length(); i++)
    {
        if (std::isalpha(text[i]))
        {
            if (std::islower(text[i]))
            {
                result += char (int(text[i] + offset - OFFSET_SMALL) % LATIN_CHARACTERS + OFFSET_SMALL);
            }
            else
            {
                result += char (int(text[i] + offset - OFFSET_BIG) % LATIN_CHARACTERS + OFFSET_BIG);
            }
        }

        else
        {
            result += text[i];
        }
    }

    return result;
}

// Text and offset for the shift are sent into this function, which returns the text's decrypted version.
string decrypt(string text, int offset)
{
    return encrypt(text, LATIN_CHARACTERS - offset);
}

int main() {
    string encrypted;
    getline(cin, encrypted);

    // Sample execution showing how to decrypt an already encrypted message.
    // Julius Ceasar was born on July 12, 100BC
    cout << decrypt(encrypted, 12);

    return 0;
}

[download id=”11738″]

.

Side note:

While authoring this post, we found a peculiar bug in the plugin named “SyntaxHlighlighter Evolved.”

Specifically, when we added the string char( in our C++ source code, we would get the following error back:

Updating failed. The response is not a valid JSON response.

To mitigate the problem, we added a space character between the word char and the open parenthesis as follows char (.

An example of MySQL code that executes TRIM() to remove a prefix and/or a suffix from all entries that match a WHERE clause

 in Programming / SQL  tagged MariaDB / mysql / prefix / suffix / trim by

The following code will remove the prefix http://wow.example.com from all rows that match the where clause:

Update `my_table`
set
`my_column` = TRIM(
  LEADING 'http://wow.example.com'
  FROM `my_column`)
WHERE (`my_column` LIKE '%http://wow.example.com/%');

The next block will remove the suffix index.php from all entries that match the where clause:

Update `my_table`
set
`my_column` = TRIM(
  TRAILING 'index.php'
  FROM `my_column`)
WHERE (`my_column` LIKE '%/index.php%');

In case we need to remove the string needle both from the prefix and the suffix while using a where clause, we can use the following code:

Update `my_table`
set
`my_column` = TRIM(
  BOTH 'needle'
  FROM `my_column`)
WHERE (`my_column` LIKE '%needle%');