Batch process to add to a group from csv file

I have a newby question here.

I have a database that has names and addresses of people (members). I also
have a .csv file with supervisors for each of these members. I want to add
to the database the supervisor that manages them to the database. The field
for supervisor is already a part of the database so that is done. However I
have the lists of supervisors in individual excel workbooks or .csv files
and I want to run a batch file that looksup each member name and then
assigns the supervisor's name to their row and then moves on to the next
member name to repeat until all members have been assigned. What would be
the best way to do this? My programmer says it cannot be done without hours
and hours of work on his part. Any suggestions?

Thanks for your help,

Jack

> My programmer says it cannot be done without hours
> and hours of work on his part. Any suggestions?

Get a new programmer!
Seriously, this is not difficult (assuming what you have told us is
true), but you do not need a batch file.
Here are 2 methods (there are lots more):
Method 1)
Step 1) Export the members database into Excel
Step 2) Use VLOOKUP to construct the new rows for the members database
(I often us Excel formulas to create all the INSERT or UPDATE queries)
Step 3) Use the output from Excel to update the members table

Method 2)
Step 1) create a temporary table in mysql to hold the member/supervisor
data.
Step 2) Load the temporary table from the csv file
Step 3) Run a single UPDATE query to load the supervisor data into the
members table

I prefer method 2.

Captain Paralytic wrote:
>>My programmer says it cannot be done without hours
>>and hours of work on his part. Any suggestions?
>
>
> Get a new programmer!
> Seriously, this is not difficult (assuming what you have told us is
> true), but you do not need a batch file.
> Here are 2 methods (there are lots more):
> Method 1)
> Step 1) Export the members database into Excel
> Step 2) Use VLOOKUP to construct the new rows for the members database
> (I often us Excel formulas to create all the INSERT or UPDATE queries)
> Step 3) Use the output from Excel to update the members table
>
> Method 2)
> Step 1) create a temporary table in mysql to hold the member/supervisor
> data.
> Step 2) Load the temporary table from the csv file
> Step 3) Run a single UPDATE query to load the supervisor data into the
> members table
>
> I prefer method 2.
>

Hmmm, that depends on the layout of the tables. If, for instance, the
user table has an id instead of a name, this might now work as well.

Personally I would do it in a batch job. Depending on your table layout
it might even take an hour or two. Just export the .CSV file, read it
one row at a time and update the tables as necessary. Easy in either
Perl or PHP, for instance. A little harder in C++ but not that much.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> Captain Paralytic wrote:
> >>My programmer says it cannot be done without hours
> >>and hours of work on his part. Any suggestions?
> >
> >
> > Get a new programmer!
> > Seriously, this is not difficult (assuming what you have told us is
> > true), but you do not need a batch file.
> > Here are 2 methods (there are lots more):
> > Method 1)
> > Step 1) Export the members database into Excel
> > Step 2) Use VLOOKUP to construct the new rows for the members database
> > (I often us Excel formulas to create all the INSERT or UPDATE queries)
> > Step 3) Use the output from Excel to update the members table
> >
> > Method 2)
> > Step 1) create a temporary table in mysql to hold the member/supervisor
> > data.
> > Step 2) Load the temporary table from the csv file
> > Step 3) Run a single UPDATE query to load the supervisor data into the
> > members table
> >
> > I prefer method 2.
> >
>
> Hmmm, that depends on the layout of the tables. If, for instance, the
> user table has an id instead of a name, this might now work as well.

That was why in included the caveat "(assuming what you have told us is
true)".
The assumption, based on the original post, is that the name field is a
unique reference common to both tables.

But why would you use a batch file when an update query like:

UPDATE members, supervisors
SET members.supervisor = supervisors.supervisor
WHERE members.membername = supervisors.membername

would seem to do the required combination?

