csv2worksheet

NAME

SYNOPSIS

csv2worksheet [ -f filename ] [ { -e | -n } sheet ] [ -d deliminator ] [ -o [ col ] , [ row ] ] [ -s cols ]

AVAILABILITY

DESCRIPTION

An Excel spreadsheet, also known as workbook, consists of one or more sheets, also known as worksheets. This worksheets are represented as named tabs in a workbook. With csv2worksheet it is possible to load a file that is structured into rows and the rows consist of fields separated by a deliminator into a worksheet. The row field separator defaults to ; but can be specified with the -d option.

This is an easy method to automatically create "nice looking" Excel reports based on plain ASCII data without the need to create Excel makros or to develop hard to maintain programs that create the whole Excel sheet on the fly. The idea is to load the data into a separate "data" worksheet and to perform the calculations, graphical data representation, statistics etc. in an other worksheet in the same Excel workbook which is not affected by the data load.

The advantage of this method is, that reports can be generated automatically without manual intervention (except the generation of the initial Excel sheet) even on non Windows systems, such as Unix or Linux.

It is possible to specify the name on an existing worksheet or to create a new worksheet while loading the data, dependent on your requirements.

However, the csv2worksheet command cannot overwrite existing data in a worksheet.

The origin of the data load can be specified. This does mean that data does not need to be loaded into a worksheet starting in cell A:1, a different cell can be specified. This enables the user to load multiple CSV data into the same worksheet as long as the data is loaded into ascending areas of an empty worksheet range.

OPTIONS

-h

usage message.

-f filename

Excel file where the CSV data should be loaded into. This file has to be writeable by the user invoking csv2worksheet. Be aware, that csv2worksheet alters the data in the Excel file specified in the -f option and does not create a copy of the file. Therefore you should keep your original template in a safe place. A good practice is to copy the original Excel file first and then to load the data. If you do not specify a file with the -f filename option, the file io.xlsx is used as a default.

-e existing_worksheet_name

load the CSV data into the existing worksheet with the name specified. If the worksheet does not exist in the workbook, csv2worksheet does exit without loading any data. If you neither specify a file with the -e nor the -n option, csv2worksheet tries to load the data into the existing DATA worksheet, that is therefore the default.

-n new_worksheet_name

load the CSV data into a worksheet with the name specified. If the worksheet does not exist currently, it is created at the end of the already existing worksheets.

-d deliminator

deliminator that separates the fields of a row. The deliminator is not restricted to a single character, but spaces or tabulators are not allowed. The default deliminator is ; if this option is not specified.

-o column_number,row_number

column number and row number where to start the load of the CSV data into the worksheet. If this option is not specified, the data is loaded starting at column 1,1, that means the upper left corner of the data loaded is in cell A:1.

The following examples are allowed specifications: -o 3,8 (start at cell C:8), -o ,8 (start at cell A:8), -o 3, (start at cell C:1), -o , (start at cell A:1).

-s column_list

comma separated list of source column numbers that should be loaded explicitly as text.
The normal behaviour of csv2worksheet is, that whenever a number is recognized in the input, it is loaded as a floating point value while all other data is loaded as text. See also NOTES section.
If you specify -s 0 all columns will be loaded as text.

ENVIRONMENT

CSV2WORKSHEET_JAVA_OPTIONS="option"

additional options for the Java JVM process.

1) Example: set heap space

  CSV2WORKSHEET_JAVA_OPTIONS="-Xmx8g"; export CSV2WORKSHEET_JAVA_OPTIONS

to set the Java heap space to 8 GBytes which might be needed when loading a large csv file into a worksheet and receiving the "java.lang.OutOfMemoryError: Java heap space" error.

To query the default JVM heap size use:

  java -XX:+PrintFlagsFinal -version | grep HeapSize

2) Example: relocate temp files

  CSV2WORKSHEET_JAVA_OPTIONS="-Djava.io.tmpdir=/dat/tmp"
  export CSV2WORKSHEET_JAVA_OPTIONS

to relocate the temporary files generated by csv2worksheet from /tmp/ to /dat/tmp/.

EXIT STATUS

0

no error.

1

the specified worksheet in the workbook could not be opened or created.

2

Excel file is not writable by the user invoking csv2worksheet or it does not exist.

4

usage displayed.

5

version displayed.

6

cannot load data. Ensure the worksheet range where to load the data is empty.

FILES

edrc/var/csv2worksheet/io.xlsx

Empty Microsoft Excel workbook. This workbook contains the worksheets Sheet1, Sheet2, Sheet3 and DATA and can be used as a template to load data into.

