PDA

View Full Version : Looking for an Excel boffin . . . .not related to MechMate . . . completely OFF-TOPIC


Gerald D
Fri 14 March 2008, 12:15
Hi All

I am looking for an Excel boffin who knows his macros . . .

We have a logger on a garbage truck that logs the gps position and time of every cart/bin pickup. Here (http://www.autotracker.co.za/RPTLifter.aspx?FromDate=2008-3-14 00:00:00&ToDate=2008-3-14 23:59:59&TheUnitID=238&TheUnitName=LIFTER4) is a report from this morning (a small number of those data lines are corrupt - those lines can be ignored) liftlogger (http://www.liftlogger.co.za/)

That daily CSV file needs to be processed to get:
Total bins L (left)
Total bins R (right)
Total bins (L+R) before an N in the Position column

(The N's represent extended driving/parking time when No bins are lifted for 15 minutes.)

Total bins in each "session" (beat) between the N's.
Time beat starts
Time beat ends
Duration of beat
Tempo of bins/hour during a beat.
Average bins per kilometer during a beat
Number of stopping points in the beat (a stopping point is when the gps location changes by less than 7 meters)
Average number of bins per stopping point for a beat.

Typical is 3 beats per day, of about 400 bins each.

Etc.

Etc.

I know Excel well enough to crunch the numbers without using macros, but the process needs serious automation. Soon there will be 14 of these loggers running every day . . . . .

Any volunteers?

Thanks

sailfl
Fri 14 March 2008, 12:23
Gerald,

I work on it for you. If I have some questions, I will send you an email.

Nils

rayditutto
Tue 18 March 2008, 01:41
Hi All

I am looking for an Excel boffin who knows his macros . . .

. . .

Soon there will be 14 of these loggers running every day . . . . .

Thanks

wasn't sure how to send a pm with attachement so my apologies for any clutter here

it's not excel but it may be a better approach
in the attachment you'll find a few things
a batch file to run - eg. "summarize.bat 2008 03 14"
a "data" directory where the raw logs go - organized in folders and subfolders by year, month and day

hopefully the the awk script and sql is fairly self explanatory
the awk script filters out any bad rows, reformats the date and time, adds a beat identifier and the number of seconds since the start of the beat
it's imported into an sqlite db
a number of sql views are created in the db (see summarize.sql) and should give a reasonable example of how to query for various stats

it's not highly polished but is should be good for a start

the query results can be piped out to a file if you like

sqlite has an odbc driver that will let excel populate its tables from an sqlite database

either the comma delimited output or odbc connection can be used in excel where the presentation can be prettied up

having the data cleaned up with some useful fields added to each row should make any excel type work easier

cheers,
robin

Gerald D
Tue 18 March 2008, 04:44
Hi Robin

You have obviously done a lot of work and I appreciate you taking the time out to look at this. It is going to take me a while to figure out what you said though. . . . .

Realise that I am not the person trying to digest the csv file. The end-user will probably only have Excel at their disposal. Nor am I in control of the database that is writing the .csv file.

rayditutto
Tue 18 March 2008, 10:03
no problem - i've learn a lot from this site and continue to be amazed by the quality of the content here

i have more practice doing this stuff than say welding :-)

based on the numbers in your first note, i'm guessing that managing the raw information in excel will quickly become problematic for any one trying to manage it
1,200 rows per log - 16,800 rows per day - 336,000 rows per month - 4,032,000 rows per year

keeping the data in raw form can be helpful later when you may want to reanalyze
the idea with the "data" dir is to keep the data in raw form but make it easy to zip say a month at a time

i was going to play with trying to generate some KML to show a color coded path (color according to say beat or tempo) but perhaps later

if you've got more sample data i should be able automate the aggregation of day over day data
(ie something better than just dump out a csv table to the console)

it's all pretty much self contained
everything used is either open source (awk) or public domain (sqlite) and included in the zip:
http://gnuwin32.sourceforge.net/packages/gawk.htm
http://www.sqlite.org/

The distance calculation was taken from:
http://www.movable-type.co.uk/scripts/latlong.html

for a bit more background, there is:
http://en.wikipedia.org/wiki/Awk
http://en.wikipedia.org/wiki/Sqlite