Jim,

I have experience with that sort of application.

In one case I have a C# program that downloads share data from a certain 
site.  One file per day in CSV format.  The format of the file has 
varied over time so the program does some cleansing.  The raw data is 
then loaded to a database where the heavy duty work starts.

Doing something like day to day deltas and producing a report or data 
extract to be distributed is easy from there.  My main product is a 
database query and reporting tool that can work on pretty much all DBMSs 
/ data sources (including Excel (.XLS) and .CSV).  It includes a script 
language suitable for automating exactly this sort of process.  Results 
can be produced in various formats including Excel, CSV and PDF.  An 
email script command can send results out.

Give me some sample data (say 3 days worth) and I'll mock something up. 
  Don't know if a custom application or the general purpose tool is the 
way to go without seeing some data.

I have done things like this in Excel using VBA.  C# or VB.Net are so 
much richer that I wouldn't contemplate using VBA any more.

Regards,

Paul

Post a reply to this message

Darren New wrote:
> SharkD wrote:
> 
>> Too bad it relies on Excel. It would be a lot easier with SQL.
> 
> 
> SQL is a language, not a file format. You can't download SQL off the 
> intarwebs.
> 
> Now, posting it as a CSV would have made more sense, were it really just 
> tabular data.
> 
Hey, some TLC data we access using dumb terminal (3270?) emulation and 
CICS!  Remember those days?

Post a reply to this message

Jim Charter wrote:
> Hey, some TLC data we access using dumb terminal (3270?) emulation and 
> CICS!  Remember those days?

I'm almost embarrassed to admit I do.

-- 
   Darren New, San Diego CA, USA (PST)
   I ordered stamps from Zazzle that read "Place Stamp Here".

Post a reply to this message

I know a lot of "serious" programmers look down on it, but this is 
exactly the kind of thing that VB shines at: quick & dirty programs that 
have simple requirements, and interface with other MS apps.

...Chambers

Post a reply to this message

Paul Fuller wrote:
> Jim,
> 
> I have experience with that sort of application.
> 
> In one case I have a C# program that downloads share data from a certain 
> site.  One file per day in CSV format.  The format of the file has 
> varied over time so the program does some cleansing.  The raw data is 
> then loaded to a database where the heavy duty work starts.
> 
> Doing something like day to day deltas and producing a report or data 
> extract to be distributed is easy from there.  My main product is a 
> database query and reporting tool that can work on pretty much all DBMSs 
> / data sources (including Excel (.XLS) and .CSV).  It includes a script 
> language suitable for automating exactly this sort of process.  Results 
> can be produced in various formats including Excel, CSV and PDF.  An 
> email script command can send results out.
> 
> Give me some sample data (say 3 days worth) and I'll mock something up. 
>  Don't know if a custom application or the general purpose tool is the 
> way to go without seeing some data.
> 
> I have done things like this in Excel using VBA.  C# or VB.Net are so 
> much richer that I wouldn't contemplate using VBA any more.
> 
> Regards,
> 
> Paul
Wow Paul, thank you. I may take you up on that.

If you are interested in taking a peek, and as of right now I would need 
to confirm everything I am saying, but I believe the list is published 
on this page:
http://www.nyc.gov/html/tlc/html/current/current_licensees.shtml

The list in question is this one:
'TLC Licensees with NYS DMV Status Issues'

Column A appears to be the license number to match on.
In Column C we would filter for 'For-Hire-Vehicle Driver'
That's all that is needed.
Column B obviously just confirms the name and
Column D is presumably the revocation/suspension date.

So if I understand it correctly a 'hit' against this list would mean a 
driver could not be dispatched.  A 'miss' would mean he is okay.  'Hits' 
could then be printed out for further investigation, individually, on 
the separate system.  (Probably that CICS emulation thing I mentioned to 
Darren.)

So the basic problem is that these bases have many drivers, so a daily 
batched lookup from a list of their own drivers, which would print out 
revoked drivers, would be a convenience for them.  Checking even say 100 
numbers, individually, against the list every day would be cumbersome 
and a bit ridiculous in this day and age.  But paying $10,000 for a 
'program' to do it? Whoa.

-Jim

P.S.
(I have a little Python script I use to download the files on this page:
http://www.nyc.gov/html/tlc/html/courtadmin/court_hearing_calendar.shtml

I have to remember to run it manually every week or so, my code checks 
for any new files I haven't already gotten.  It is also quite messy 
because these are PDF files so it uses some code I found, but don't 
understand, to translate into plain text.  I then use string 
manipulation and pattern matching to extract what I need, the counts for 
the numbers of times different violations are scheduled for court 
hearings.  But this is just a little scripted tool I use myself with the 
Python interpreter installed. And there is no error catching or bullet 
proofing of any kind.  I wouldn't have the first clue about distributing 
a compiled version of such a thing.)

Post a reply to this message

Jim Charter wrote:
> And there is no error catching or bullet 
> proofing of any kind.  I wouldn't have the first clue about distributing 
> a compiled version of such a thing.)

FWIW, this is the whole "Software as a Service" kind of thing. Have *them* 
send *you* a list of their licensees whenever they hire or fire someone, 
then run everyones lists against the excel sheet and mail them back any 
hits.  Then you don't have to compile it, bullet proof it, etc etc etc.

-- 
   Darren New, San Diego CA, USA (PST)
   I ordered stamps from Zazzle that read "Place Stamp Here".

Post a reply to this message

Darren New wrote:
> FWIW, this is the whole "Software as a Service" kind of thing. 

Alternately, I don't know how one scripts Open Office offhand, but that 
might be easier also.

-- 
   Darren New, San Diego CA, USA (PST)
   I ordered stamps from Zazzle that read "Place Stamp Here".

Post a reply to this message

Jim,

After I replied I found the TLC website and grabbed some data.  Looks 
really straight forward.

Is there some reference list that maps drivers to companies ?

Then you could list the suspended drivers by licensee.  Those licensees 
that sign up for your service get a list of their drivers who have 
changed status.

Paul

Post a reply to this message

Paul Fuller wrote:
> Jim,
> 
> After I replied I found the TLC website and grabbed some data.  Looks 
> really straight forward.
> 
> Is there some reference list that maps drivers to companies ?
> 
> Then you could list the suspended drivers by licensee.  Those licensees 
> that sign up for your service get a list of their drivers who have 
> changed status.
> 
> Paul
I will pitch this idea to my boss.

Post a reply to this message

Paul Fuller wrote:
> Is there some reference list that maps drivers to companies ?

Or, when they sign up, they provide you the list of drivers, which you then 
keep and let them update remotely.

-- 
   Darren New, San Diego CA, USA (PST)
   I ordered stamps from Zazzle that read "Place Stamp Here".

Post a reply to this message