Captain Paralytic wrote:
> Jerry Stuckle wrote:
>
>>Captain Paralytic wrote:
>>
>>>>My programmer says it cannot be done without hours
>>>>and hours of work on his part. Any suggestions?
>>>
>>>
>>>Get a new programmer!
>>>Seriously, this is not difficult (assuming what you have told us is
>>>true), but you do not need a batch file.
>>>Here are 2 methods (there are lots more):
>>>Method 1)
>>>Step 1) Export the members database into Excel
>>>Step 2) Use VLOOKUP to construct the new rows for the members database
>>>(I often us Excel formulas to create all the INSERT or UPDATE queries)
>>>Step 3) Use the output from Excel to update the members table
>>>
>>>Method 2)
>>>Step 1) create a temporary table in mysql to hold the member/supervisor
>>>data.
>>>Step 2) Load the temporary table from the csv file
>>>Step 3) Run a single UPDATE query to load the supervisor data into the
>>>members table
>>>
>>>I prefer method 2.
>>>
>>
>>Hmmm, that depends on the layout of the tables. If, for instance, the
>>user table has an id instead of a name, this might now work as well.
>
>
> That was why in included the caveat "(assuming what you have told us is
> true)".
> The assumption, based on the original post, is that the name field is a
> unique reference common to both tables.
>
> But why would you use a batch file when an update query like:
>
> UPDATE members, supervisors
> SET members.supervisor = supervisors.supervisor
> WHERE members.membername = supervisors.membername
>
> would seem to do the required combination?
>

Actually, it probably isn't. It's probably something like employee id
or similar. Could also be department number.

Text comparisons are very expensive compared to integer comparisons.
And what if you have two supervisors named "john smith"?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:
> Captain Paralytic wrote:
> > Jerry Stuckle wrote:
> >
> >>Captain Paralytic wrote:
> >>
> >>>>My programmer says it cannot be done without hours
> >>>>and hours of work on his part. Any suggestions?
> >>>
> >>>
> >>>Get a new programmer!
> >>>Seriously, this is not difficult (assuming what you have told us is
> >>>true), but you do not need a batch file.
> >>>Here are 2 methods (there are lots more):
> >>>Method 1)
> >>>Step 1) Export the members database into Excel
> >>>Step 2) Use VLOOKUP to construct the new rows for the members database
> >>>(I often us Excel formulas to create all the INSERT or UPDATE queries)
> >>>Step 3) Use the output from Excel to update the members table
> >>>
> >>>Method 2)
> >>>Step 1) create a temporary table in mysql to hold the member/supervisor
> >>>data.
> >>>Step 2) Load the temporary table from the csv file
> >>>Step 3) Run a single UPDATE query to load the supervisor data into the
> >>>members table
> >>>
> >>>I prefer method 2.
> >>>
> >>
> >>Hmmm, that depends on the layout of the tables. If, for instance, the
> >>user table has an id instead of a name, this might now work as well.
> >
> >
> > That was why in included the caveat "(assuming what you have told us is
> > true)".
> > The assumption, based on the original post, is that the name field is a
> > unique reference common to both tables.
> >
> > But why would you use a batch file when an update query like:
> >
> > UPDATE members, supervisors
> > SET members.supervisor = supervisors.supervisor
> > WHERE members.membername = supervisors.membername
> >
> > would seem to do the required combination?
> >
>
> Actually, it probably isn't. It's probably something like employee id
> or similar. Could also be department number.
I too doubt that the name field is the correct one to use (hence as I
said the caveat). But if the field DOES contain valid and unique data
and further, is all the OP has then that is what has to be used.

> Text comparisons are very expensive compared to integer comparisons.
This is as may be, but whether done in a batch file or as an update
query, from what the OP posted, they will HAVE to be done some time or
other.
My question still stands, why go to the trouble of writing and
executing a batch file when a single update query will do the job as
well?

> And what if you have two supervisors named "john smith"?
Whilst 2 supervisors named John Smith would cause confusion to anyone
using the data, it wouldn't cause a problem in doing the updates as the
supervisor name is not used for lookup or comparison.

