fields.awk database

Under development: PCMCIA, wireless, etc.
Message
Author
User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

fields.awk database

#1 Post by Pete »

Hi all

Ever since musher0 introduced fields.awk, been using it on a daily basis.

http://www.murga-linux.com/puppy/viewto ... &start=165

It's a nice, simple, lightweight flat file DB based on awk and should run on just about any computer, even a lowly 10 year old box.

My uses include:

1) All my grocery shopping is now entered into fields.awk
I have 7 fields:
StoreName Desc Price Unit Date BarCode Comments

So now can cross check best prices, how often I buy certain things and so on.

2) I also use it as a search replacement for the "not so good" search facility on this forum.
When ever I come across any interesting or useful bits of info, I enter it into a DB with just three fields:

Distro Comments URL

3) Since the DB file format used by fields.awk is so simple, I intend to get a couple of Arduino's (or maybe even Pi Zeros) as data collection devices, write the data into a compatible file which can then be used in fields.awk

If anyone has a use for a straight forward, non complicated database, I can highly recommend this one.

@musher0, please feel free to "take over" this thread, so to speak, with any ideas, suggestions and possible improvements you have in mind.

The floor is yours ........

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#2 Post by musher0 »

Thanks for the kind offer, Pete, but you're doing fine! I couldn't do any better than you
did, and I have nothing to add to what you've said -- except perhaps copy here from the
general "Puppy Database" thread the URL's concerning fields.awk.

DB's are and perhaps have always been PuppyLinux's soft spot. I'm actually very
glad to see someone who knows his stuff pick up the fields.awk DB subject.

It's actually a good thing too that fields.awk now has its own thread. It'll help it stand out
from the rest.

This would be the ideal thread for the author of fileds.awk Ian R. Forsyth to join
and share some insights with us on databasing every once and a while. I tried to
convince him earlier on, but he seems to be the "shy genius type". :)

BFN.
Last edited by musher0 on Thu 23 Jun 2016, 12:21, edited 1 time in total.
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#3 Post by Pete »

Thank you musher0

For those interested in fields.awk, more information here:

http://www.murga-linux.com/puppy/viewto ... 592#874592


and also here:

http://www.murga-linux.com/puppy/viewto ... 874#906874


Also have some information there on how to use Gnumeric, OpenOffice and Google Docs/Sheets to produce graphs from a field.awk DB.

I also recommend using LXTerminal over URXVT due to a scrolling problem and much better copy and paste support.
More details in the second link above.

EDIT:

Modified links above.
(Thank you greengeek).
Last edited by Pete on Mon 20 Jun 2016, 00:42, edited 2 times in total.

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#4 Post by Pete »

I have also been preparing a how-to on using a handheld barcode scanner
with fields.awk to enter barcode data and also do searches based on them.

Found a used Manhattan ICI401517 barcode scanner in the garage a few days ago with a few scratches but in working order.

Just need to download the programming booklet as it's set to produce Windows CR+LF instead of just LF for Linux after the barcode.

Soon as I have done this and confirmed that it's OK, will post results here.

I think it will be a nice addition for fields.awk (or any other software) to be able to scan barcodes instead of the tedious typing of all those characters.

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#5 Post by Pete »

How to use a Barcode scanner with fields.awk.

Before I get started, I must point out that by barcodes, I'm referring to the one dimensional (stripes) type barcode and not the newer 2 dimensinal ones such as QR code.
I will not discuss the 2D types, for one simple reason, I don't own a scanner that supports them.

For a detailed list of barcodes, see here:

https://en.wikipedia.org/wiki/Barcode#Types_of_barcodes
Although there are many, the most popular ones seem to be EAN8, EAN13, CODE 128 and UPC.

Handheld barcode scanners come in three "flavours", namely USB, Serial and Bluetooth.
The most popular type (and cheapest) are the USB types and vary in price from around US$17 to over US$100

They are available from online stockists plus don't forget to check your nearest thrift shop/recycling bank/reclamation yard.
You will be surprised at what people throw away in a working condition.

If you are thinking of buying one, there are a few important features to consider:

1) As stated before, get a USB type, hardly anyone has serial ports these days and the Buetooth ones are more expensive and fiddly to set up.

2) Make sure that it can emulate a HID keyboard.

3) Ensure that the programming booklet is available.
This has special barcodes which are used to program the scanner for things like, adding CR+LF or just LF at the end of the scanned barcode to emulate the ENTER button being pressed.

Note that no drivers or .ko modules are required.

If you can only get the booklet in pdf format, then you will need to print it out so that you can scan these special barcodes.
This only has to be done once when setting up.
Mine was already all set up but just had to change CR+LF to just LF.

