preprocess

#!/usr/bin/env bash

# ------------------------------------------------------------------------------
# Author: <Ryan Chang>
# Script Name: preprocess
# Description: Preprocess a raw CSV file by cleaning up formatting issues.
#              This includes convert the semicolon separator to the  <tab> character, Convert
#              the Microsoft line endings to Unix line endings, Change format of floating-point numbers. 
#              The cleaned output is printed to stdout.
# Usage: ./preprocess <file>
# ------------------------------------------------------------------------------

# Check the number of arguments
# If the number is not 1, print error message
if [[ $# != 1 ]]
then
    echo "Error: The number of arguments does not match" >&2
    exit 1
fi

file="$1"
sep=";"

# Check file existence and permissions
if [[ ! -f "$file" ]]; then
    echo "Error: File '$file' is not found." >&2
    exit 1
fi
if [[ ! -r "$file" ]]; then
    echo "Error: File '$file' is not readable." >&2
    exit 1
fi

# Create a temp file to store the cleaned file
cleaned_file=$(mktemp)

# Data cleaning
# 1. Convert the semicolon separator to the  <tab> character (replace separator with '\t')
# 2. Convert the Microsoft line endings to Unix line endings (remove '\r')
# 3. Deal with non-ASCII characters by deleting them from the output.
# 4. Change format of floating-point numbers to use '.' rather than ',' as the decimal point.
sed -e "s/$sep/$(printf '\t')/g" < "$file" \
| tr -d '\r' \
| tr -cd '\11\12\15\40-\176\n' \
| gawk -F '\t' -v OFS='\t' '
    NR == 1 { print; next }
    {
        for (i = 1; i <= NF; i++) {
            val = $i
            if (i == 9 || i == 11) {
                $i = ""
                for (j = 1; j <= length(val); j++) {
                    c = substr(val, j, 1)
                    $i = $i ((c == ",") ? "." : c)
                }
            }
        }
        print 
    }' > "$cleaned_file"

# Find the max ID number
maxid=$(
    gawk -F '\t' '
    NR > 1 && $1 ~ /^[0-9]+$/ {
        id = int($1)
        if (id > maxid) maxid = id
    }
    END {
        print maxid
    }
    ' "$cleaned_file"
)

# Check if the max ID is greater than 0
if [[ $maxid -le 0 ]]; then
    echo "Error: The max ID number is not greater than 0." >&2
    exit 1
fi

# Filling the missing ID
# Assume that ID is the first column
gawk -F '\t' -v OFS='\t' -v maxid="$maxid" '
NR == 1 { print; next }
{
    if ($1 ~ /^[ \t]*$/) {
        $1 = ++maxid
    }
    print
}
' "$cleaned_file"

# Remove the cleaned temporary file
rm -f "$cleaned_file"

# Exit successfully
exit 0