1

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;
  • "Am trying to create excel sheet base on multifiles on a root, I read files line by line append in the final excel sheet." -- so that means you want to concatenate several Excel files in one? – schaiba Nov 19 '15 at 10:05
  • ya, you can say that – Ahmed Samir Nov 19 '15 at 10:21
  • it seems you exhausted the available memory. How big are that files? – Rui F Ribeiro Nov 19 '15 at 10:28
  • Using bash to parse XML and parsing it by hand isn't the best option, you know. If you have some background in basics of programming, I'd suggest using Python + cElementTree(which works like ElementTree, except it's faster) - I'm currently using it, and it allows me to handle files bigger than my ram, so memory is no issue here. – MatthewRock Nov 19 '15 at 10:29
  • btw. "Cannot reallocate" refers to reallocation of memory in RAM, which means that program can't request memory you want from kernel, and it's the reason for failure. It has nothing to do with reallocation of physical files. – MatthewRock Nov 19 '15 at 10:31
0

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.