I recalled doing this for one of our integrations ( batch_056 ) and used the method mentioned by Charlie of having the owner of the table do it in Excel and extract it to a CSV file that I used for generating the cloverleaf table on an automated daily basis.
I agree with Charlie overall it was an easy way to approach it as opposed to other methods, plus we already had a TCL script that would do the heavy lifting if I just gave it some arguments.
Here is an example of the arguments and how I called the table_builder.tcl script.
Here is the calling setup ( iftp_056_build_table.ksh ) to get you a tangible example to help solidify the concept and the input args to possibly consider.
#!/usr/bin/ksh
cd $HCISITEDIR/Tables
InputFileName=/ftp/$MDA_BATCH_ENV/iftp_056/work/doctor_to_BA.TXT.pre_processed
SeparatorCharacter=,
TableName=bill_area.tbl
TableDefaultValue=00510
LogFileDir=$HCISITEDIR/Tables
rm -f /ftp/$MDA_BATCH_ENV/oftp_056/work/*
rm -f $HCISITEDIR/Tables/*
touch $HCISITEDIR/Tables/$TableName
table_builder.tcl
-i $InputFileName
-sep $SeparatorCharacter
-outbound_proc ‘{oth_trim_chars {{CHARACTERS “r”} {DIRECTION “right”} {DEBUG “n”}}}’
-table $TableName
-dflt $TableDefaultValue
-l $LogFileDir
-debug > /ftp/$MDA_BATCH_ENV/oftp_056/work/table_builder.log.processed
exit_status=$?
mv $HCISITEDIR/Tables/$TableName /ftp/$MDA_BATCH_ENV/oftp_056/work/${TableName}.processed
rm -f $HCISITEDIR/Tables/*
if [ “$exit_status” != “0” ]
then
script_name=”$0″
email_address=”${MDA_BATCH_ENV}_oftp_056_email”
alert_log_file=”$HCISITEDIR/Alerts/oftp_056.log”
alert_message=”`date` – $HCISITE alertrr$script_name FATAL ERROR, HUB on-call will need to investigate by doing:rrcd /ftp; findall | grep _056″
send_alert_message.ksh “$email_address” “$alert_message”
echo “$alert_message” >> $alert_log_file
exit $exit_status
fi
rm -f $InputFileName
If memory serves me right, I think I have a preprocessing step somewhere that might remove the header line from the CSV file, so keep that in mind if using a CSV approach.
Russ Ross
RussRoss318@gmail.com