Captain Paralytic wrote:
> Jerry Stuckle wrote:
>
>>Captain Paralytic wrote:
>>
>>>Jerry Stuckle wrote:
>>>
>>>
>>>>Captain Paralytic wrote:
>>>>
>>>>
>>>>>>My programmer says it cannot be done without hours
>>>>>>and hours of work on his part. Any suggestions?
>>>>>
>>>>>
>>>>>Get a new programmer!
>>>>>Seriously, this is not difficult (assuming what you have told us is
>>>>>true), but you do not need a batch file.
>>>>>Here are 2 methods (there are lots more):
>>>>>Method 1)
>>>>>Step 1) Export the members database into Excel
>>>>>Step 2) Use VLOOKUP to construct the new rows for the members database
>>>>>(I often us Excel formulas to create all the INSERT or UPDATE queries)
>>>>>Step 3) Use the output from Excel to update the members table
>>>>>
>>>>>Method 2)
>>>>>Step 1) create a temporary table in mysql to hold the member/supervisor
>>>>>data.
>>>>>Step 2) Load the temporary table from the csv file
>>>>>Step 3) Run a single UPDATE query to load the supervisor data into the
>>>>>members table
>>>>>
>>>>>I prefer method 2.
>>>>>
>>>>
>>>>Hmmm, that depends on the layout of the tables. If, for instance, the
>>>>user table has an id instead of a name, this might now work as well.
>>>
>>>
>>>That was why in included the caveat "(assuming what you have told us is
>>>true)".
>>>The assumption, based on the original post, is that the name field is a
>>>unique reference common to both tables.
>>>
>>>But why would you use a batch file when an update query like:
>>>
>>>UPDATE members, supervisors
>>>SET members.supervisor = supervisors.supervisor
>>>WHERE members.membername = supervisors.membername
>>>
>>>would seem to do the required combination?
>>>
>>
>>Actually, it probably isn't. It's probably something like employee id
>>or similar. Could also be department number.
>
> I too doubt that the name field is the correct one to use (hence as I
> said the caveat). But if the field DOES contain valid and unique data
> and further, is all the OP has then that is what has to be used.
>
>
>>Text comparisons are very expensive compared to integer comparisons.
>
> This is as may be, but whether done in a batch file or as an update
> query, from what the OP posted, they will HAVE to be done some time or
> other.
> My question still stands, why go to the trouble of writing and
> executing a batch file when a single update query will do the job as
> well?
>

Yes, but they have an employee id or similar, the text comparison only
needs to be done when inserting the data. Later an int comparison can
be done.

And it's not quite as simple as you make it out to be. You have to go
through a lot of steps - exporting the database, lookups, etc. In the
time it takes them to go through all those steps one could easily write
a PHP batch job to do the work.

>
>>And what if you have two supervisors named "john smith"?
>
> Whilst 2 supervisors named John Smith would cause confusion to anyone
> using the data, it wouldn't cause a problem in doing the updates as the
> supervisor name is not used for lookup or comparison.
>

What are you using then?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Jerry Stuckle wrote:

>>
>>> And what if you have two supervisors named "john smith"?
>>
>> Whilst 2 supervisors named John Smith would cause confusion to anyone
>> using the data, it wouldn't cause a problem in doing the updates as
>> the supervisor name is not used for lookup or comparison.
>>
>
> What are you using then?

What am I using for what?

Paul Lautman wrote:
> Jerry Stuckle wrote:
>
>
>>>>And what if you have two supervisors named "john smith"?
>>>
>>>Whilst 2 supervisors named John Smith would cause confusion to anyone
>>>using the data, it wouldn't cause a problem in doing the updates as
>>>the supervisor name is not used for lookup or comparison.
>>>
>>
>>What are you using then?
>
>
> What am I using for what?
>
>

For the comparison. It can't be the name.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Well, yes this seems right to me. There are no duplicate supervisor names.
and the names are unique. To my knowledge there is no member id. I suppose I
could ask for a table layout or something and see if there is an integer
associated with the name.

Thanks for your input.

Jack