Puppy Linux Discussion Forum Forum Index Puppy Linux Discussion Forum
Puppy HOME page : puppylinux.com
"THE" alternative forum : puppylinux.info
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

The time now is Sat 25 Feb 2017, 23:58
All times are UTC - 4
 Forum index » Advanced Topics » Cutting edge
fields.awk database
Moderators: Flash, Ian, JohnMurga
Post new topic   Reply to topic View previous topic :: View next topic
Page 1 of 7 [97 Posts]   Goto page: 1, 2, 3, 4, 5, 6, 7 Next
Author Message
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Sat 18 Jun 2016, 16:03    Post subject:  fields.awk database  

Hi all

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

http://www.murga-linux.com/puppy/viewtopic.php?t=18420&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 ........
Back to top
View user's profile Send private message 
musher0


Joined: 04 Jan 2009
Posts: 9539
Location: Gatineau (Qc), Canada

PostPosted: Sat 18 Jun 2016, 17:16    Post subject:  

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". Smile

BFN.

_________________
musher0
~~~~~~~~~~
« Un insensé sur le trône n'est qu'un singe sur le haut d'un toit. » / "A madman
on the throne is just a monkey on top of a roof." (Bernard de Clervaux)

Last edited by musher0 on Thu 23 Jun 2016, 08:21; edited 1 time in total
Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Sat 18 Jun 2016, 18:03    Post subject:  

Thank you musher0

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

http://www.murga-linux.com/puppy/viewtopic.php?p=874592#874592


and also here:

http://www.murga-linux.com/puppy/viewtopic.php?p=906874#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 Sun 19 Jun 2016, 20:42; edited 2 times in total
Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Sat 18 Jun 2016, 18:25    Post subject:  

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.
Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Sat 18 Jun 2016, 19:42    Post subject:  

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.
Back to top
View user's profile Send private message 
greengeek


Joined: 20 Jul 2010
Posts: 4436
Location: New Zealand

PostPosted: Sun 19 Jun 2016, 20:15    Post subject:  

Pete wrote:
For those interested in fields.awk, ...
http://www.murga-linux.com/puppy/viewtopic.php?t=18420&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..)
LinkHowTo.jpg
 Description   
 Filesize   36.37 KB
 Viewed   662 Time(s)

LinkHowTo.jpg

Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Sun 19 Jun 2016, 20:35    Post subject:  

@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.
Back to top
View user's profile Send private message 
greengeek


Joined: 20 Jul 2010
Posts: 4436
Location: New Zealand

PostPosted: Mon 20 Jun 2016, 04:21    Post subject:  

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.
Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Mon 20 Jun 2016, 05:04    Post subject:  

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.
Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Mon 20 Jun 2016, 06:28    Post subject:  

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:

@@@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:

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


and the result:

Code:

@@@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:

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.
ScreenshotAWK.jpg
 Description   
 Filesize   22.54 KB
 Viewed   617 Time(s)

ScreenshotAWK.jpg

Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Mon 20 Jun 2016, 08:15    Post subject:  

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:

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:

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


The answer would now be 356.89
Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Mon 20 Jun 2016, 09:38    Post subject:  

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

Code:

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:

<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:
ScreenshotHTML.jpg
 Description   
 Filesize   19.88 KB
 Viewed   581 Time(s)

ScreenshotHTML.jpg

Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Mon 20 Jun 2016, 15:47    Post subject:  

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:

@@@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::
ScreenshotGH.jpg
 Description   
 Filesize   57.85 KB
 Viewed   555 Time(s)

ScreenshotGH.jpg

Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Tue 21 Jun 2016, 10:42    Post subject:  

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).
FieldsAwk.png.zip
Description 
zip

 Download 
Filename  FieldsAwk.png.zip 
Filesize  13.42 KB 
Downloaded  72 Time(s) 
ScreenshotFieldsAwk.jpg
 Description   
 Filesize   8.18 KB
 Viewed   531 Time(s)

ScreenshotFieldsAwk.jpg

Back to top
View user's profile Send private message 
Pete


Joined: 02 Mar 2014
Posts: 670

PostPosted: Tue 21 Jun 2016, 10:49    Post subject:  

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).
ConvertingCSVwithGeany.zip
Description 
zip

 Download 
Filename  ConvertingCSVwithGeany.zip 
Filesize  189.31 KB 
Downloaded  56 Time(s) 

Last edited by Pete on Tue 21 Jun 2016, 10:54; edited 1 time in total
Back to top
View user's profile Send private message 
Display posts from previous:   Sort by:   
Page 1 of 7 [97 Posts]   Goto page: 1, 2, 3, 4, 5, 6, 7 Next
Post new topic   Reply to topic View previous topic :: View next topic
 Forum index » Advanced Topics » Cutting edge
Jump to:  

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


Powered by phpBB © 2001, 2005 phpBB Group
[ Time: 0.0804s ][ Queries: 12 (0.0111s) ][ GZIP on ]