"Cannot reallocate" when create file?

Question

I am trying to create excel sheet based on multiple files on a root. I read files line by line and append in the final excel sheet.

I am trying this shell script on small files and it worked 100%, but when I try it on the needed files (85MB per each file) I get this error:

(dsadm@DEVDS) /EDWH/XML/Must # XML.sh csv excel_outputfilename
./XML.sh: line 41: fallocate: command not found
./XML.sh: xmalloc: cannot allocate 172035663 bytes (0 bytes allocated)
./XML.sh: xrealloc: cannot reallocate 86013568 bytes (0 bytes allocated)
./XML.sh: xrealloc: cannot reallocate 86021888 bytes (0 bytes allocated)

Note:

• The csv parameter is the file extension

• My OS and version: Unix AIX 7.1

Here's the script:

#!/usr/bin/bash  

#Files Extension#
Ext=$1

#OutPut File Name without extension ex: TEST#
OutPutFileName=$2.xls

function XMLHeader ()
{
     echo "<?xml version=\"1.0\"?>
    <Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"
     xmlns:o=\"urn:schemas-microsoft-com:office:office\"
     xmlns:x=\"urn:schemas-microsoft-com:office:excel\"
     xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"
     xmlns:html=\"http://www.w3.org/TR/REC-html40\">"
}

function SheetHeader ()
{
    echo "<Worksheet ss:Name=\"Sheet1\">
    <Table ss:ExpandedColumnCount=\"2\" ss:ExpandedRowCount=\"2\" x:FullColumns=\"1\" x:FullRows=\"1\">
    <Row><Cell><Data ss:Type=\"String\">"
}

function SheetFooter ()
{
    echo "</Data></Cell></Row></Table>
    </Worksheet>"
}

function XMLFooter ()
{
    echo "</Workbook>"
}

####################################################################################

cd /EDWH/Samir/XML/Must;

fallocate -l 1G $OutPutFileName

XMLHeader > $OutPutFileName;

# loop on the exists files to build Worksheet per each file 
for Vfile in $(ls | grep .$Ext); 
do
    echo "<Worksheet ss:Name=\"$Vfile\"><Table>" >> $OutPutFileName

    ### loop to write the Row 
        VarRow=`cat $Vfile`
        for Row in $(echo $VarRow )
        do

            echo "<Row>" >> $OutPutFileName

                ### loop to write the cells 
                VarCell=`echo $VarRow`
                for Cell in $(echo $VarCell | sed "s/,/ /g")
                do
                    echo "<Cell><Data ss:Type=\"String\">$Cell</Data></Cell>" >> $OutPutFileName
                done

            echo "</Row>" >> $OutPutFileName

        done

    echo "</Table></Worksheet>" >> $OutPutFileName

done    


echo "</Workbook>" >> $OutPutFileName   

####################################################################################

exit;

Answer 1

Just a few things about this script, ignoring that it's a shell script that deals with XML.

1. It reads each files into memory.
2. It uses some constructs that are "bad practice".

Let's fix that.

• First off, each command does not need to end with ;. The ; is used if you put multiple commands on one line, like ls; echo "hello".

• Variable expansions should alsways be double-quoted. See "Security implications of forgetting to quote a variable in bash/POSIX shells". For example: Ext="$1", XMLHeader >"$OutPutFileName", etc.

• for Vfile in $(ls | grep .$Ext) is better written for Vfile in ./*."$Ext".

• Rather than doing multiple redirections in a loop, all appending to the same file, end the loop with done >>"$OutPutFileName". This is more efficient.

• VarRow=`cat $Vfile` will put the contents of a 85Mb file into a single variable, and then for Row in $(echo $VarRow ) will loop over the rows, or so you hope. Instead, do while IFS= read -r Row; do ... done <"$Vfile". This will read a line at a time. Instead of storing the whole file, you ever only store a line at a time. This is probably where you run into memory issues.

• Rather than VarCell=`echo $VarRow` followed by for Cell in $(echo $VarCell | sed "s/,/ /g"), just do VarCell="${VarRow//,/ }" followed by for Cell in $VarCell (this feels a bit iffy, an unquoted variable holding input data, suggestions for improvements are welcomed)

• Any echo that outputs variable data, I would change to printf with a single-quoted format string followed by double-quoted variable expansions. For example: echo "<Worksheet ss:Name=\"$Vfile\"><Table>" changes to printf '<Worksheet ss:Name="%s"><Table>\n' "$VFile". See "Why is printf better than echo?".

• When you want to output a long string (several lines), use a here-document. That way you additionally don't have to worry about escaping quotes.