EXAMPLES

1) load data into an existing worksheet

Load file data.csv into the existing worksheet 'current' of file statistics.xlsx

[ /data_dwh1/dat/exports ]
[ root@dwh_db1_prod ][ksh]: cat data.csv | \
                            csv2worksheet -f statistics.xlsx \
                              -e current

csv2worksheet - load a CSV file into an Excel sheet, by Chr. Walther

load data ...
        sheet 'statistics.xlsx/current' opened.
        load origin is '1,1'.
        data deliminator is ';'.
        explicit text columns are ''.
        load rows ...
                1     ....................................(36)
                2     ....................................(36)
                3     ....................................(36)
                4     ....................................(36)
                5     ...............(15)
                6     ....................................(36)
                7     ....................................(36)
                8     ....................................(36)
                9     .............................(29)
                10    ....................................(36)
        done.
done.

2) load data into a new worksheet using input redirection

Load file data.csv into the new worksheet 'Month=Sep' of file statistics.xlsx starting in cell C:5 using 'input redirection' instead of a pipe

[ /data_dwh1/dat/exports ]
[ root@dwh_db1_prod ][ksh]: csv2worksheet < data.csv \ 
                              -f statistics.xlsx \
                              -n Month=`date +%b` -o 3,5

csv2worksheet - load a CSV file into an Excel sheet, by Chr. Walther

load data ...
        sheet 'statistics.xlsx/Month=Sep' created.
        load origin is '3,5'.
        data deliminator is ';'.
        explicit text columns are ''.
        load rows ...
                5     ....................................(36)
                6     ....................................(36)
                7     ....................................(36)
                8     ....................................(36)
                9     ...............(15)
                10    ....................................(36)
                11    ....................................(36)
                12    ....................................(36)
                13    .............................(29)
                14    ....................................(36)
        done.
done.

3) load data into existing worksheet using in here

Load data into the existing worksheet 'DATA' of file io.xlsx using the 'in here' mechanism

[ /data_dwh1/dat/exports ]
[ root@dwh_db1_prod ][ksh]: cat <<EOM | csv2worksheet
NAME;MIN;MAX
alpha;100;200
bravo;150;250
charly;155;400
EOM

csv2worksheet - load a CSV file into an Excel sheet, by Chr. Walther

load data ...
        sheet 'io.xlsx/DATA' opened.
        load origin is '1,1'.
        data deliminator is ';'.
        explicit text columns are ''.
        load rows ...
                1     ...(3)
                2     ...(3)
                3     ...(3)
                4     ...(3)
        done.
done.

4) set a single cell of existing worksheet

Set a single cell ( E:20 ) of an existing worksheet 'DATA' of file io.xlsx

[ /data_dwh1/dat/exports ]
[ root@dwh_db1_prod ][ksh]: echo "Load at: `date`" |\
                              csv2worksheet -o 5,20

csv2worksheet - load a CSV file into an Excel sheet, by Chr. Walther

load data ...
        sheet 'io.xlsx/DATA' opened.
        load origin is '5,20'.
        data deliminator is ';'.
        explicit text columns are ''.
        load rows ...
                20    .(1)
        done.
done.

5) explicitly load certain columns as text

Load data into the existing worksheet 'DATA' of file io.xlsx using the 'in here' mechanism and explicitly load the columns 4 and 5 (of the input) as text into the worksheet.

[ /data_dwh1/dat/exports ]
[ root@dwh_db1_prod ][ksh]: cat <<EOM | csv2worksheet -s 4,5
NAME;MIN;MAX;SERIAL;PHONE
alpha;100;200;2009063009250001;080007121941
bravo;150;250;2009063009250002;080006061944
charly;155;400;2009063009250003;080020071969
EOM

csv2worksheet - load a CSV file into an Excel sheet, by Chr. Walther

load data ...
        sheet 'io.xlsx/DATA' opened.
        load origin is '1,1'.
        data deliminator is ';'.
        explicit text columns are '4,5'.
        load rows ...
                1     .....(3)
                2     .....(3)
                3     .....(3)
                4     .....(3)
        done.
done.

SEE ALSO

NOTES

csv2worksheet uses "Jakarta POI - Java API To Access Microsoft Format Files " of the Apache Jakarta project. See http://jakarta.apache.org/poi/ for more information about the progress of the implementation.

BUGS

Therefore if still jobs are present that operate with *.xls files, convert this files to *.xlsx.

AUTHOR

COPYRIGHT

This is free software; see edrc/doc/COPYING for copying conditions. There is ABSOLUTELY NO WARRANTY; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.