4) A scan width of around 80mm (around 3") is a good compromise.
The scan width determines the smallest and biggest barcode that can be reliably scanned.

5)Make sure that it can scan the most popular 1D barcodes as described above.
Most can.

Once you have set up the scanner using the special codes, it's time to test.
Get a couple of products and open a text editor like leafpad or Geany or nano or .....
Now one at a time, scan the products.
You should see the same characters as those printed below the barcode.

If the last digit is missing, check the programming booklet as most likely the scanner is not set up to include the last digit which is usually the checksum.

Also ensure that after each scan, the cursor on the text editor has gone to a new line.
If not, double check that the scanner has been programmed to output just a LF instead of a CR+LF.

If all is OK, you can now use the scanner with fields.awk or any other software as the scanner is simply emulating you typing in those characters on the barcode.

Hope it's of help.

User avatar
greengeek
Posts: 5789
Joined: Tue 20 Jul 2010, 09:34
Location: Republic of Novo Zelande

#6 Post by greengeek »

Pete wrote:For those interested in fields.awk, ...
http://www.murga-linux.com/puppy/viewto ... &start=195
See from the 6th post..
Hi Pete, thanks for the info. Will be testing over the next few days (hoping it will help me tidy up tax receipts...)

Were you aware of the following method of linking to a thread post? (Saves having to list the "top of page" post and counting down several posts):

Each post has a small square just to the left of the "Posted" timestamp. If you rightclick that square and "Copy Link Location" it will give you the exact link for the post you want to refer to.

(Just dont do "Copy Image Location" which does something different..)
Attachments
LinkHowTo.jpg
(36.37 KiB) Downloaded 954 times

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#7 Post by Pete »

@greengeek

Many thanks for the tip on how to get the link to the exact post.
I did not know that and it's certainly easier than counting.

I will edit the relevant links.

Please do report back on how you get on with your receipts.

User avatar
greengeek
Posts: 5789
Joined: Tue 20 Jul 2010, 09:34
Location: Republic of Novo Zelande

#8 Post by greengeek »

OK, I have a couple of things to mention that threw me at first:

1) After you create a new datafile you cannot immediately try to add a new record. It is necessary to type "S" first - this gives you an opportunity to select the datafile you want to work with (I figured it would be selected automatically because I had just created it, but the program requires you to specifically select it)

2) If you try to read some data out of your data file the program asks you if you want to create an output file. If you say yes it will ask you what name to give that file. However - it will not write that file until after you complete the read function and quit back to the home screen. (File is not created till then).

I will keep practising with this - I think it might be useful for my tax - especially if i can do the export to gnumeric as suggested.

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#9 Post by Pete »

Hi greengeek

Yes, one has to (S)elect a database file even if it was just created.
Not sure why the author made it that way, as you say it's a fair assumption to think that it would be selected automatically.

As for creating a "search results" file, I had never noticed that it only creates the file after returning to the main menu.
I normally just print to screen, so thanks for posting as I'm sure it will be of help to others.

I'm also planning some extra features to fields.awk to expand it's usefulness.
Have some already, just need to test more and will post soon.

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#10 Post by Pete »

Using awk to expand on fields.awk.

Let's say we had a DB file called June06Purchases.txt where we put all purchases for this month.

Raw view example:

Code: Select all

@@@StoreName::Item::Price::Unit::
Acme Trading::Road Runner Traps::24.99::1::
Fleabay::USB thingy::20.95::1::
BigPrice::arm chair::99.00::2::
OldEgg.com::lamp::12.95::1::
Now we want to see which stores we have bought from and what we bought.
We can use awk for this:

Code: Select all

awk -F:: '{ print $1,"\t",$2 }' /root/.DB/June06Purchases.txt
and the result:

Code: Select all

@@@StoreName 	 Item
Acme Trading 	         Road Runner Traps
Fleabay 	                 USB thingy
BigPrice 	                 arm chair
OldEgg.com 	         lamp
The tabulation is a bit off due to the forum software, so look at the screen shot below.

If now we wanted to save this to a file:

Code: Select all

awk -F:: '{ print $1,"\t",$2 }' /root/.DB/June06Purchases.txt > /path/to/some/file.txt
Explanation of the above code:

The -F:: tells awk that our field separator is ::
The $1 is the first field to print, the $2 the second and the "\t" tells it to put TAB between them.
The /root/.DB/June06Purchases.txt is the full path and name of the DB file.
The > routes the output to somefile.txt instead of the screen.

BTW, if we want to add several searches all to the same file, simply change the > with a >> and each new search will append (rather than over write) to the same file.

Since the DB in this case has 4 fields, we can use $1 to $4 in any combination.

