Play pretend here for a second. You are a new accountant for a company and you need to make a consolidated financial report of many units. (This example is way over simplified, but I think you will get the idea.) You need to get a report out, but you do not have time to re-enter figures, because all the units send their data in ascii or what is known as text files. How can we get the computer to compile the data so it can be easily entered into the spreadsheet? Heres how!

A good prerequisite for this instructable is at: http://www.linuxintheshell.org/

Step 1: The Data.

Units one, two, and three send their data in, but they are in an ascii file. We want to convert them so they can be easily converted for use in a spreadsheet.

From Unit 1 in unit1:

Unit 1
Income: 5000
Expenses 2000

Data from Unit 2 in unit2:

Unit 2
Income 45000
Expenses 46000

Data from Unit 3 in unit3:

Unit 3
Income 18000
Expenses 18000

Files should look something more like the pictures.

Note: For some reason the data files were marked unusable. They were just simple text files. I deleted them.

Step 2: The Code.

Lets create a program to compile the data. Nice thing about this is, all we have to do is to add units of the the same type file (unit#) in the directory. No requirements to change the program even if you have more or less units. Warning it will kill the old cutitdata, so be sure and back all the files up and and delete ones you do not need. There is of course and easier way, but we will save that for later….

Use a text editor to create the program:

compiledata.sh

[code]

# Get data from unit files and create .CUT files.
for f in unit?
do
newfile="$f.CUT"
    cut -c 12-25 $f > $newfile
done

# create tmpfile.
touch cutfile
rm cutfile
touch cutfile

#create Labels for the file.
echo " " > cutitfile
echo Income: >> cutitfile
echo Expenses >> cutitfile

# put it all together.
for g in unit?.CUT
do
   paste --d , cutitfile $g >> cutfile
   mv cutfile cutitfile
done

# uncomment the next line if you want to use sc
# psc -d , < cutitfile > Units.sc
[/code]

Make the program executable

$ chmod +x compiledata.sh

Run the program.

$./compiledata.sh

See the results.

$ cat cutitfile

, Unit 1, Unit 2, Unit 3
Income:, 5000, 45000, 18000
Expenses, 2000, 46000, 18000

Three files are now combined into one! This is what is known as a comma delimited file which most spreadsheets can import easily.

Now we can use the spreadsheet to import the data.

Step 3: Importing the File Into Spreadsheet.

Fire up the spreadsheet.
Import the file with insert sheet from file.
Choose the file. (cutitfile).
The data will look like it is in all one column. No problem just click on the comma option under separated by. The data will then line up in separate columns.
Say ok! (press ok.)
Aah!! now you have the data in the spreadsheet. All without retyping the data.

Step 4: Window Dressing.

Now you can add all the totals and extra information for the spreadsheet report. We could have done all that in the batch file but I wanted to make things simpler as it might be done in a real office! Now all you have to do is automate the reports from the different units.

Done.

Step 5: SC, the Command Line Spreadsheet.

If you want to stay completely command line, you can use the program called sc.
You will need to do a conversion though.

$ psc -d , < cutitfile > Units.sc
$ sc Units.sc