I have had this tool lying around since 2014. I wrote it once for a business that needed to convert the plain-text .OUT files from Sage PRO into CSV format. It isn’t a super smart script; it only converts the formatting so that the file can be opened in a program like LibreOffice Calc or Microsoft Excel. One .OUT file can have information for lots of accounts, so it doesn’t even bother trying to split up the accounts, though it’s easy to do by hand if desired. I don’t know if this will work with newer versions of PRO or with reports different from the kind I wrote it against. It is offered as-is, no warranty, use at your own risk, don’t blame me if the output gets you a call from the IRS.
If this is useful to you, please leave a comment and let me know! The company I did this for ended up not even using the product of my hard work, so just knowing that anyone at all found this useful will make me very happy.
To use this, you’ll need to give it the name of the .out file you want it to process. Also, this was written when my shell scripting was still a little unrefined…please don’t judge too harshly 🙂
Click here to download the Sage PRO to CSV shell script.
#!/bin/sh
# Convert Sage PRO exported text to CSV text format
# Copyright (C) 2014-2020 by Jody Bruchon <jody@jodybruchon.com>
# Distributed under The MIT License
# Distributed AS-IS with ABSOLUTELY NO WARRANTY. Use at your own risk!
# Program synopsis:
# Converts a Sage PRO ".out" text file to CSV for use as a spreadsheet
# OUT files are generally fixed-width plain text with a variety of
# header and footer information.
# The general process of converting them to CSV text is as follows:
# - Read each line in the file
# - Skip lines that aren't part of the financial data
# - Skip irrelevant page/column headers and any empty lines
# - Read the account number/name information header
# - Consume columns of transaction data in order; convert to CSV data
# - Ignore account/grand totals and beginning balance fields
# - Loop through all the lines until input data is exhausted
# This script has only been tested on a specific version of Sage PRO
# and with one year of financial data output from one company. It may
# not work properly on your exported data, in which case you'll need
# to fix it yourself.
# ALWAYS ***ALWAYS*** CHECK OUTPUT FILES FOR CORRECTNESS. This script
# will throw an error if it encounters unexpected data; however, this
# does not always happen if the data appears to conform to expected
# input data ordering and formatting. For example, financial data is
# assumed to be fixed-width columns and the data is not checked for
# correct type i.e. a valid float, integer, or string.
echo "A tool to convert Sage PRO exported text to CSV text format"
echo "Copyright (C) 2014-2020 by Jody Bruchon <jody@jodybruchon.com>"
echo "Distributed under The MIT License"
echo -e "Distributed AS-IS with ABSOLUTELY NO WARRANTY. Use at your own risk.\n"
if [ ! -e "$1" ]
then echo "Specify a file to convert."
echo -e "\nUsage: $0 01-2014.out > 01-2014.csv\n\n"
exit 1
fi
SKIP=0 # Number of lines to skip
LN=0 # Current processing ine number
TM=0 # Transaction output mode
HEADERS='"Tran Date","Source","Session","Transaction Description","Batch","Tran No","Debit Amt.","Credit Amt.","Ending Bal."'
# Column widths
C1=8 # Tran Date
C2=2 # Source (initials
C3=9 # Session
C4=23 # Transaction Description
C5=9 # Batch
C6=6 # Tran No
C7=26 # Debit Amt.
C8=20 # Credit Amt.
C9=18 # Ending Bal.
CMAX=9 # Number of columns
pad_col () {
X=$(expr $CMAX - $1)
while [ $X -gt 0 ]
do echo -n ","
X=$((X - 1))
done
echo
}
consume_col () {
# Read next item in line
CNT=$(eval echo \$C$Z)
#echo CNT $CNT
I="$(echo -E "$T" | sed "s/\\(.\{$CNT\}\\).*/\"\1\",/")"
T="$(echo -E "$T" | sed "s/^.\{$CNT\} //")"
# Strip extraneous spaces in fields
if [ $Z != 4 ]
then I="$(echo -E $I | sed 's/^ *//;s/ *$//')"
fi
echo -n "$I"
}
while read -r LINE
do
# Count line numbers in case we need to report an error
LN=$((LN + 1))
# Handle line skips as needed
if [ $SKIP -gt 0 ]
then SKIP=$((SKIP - 1))
continue
fi
# Strip common page headers (depaginate)
if echo "$LINE" | grep -q "^Page:"
then SKIP=7
continue
fi
# Strip standard column headers
if echo "$LINE" | grep -q "^Tran Date"; then continue; fi
if echo "$LINE" | grep -q "^Account Number"; then continue; fi
# Don't process totally empty lines
if [ -z "$LINE" ]; then continue; fi
# Pull account number and name
if echo "$LINE" | grep -q '^[0-9]\{5\}'
then
ACCT="$(echo -E "$LINE" | cut -d\ -f1)"
ACCTNAME="$(echo -E "$LINE" | sed 's/ */ /g;s/^ *//' | cut -d\ -f2-)"
pad_col 0
echo -n "$ACCT,\"$ACCTNAME\""; pad_col 2
continue
fi
# Sometimes totals end up on the previous line
if echo -E "$LINE" | grep -q '^[0-9][0-9][^/]'
then LL="$LINE"
continue
fi
if echo -E "$LINE" | grep -q '^\$'
then LL="$LINE"
continue
fi
if [ ! -z "$LL" ]
then LINE="$LINE $LL"
unset LL
fi
if echo "$LINE" | grep -q "Beginning Balance"
# then BB="$(echo -E "$LINE" | awk '{print $3}')"
# echo -n "\"Begin Bal:\",$BB"; pad_col 2
# pad_col 0
then
TM=1; AT=0
echo "$HEADERS"
continue
fi
if echo "$LINE" | grep -q '^[0-9][0-9]/[0-9][0-9]/[0-9][0-9]'
then if [ $TM -eq 1 ]
then
T="$LINE"
Z=0
while [ $Z -lt $CMAX ]
do
Z=$((Z + 1))
consume_col
done
echo
continue
else echo "error: unexpected transaction" >&2
exit 1
fi
fi
# Handle account totals line
if echo "$LINE" | grep -q "^Account Total:"
then TM=0; AT=1
continue
fi
if echo "$LINE" | grep -q "^Begin. Bal."
then if [ $AT -eq 1 ]
then
echo -n '"Begin Bal",'
T="$(echo -E "$LINE" | sed 's/Begin[^$]*//;s/\$ */$/g;s/\$/"$/g;s/ Net Change: */","Net Change/g;s/\$/,"$/g;s/$/"/;s/ *//g;s/^",//')"
T2="$(echo -E "$T" | cut -d\" -f1-7)"
T3="$(echo -E "$T" | cut -d\" -f7-)"
echo $T2,$T3
continue
else
echo "error: unexpected totals line" >&2
fi
fi
if echo "$LINE" | grep -q "^Grand Total:"
then
pad_col 0; pad_col 0
echo '"Grand Total"'; pad_col 1
continue
fi
# Output error (unknown line)
echo "ERROR: Unknown data while processing line $LN" >&2
echo -E "$LINE" >&2
exit 1
# echo -E "$LINE"
done < "$1"
