Professional Web Applications Themes

Batch process to add to a group from csv file - MySQL

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

  1. #1

    Default 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


    Jack Guest

  2. #2

    Default Re: Batch process to add to a group from csv file

    > My programmer says it cannot be done without hours 

    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 Guest

  3. #3

    Default Re: Batch process to add to a group from csv file

    Captain Paralytic wrote: 
    >
    >
    > 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.
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  4. #4

    Default Re: Batch process to add to a group from csv file


    Jerry Stuckle wrote: 
    > >
    > >
    > > 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.
    > >[/ref]
    >
    > 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.[/ref]

    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 Guest

  5. #5

    Default Re: Batch process to add to a group from csv file

    Captain Paralytic wrote: 
    >>
    >>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.[/ref]
    >
    >
    > 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?
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: Batch process to add to a group from csv file


    Jerry Stuckle wrote: 
    > >
    > >
    > > 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?
    > >[/ref]
    >
    > Actually, it probably isn't. It's probably something like employee id
    > or similar. Could also be department number.[/ref]
    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.
     
    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?
     
    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 Guest

  7. #7

    Default Re: Batch process to add to a group from csv file

    Captain Paralytic wrote: 
    >>
    >>Actually, it probably isn't. It's probably something like employee id
    >>or similar. Could also be department number.[/ref]
    >
    > 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.
    >

    >
    > 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?
    >[/ref]

    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.
     
    >
    > 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.
    >[/ref]

    What are you using then?

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

  8. #8

    Default Re: Batch process to add to a group from csv file

    Jerry Stuckle wrote:
     
    >>
    >> 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.
    >>[/ref]
    >
    > What are you using then?[/ref]

    What am I using for what?


    Paul Guest

  9. #9

    Default Re: Batch process to add to a group from csv file

    Paul Lautman wrote: 
    >>
    >>What are you using then?[/ref]
    >
    >
    > What am I using for what?
    >
    >[/ref]

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

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

  10. #10

    Default Re: Batch process to add to a group from csv file

    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


    Jack Guest

  11. #11

    Default Re: Batch process to add to a group from csv file


    Jerry Stuckle wrote: 
    > >
    > >
    > > What am I using for what?
    > >
    > >[/ref]
    >
    > For the comparison. It can't be the name.[/ref]

    What are you on about? I'm not using anything for any comparison. I'm
    not doing this!
    However, if I was then I would be looking up a member name and
    inserting the associated supervisor's name into the members database.
    The important piece of information for this is the name that I need to
    lookup (i.e. do the compare with). This is the MEMBER NAME. The
    SUPERVISOR NAME is the result of the lookup.

    Captain Guest

  12. #12

    Default Re: Batch process to add to a group from csv file

    Jack Matthews wrote: 

    The table layout would be critical here. You can't update something if
    you don't know what it looks like!

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

  13. #13

    Default Re: Batch process to add to a group from csv file

    Captain Paralytic wrote: 
    >>
    >>For the comparison. It can't be the name.[/ref]
    >
    >
    > What are you on about? I'm not using anything for any comparison. I'm
    > not doing this!
    > However, if I was then I would be looking up a member name and
    > inserting the associated supervisor's name into the members database.
    > The important piece of information for this is the name that I need to
    > lookup (i.e. do the compare with). This is the MEMBER NAME. The
    > SUPERVISOR NAME is the result of the lookup.
    >[/ref]

    Ok, I understand. You're doing the comparison on the employee name. No
    problem.

    But your solution is still unwieldy. It would take me just as along to
    write a batch program to do the job as it does you to go through all the
    export, etc. for one file. And he has multiple files.

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

  14. #14

    Default Re: Batch process to add to a group from csv file

    Ok I did a query and there is a family ID associated with the name. The ID
    is not in my excel supervisor sheets.
    The family is assigned the supervisor, but the name of the family's adults
    should be fine to assign the supervisor.

    FamilyID Title FirstName Middle LastName NameSuffix FamilyRole
    MaritalStatus Address City State ZipCode
    25501076196070500 JIMMY BUFFET Adult Married 4000 S EVANSTON CIR
    UNIT F Aurora CO 80014


    See example above. I do not yet know the data/table structure.


    "Jerry Stuckle" <net> wrote in message
    news:com... [/ref]
    names. [/ref]
    suppose I [/ref]
    integer 
    >
    > The table layout would be critical here. You can't update something if
    > you don't know what it looks like!
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]


    Jack Guest

  15. #15

    Default Re: Batch process to add to a group from csv file

    Jack Matthews wrote: [/ref]
    >
    > names.
    > [/ref]
    >
    > suppose I
    > [/ref]
    >
    > integer

    >>
    >>The table layout would be critical here. You can't update something if
    >>you don't know what it looks like!
    >>[/ref]
    > Ok I did a query and there is a family ID associated with the name.[/ref]
    The ID 
    adults 

    (Top posting fixed)

    OK, well, it looks like you're going to have to match up ids also. The
    structure of the other tables will be key here. And the fact this isn't
    in your Excel spreadsheets makes things a little harder, but shouldn't
    be that much.


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

  16. #16

    Default Re: Batch process to add to a group from csv file

    ok so what would that look like? Matching the IDs that is.

    "Jerry Stuckle" <net> wrote in message
    news:com... 
    > >
    > > names.
    > > 
    > >
    > > suppose I
    > > 
    > >
    > > integer
    > > 
    > > Ok I did a query and there is a family ID associated with the name.[/ref]
    > The ID 
    > adults [/ref]
    CIR 
    >
    > (Top posting fixed)
    >
    > OK, well, it looks like you're going to have to match up ids also. The
    > structure of the other tables will be key here. And the fact this isn't
    > in your Excel spreadsheets makes things a little harder, but shouldn't
    > be that much.
    >
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]


    Jack Guest

  17. #17

    Default Re: Batch process to add to a group from csv file

    Jack Matthews wrote: 
    >> 
    >>The ID 
    >>adults [/ref]
    >
    > CIR

    >>
    >>(Top posting fixed)
    >>
    >>OK, well, it looks like you're going to have to match up ids also. The
    >>structure of the other tables will be key here. And the fact this isn't
    >>in your Excel spreadsheets makes things a little harder, but shouldn't
    >>be that much.
    >>
    >>
    >>--
    >>==================
    >>Remove the "x" from my email address
    >>Jerry Stuckle
    >>JDS Computer Training Corp.
    >>net
    >>==================[/ref]
    >
    >
    >[/ref]
     

    Not knowing what the table layouts are, I have no idea.

    Sounds like you need a basic SQL book to help you out. All of this is
    pretty basic SQL, but it all depends on the information you have
    available, the format of that information, the table layouts, etc.

    You should try to get it working from you end, then if you have problems
    you can't figure out, you should post the code and the problem you're
    having here.

    Or, if you're looking for someone to do it for you, hire a consultant to
    do it. This isn't a good place to get something like this written for
    you. It's not hard - but it will take a little time.


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

Similar Threads

  1. Run batch process (OCR) using VB?
    By rajiv@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 2
    Last Post: October 21st, 01:01 PM
  2. HELP! Need to write batch file to automate securityupdate process
    By dkeeling0419 in forum Coldfusion Server Administration
    Replies: 0
    Last Post: March 1st, 03:25 PM
  3. Batch Process
    By Enzo Fiorello in forum Adobe Acrobat Windows
    Replies: 14
    Last Post: April 8th, 10:08 PM
  4. Batch Process Resize
    By maszsam in forum Macromedia Fireworks
    Replies: 0
    Last Post: August 13th, 11:50 PM
  5. batch process?
    By emptx webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 0
    Last Post: July 25th, 12:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139