My intention is to include this (plus many more) in a script but until I have time to do so, I thought I would post just snippets which may be of help and perhaps others will also contribute.
Attachments
ScreenshotAWK.jpg
(22.54 KiB) Downloaded 915 times

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#11 Post by Pete »

Here is another handy one liner.

With reference to the DB file in my previous example (June06Purchases.txt)
where the PRICE of an item is in field 3 and the qty we bought is in field 4,
this will produce the total spent so far:

Code: Select all

awk -F:: 'BEGIN { sum = 0 } // { sum = sum + $3 * $4 } END { print sum }' /root/.DB/June06Purchases.txt
The answer is 256.89

The calculation is done in the { sum = sum + $3 * $4 }
Change to suit your needs.

You could also add an offset, for example a total you got from another as yet undocumented expenditure, let's say 100:

Code: Select all

awk -F:: 'BEGIN { sum = 100 } // { sum = sum + $3 * $4 } END { print sum }' /root/.DB/June06Purchases.txt
The answer would now be 356.89

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#12 Post by Pete »

Here is another one liner if you want to convert your DB to html and view in a web browser:

Code: Select all

awk -F:: 'BEGIN{print "<pre><table>"} {print "<tr>";for(i=1;i<=NF;i++)print "<td>" $i"&emsp;&emsp;</td>";print "</tr>"} END{print "</table></pre>"}' /root/.DB/June06Purchases.txt > /root/June06Purchases.html
It will produce June06Purchases.html in /root
Change to suit your needs.


NB!! I see the one liner has wrapped to a new line when posted here.
If you copy and paste into the Terminal, watch out for this.



Note that I have not bothered with the html head and body tags as modern browsers don't seem too bothered by the fact that they are missing.
If you need/want them, add before the <pre> and after the </pre> tags in the awk code above.

This is the html code it produces:

Code: Select all

<pre><table>
<tr>
<td>@@@StoreName&emsp;&emsp;</td>
<td>Item&emsp;&emsp;</td>
<td>Price&emsp;&emsp;</td>
<td>Unit&emsp;&emsp;</td>
<td>&emsp;&emsp;</td>
</tr>
<tr>
<td>Acme Trading&emsp;&emsp;</td>
<td>Road Runner Traps&emsp;&emsp;</td>
<td>24.99&emsp;&emsp;</td>
<td>1&emsp;&emsp;</td>
<td>&emsp;&emsp;</td>
</tr>
<tr>
<td>Fleabay&emsp;&emsp;</td>
<td>USB thingy&emsp;&emsp;</td>
<td>20.95&emsp;&emsp;</td>
<td>1&emsp;&emsp;</td>
<td>&emsp;&emsp;</td>
</tr>
<tr>
<td>BigPrice&emsp;&emsp;</td>
<td>arm chair&emsp;&emsp;</td>
<td>99.00&emsp;&emsp;</td>
<td>2&emsp;&emsp;</td>
<td>&emsp;&emsp;</td>
</tr>
<tr>
<td>OldEgg.com&emsp;&emsp;</td>
<td>lamp&emsp;&emsp;</td>
<td>12.95&emsp;&emsp;</td>
<td>1&emsp;&emsp;</td>
<td>&emsp;&emsp;</td>
</tr>
</table></pre>
and this is what it looks like in a web browser:
Attachments
ScreenshotHTML.jpg
(19.88 KiB) Downloaded 880 times

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#13 Post by Pete »

Another possible application for fields.awk --- data collection.

Let's assume you have a greenhouse and install two temperature sensors. one inside and one outside and connect them to an Arduino which in turn is connected to your Linux box via a serial (or USB to Serial) port.

Would be quite easy to write a Bash script that read the temps from the Arduino every hour on the hour and log it to a DB file.

This could be run for say a 24 period.
You can then analyze the data in fields.awk or even search for certain temperatures or the temperature at a certain time.

Could even import the DB into Gnumeric (as is) and create graphs.

Below is a DB that contains simulated values:
(It only has three fields, namely time of day, outside temp and inside temp)

Code: Select all

@@@Time::OutTemp::InTemp::
00::10::18::
01::10::18::
02::9::18::
03::9::18::
04::8::18::
05::6::18::
06::7::18::
07::8::18::
08::10::18::
09::12::18::
10::14::18::
11::15.5::18::
12::17::18::
13::19::18::
14::22::20::
15::22::20::
16::22.5::21::
17::21::20::
18::21::20::
19::19::19::
20::17::18.5::
21::14::18::
22::12::18::
23::11::18::
Attachments
ScreenshotGH.jpg
(57.85 KiB) Downloaded 849 times

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#14 Post by Pete »

Created a icon for my shortcut on the desktop for fields.awk.
It's a 128X128 png and is attached here as FieldsAwk.png.zip
Remove the fake .zip

