I’m a membership organiser with a community union and spend an inordinate amount of my time prepping excel spreadsheets with membership data. Is there anything out there I could use to split the entire data set into sheets with tables of, say, 20 members each, their contact details, plus boxes for whether or not they can attend this or that event?

  • ☭ 𝗚𝗿𝗮𝗶𝗻𝗘𝗮𝘁𝗲𝗿 ☭
    2•2 years ago

    @Shaggy0291@lemmygrad.ml I made a script that should work for this (with placeholder columns for now, but I can fix that if you tell me what columns you want, or I could just tell you what to change). You need a Unix shell interpreter, either locally (e.g. the Bash interpreter included with Git on Windows) or through some online service (although I wouldn’t recommend that since you’d presumably be uploading sensitive data as input).

    shell script 
    #!/bin/sh

add_header() {
    name=sheet$sheet.csv
    [ -f $name ] && echo "File '$name' already exists." && exit 1
    printf 'NAME,PHONE NUMBER,SOME COLUMN,SOME OTHER COLUMN\n' > $name
}

print_sheet() {
    echo "### SHEET $sheet ###"
    cat sheet$sheet.csv
    echo
}

count=1
sheet=1
add_header
IFS=''; while read line; do
    printf '%s,,\n' $line >> sheet$sheet.csv
    if [ $count = 20 ]; then
	count=0
	print_sheet
	sheet=$(expr $sheet + 1)
	add_header
    else
	count=$(expr $count + 1)
    fi
done

print_sheet

    If you copy this to a file process.sh and you have input in some file input.csv, you can run cat input.csv | sh ./process.sh and it’ll create files sheet1.csv, sheet2.csv, etc., for each group of 20 rows

      • ☭ 𝗚𝗿𝗮𝗶𝗻𝗘𝗮𝘁𝗲𝗿 ☭
        2•2 years ago

        It’s just software that reads a script (like what I posted) and executes the corresponding instructions. The most popular (AFAIK) interpreter is Bash.

        If you’re using Windows 10-11 (unless it’s a 32-bit version), you can either use the built-in Bash through the “Windows Subsystem for Linux” (instructions on how to enable it here; I’ve never tried it, though, and it installs a lot more than just Bash), or install something like Git, which includes a version of Bash. I’d recommend the latter (you can just download the “thumbdrive edition” to avoid installing anything).

        If you’re using MacOS (or whatever it’s called now), it’s a lot easier since it’s partially based on Unix (just use the built-in terminal emulator).

        Let me know when you’ve got Bash working (or if something went wrong)