 |
|
|
|
|
|
| |
| |
|
|
|
|
| |
| |
|
|
But there may be someone here that could and would give me a quick answer:
Suppose there is an Excel file, oh let's say posted on a website daily.
The file contains licensing information and can be downloaded.
Suppose you have a separate list of license numbers you want to match
against those on the file.
Does Excel itself have the means to automate this match?
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Jim Charter schrieb:
> Does Excel itself have the means to automate this match?
Well, Excel has macros - so yes, quite definitely so...
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Jim Charter <jrc### [at] msn com> wrote:
> But there may be someone here that could and would give me a quick answer:
>
> Suppose there is an Excel file, oh let's say posted on a website daily.
>
> The file contains licensing information and can be downloaded.
>
> Suppose you have a separate list of license numbers you want to match
> against those on the file.
>
> Does Excel itself have the means to automate this match?
Yes it does. You can even make a refreshable web query that will pull the Excel
file from the website and make the matches.
I don't know how to do it, but I've heard of that sort of thing being done.
-Reactor
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Jim Charter wrote:
> Does Excel itself have the means to automate this match?
You could write a VBA macro on the first sheet, along with your license
numbers, and have the macro use an XMLHttpRequest to pull down the excel
file locally, then import it into the second sheet and do the appropriate
match. You'd have to wander around help files and MSDN to see exactly how, tho.
--
Darren New, San Diego CA, USA (PST)
I ordered stamps from Zazzle that read "Place Stamp Here".
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Thanks for the quick response guys.
In New York, the Taxi and Limousine Commission has instituted a new set
of regulations for For Hire Vehicle bases making the base owners
directly responsible for any cars they dispatch that do not have current
valid licenses. On their side the TLC is posting the list of valid
licenses on said Excel file. Bases in violation of this requirement
could be shut down. Even a one day shut-down of one of the larger
operations could easily impact them in the six figures range.
I have heard that some of these guys have purchased a program, priced as
much as $10,000, to check the validity of their drivers' licenses!
My boss (where I work training taxi drivers) sees an opportunity because
there are hosts of smaller bases who can't afford that, and, truth be
told, really don't get it that the TLC means business. He asked me if I
could write such a program we could distribute cheaply, both to make
some money for us, and to help educate these smaller guys who could
really get slammed. Frustrating to me because while scripting such an
app is well within my capability, writing something that could be
compiled, secured, licensed, and distributed, is not.
But at least, for one of our former instructors who is now in this
situation, I would like to put together something minimal for him to
use. If I could do it with an Excel macro that would be perfect.
Now I know that it is worth my time looking into. Thanks again.
-Jim
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Jim Charter wrote:
> Frustrating to me because while scripting such an
> app is well within my capability, writing something that could be
> compiled, secured, licensed, and distributed, is not.
If that's what you're trying to do, I'd suggest writing a program that
invokes Excel via COM automation. That is, write a program in a higher-level
language (Perl, Python, Tcl, C#, anything.NET, etc) that downloads the excel
spreadsheet to the local drive, starts Excel running without it opening a
window, and tells Excel to extract the list you're interested in and save it
as a CSV file, then script all you like from there. Alternately, continue
using COM to drive Excel and simply ask it what the value in each cell of
the spreadsheet is, comparing against your list.
I.e., treat reading the excel file in much the same way you'd read XML and
process it with javascript DOM stuff. *That* would be the part you could
compile and distribute. (This is the sort of thing .NET would be very good
for - Excel spreadsheets turn into just another object you access in C# with
array-index syntax and things like that.)
It would be pretty easy to tell the people using it that they need a
sufficiently-recent version of excel installed, and to install the ".NET
automation libraries" from the add/remove program details for Excel, and
then run your program.
--
Darren New, San Diego CA, USA (PST)
I ordered stamps from Zazzle that read "Place Stamp Here".
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Jim Charter wrote:
> But there may be someone here that could and would give me a quick answer:
>
> Suppose there is an Excel file, oh let's say posted on a website daily.
>
> The file contains licensing information and can be downloaded.
>
> Suppose you have a separate list of license numbers you want to match
> against those on the file.
>
> Does Excel itself have the means to automate this match?
Too bad it relies on Excel. It would be a lot easier with SQL.
-Mike
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
SharkD wrote:
> Too bad it relies on Excel. It would be a lot easier with SQL.
If I'm not entirely mistaken, MS Access can execute SQL queries on Excel
spreadsheets. (And CSV files, in fact.)
--
http://blog.orphi.me.uk/
http://www.zazzle.com/MathematicalOrchid*
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
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.
--
Darren New, San Diego CA, USA (PST)
I ordered stamps from Zazzle that read "Place Stamp Here".
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
Jim Charter <jrc### [at] msncom> wrote:
> Frustrating to me because while scripting such an
> app is well within my capability, writing something that could be
> compiled, secured, licensed, and distributed, is not.
>
GPL the spreadsheet, then sell a "service" of teaching people how to use it. The
scenario here however is that the more well-written your little macro is, the
lesser chance of getting revenue for service.
Post a reply to this message
|
|
| |
| |
|
|
|
|
| |
| |
|
|
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
|
|
| |
| |
|
|
|
|
| |
