Thursday, April 12, 2012

Cut and paste.

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 post is at:

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 this:
Then after we cut the files they should look like this:
Lets create a program to compile the data. Nice thing about this is all we have to do to add units is use the same type file in the directory. No requirements to change the file. 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….
# Get data from unit files and create .CUT files.
for f in unit?
    cut -c 12-25 $f > $newfile

# 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
   paste --d , cutitfile  $g >> cutfile
   mv cutfile cutitfile

# uncomment the next line if you want to use sc
# psc -d , < cutitfile >

Make an executable file:
$ chmod +x

Run the program:
See the result file:
$ cat cutitfile
, Unit 1, Unit 2, Unit 3
Income:, 5000, 45000, 18000
Expenses, 2000, 46000, 18000
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.
Choose the file:
Using the comma splits out the cells like we want them.
Raw data:
After the window dressing.
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 >
$ sc

No comments:

Post a Comment