I have also attached a jpg version (just for preview, don't use it, rather use the png).
Attachments
FieldsAwk.png.zip
(13.42 KiB) Downloaded 403 times
ScreenshotFieldsAwk.jpg
(8.18 KiB) Downloaded 826 times

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#15 Post by Pete »

If you have a .csv file and want to convert to a fields.awk compatible DB file, I have attached instructions on how to do it in 3 steps using only Geany.

Note that the attachment is a real zip file that contains the instructions in html format (with screenshots so ensure you extract them as well).
Attachments
ConvertingCSVwithGeany.zip
(189.31 KiB) Downloaded 251 times
Last edited by Pete on Tue 21 Jun 2016, 14:54, edited 1 time in total.

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#16 Post by Pete »

Or if you prefer it in pdf format, it's attached.
Just remove the fake .zip file in this case.

I didn't include it in the zip file of the previous post as it took the file size to over the 256KB max.
Attachments
UsingGeanyToConvertCSV.pdf.zip
(171.95 KiB) Downloaded 233 times

musher0
Posts: 14629
Joined: Mon 05 Jan 2009, 00:54
Location: Gatineau (Qc), Canada

#17 Post by musher0 »

Thanks, Pete.
musher0
~~~~~~~~~~
"You want it darker? We kill the flame." (L. Cohen)

User avatar
Pete
Posts: 660
Joined: Sun 02 Mar 2014, 18:36

#18 Post by Pete »

You're welcome musher0

Of course it's also just as easy to use geany to convert from fields.awk back to csv.

cabbie0
Posts: 26
Joined: Thu 23 Jun 2016, 05:19
Location: British Columbia, Canada

Great Job Guys

#19 Post by cabbie0 »

Hello Puppy Linux group,

Delighted to see you folks are finding such ingenious ways to make use of fields.awk.
Keep up the good work!

Just a quick correction in that musher0 somehow got my last name mixed up. It's Forsyth, not Stewart (though they're both Scottish lol).

The work on adaptation to barcode reader outputs and Arduino+sensor data is outstanding creativity. Kudos.

And yes, as you've discovered, using awk in subsequent scripts can produce handy analyses of the fields.awk database files. I do this on a regular basis, especially to keep track of running totals in a given group of items. For example if a simple database for musical items exists like so:

@@@DATE::STORE::ITEMS::TOTAL::
07-16-14::Wentworth Music::violin string set (Helicore 4/4 Light)::50.35::
11-14-14::Wentworth Music::Violin fine tuner (W1102)::4.43::
03-12-15::Wentworth Music::Pro-Arte Violin Set 4/4 (nylon core)::43.63::
05-09-15::London Drugs::Sony Memory Stick Pro Duo (4GB)::33.59::
05-15-15::Wentworth Music::violin string set (D'Addario helicore Heavy-tension 4/4)::50.35::
10-09-15::Wentworth Music::replacement "A" string (Precision A 4/4; 51 Medium, solid steel core, chrome wound)::14.51::
10-16-15::Wentworth Music::violin bow (4/4, carbon composite)::124.27::
10-30-12::Wentworth Music::HOHNER Pro Harp G::44.74::

Then I can enter the following command (of course give it a one-word handle) for the total,

awk -F:: '{print $4}' ~/DB_FILES/music_exp.txt | addcol 1
365.87

Note here the "addcol" command is yet another useful short script based on awk:

# ADDS THE NUMBERS IN COLUMN NUMBER SPECIFIED ON COMMAND LINE
# SYNTAX: addcol [number] [filename]

if [ $# -eq 0 ]
then echo USAGE: addcol [number] [filename]
exit 0
fi

awk -v colnum=$1 'BEGIN {t=0} {t=t+$colnum; print t}' $2 | tail -n1

exit 0

No doubt you may find many uses for that as well. I find it serves very well at tax time, so long as your records are tabulated. No more laborious checking columns with a calculator...


And a huge thank you to Pete for creating the FieldsAwk.png icon :) Awesome!

Ian R. Forsyth

User avatar
greengeek
Posts: 5789
Joined: Tue 20 Jul 2010, 09:34
Location: Republic of Novo Zelande

#20 Post by greengeek »

Welcome!
I am just getting to grips with fields.awk and have a long way to go...

I enjoyed the youtube video mushero linked to. A good starting point.

I'm hoping to use this to help keep track of tax receipts throughout the year. I think the first hurdle is to decide how many fields to create. The temptation is to create a small number of fields to make it easy to get started - but it may well be better to add more fields than I think I need - in order to allow for future needs.

Or is there a way to add more fields later if necessary? Would that upset prior entries?
.

Post Reply