Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    28

    Unanswered: Dealing w/ Duplicates

    Here is a MySQL script that I am running on my server that outputs entries to a txt file that I then import into blackboard web portal software in order to create user accounts:


    --Create a temporary table to hold information

    DROP TABLE IF EXISTS `temp`;
    CREATE TABLE `temp` (
    `begin` VARCHAR(125)
    );

    --Insert combined string of several fields from chc_students table into the temporary table

    INSERT INTO temp(begin)
    SELECT concat_ws('","',concat_ws('',LEFT(first_name,1),la st_name),(last_name),(first_name),concat_ws('',LEF T(first_name,1),last_name,'@','christianheritage.e du'),concat_ws('',LOWER(LEFT(first_name,1)),LOWER( LEFT(last_name,1)),student_id),(student_id),(middl e_name))
    FROM chc_students;

    --attach " to the beginning and end of string and then export to txt file to import into blackboard

    SELECT concat_ws('','"',begin,'"')
    FROM temp
    INTO OUTFILE "/blackboard_create.txt";



    Once that script runs the txt file looks like this:



    "DValdez","Valdez","Dina","DValdez@christianherita ge.edu","DV113382","113382",""
    "DValdez","Valdez","Dina","DValdez@christianherita ge.edu","DV113382","113382",""
    "DValdez","Valdez","Dina","DValdez@christianherita ge.edu","DV113382","113382",""
    "DValdez","Valdez","Dina","DValdez@christianherita ge.edu","DV113382","113382",""
    "DValdez","Valdez","Dina","DValdez@christianherita ge.edu","DV113382","113382",""
    "DValdez","Valdez","Dina","DValdez@christianherita ge.edu","DV113382","113382",""
    "AClarkson","Clarkson","Amber","AClarkson@christia nheritage.edu","AC111528","111528","J"
    "AClarkson","Clarkson","Amber","AClarkson@christia nheritage.edu","AC111528","111528","J"
    "CBender","Bender","Candi","CBender@christianherit age.edu","CB111818","111818","J"
    "CBender","Bender","Candi","CBender@christianherit age.edu","CB111818","111818","J"
    "JFlenoid","Flenoid","Judy","JFlenoid@christianher itage.edu","JF111795","111795",""
    "JFlenoid","Flenoid","Judy","JFlenoid@christianher itage.edu","JF111795","111795",""
    "JWinters","Winters","Jason","JWinters@christianhe ritage.edu","JW113541","113541","E"
    "JWinters","Winters","Jason","JWinters@christianhe ritage.edu","JW113541","113541","E"
    "JWinters","Winters","Jason","JWinters@christianhe ritage.edu","JW113541","113541","E"



    I need to figure out a way to remove the duplicate lines. The database from which I am pulling the info has an entry for each course in which the student is enrolled. So altering the database is not an option. But I assume there is a simple way to get rid of the duplicates. Thanks,

    Brian

    Oh, also, at the end of the txt file there is a grandtotal line attached that looks like this:

    "LLewis","Lewis","Lloyd","LLewis@christianheritage .edu","LL010165","010165","K"
    "CPickel","Pickel","Carl","CPickel@christianherita ge.edu","CP010195","010195","T"
    "CPickel","Pickel","Carl","CPickel@christianherita ge.edu","CP010195","010195","T"
    "LTyler","Tyler","Linda","LTyler@christianheritage .edu","LT010199","010199","J"
    "LTyler","Tyler","Linda","LTyler@christianheritage .edu","LT010199","010199","J"
    "Grand Total:","Grand Total:","","Grand Total:@christianheritage.edu","G334","334",""
    "","","","@christianheritage.edu","","",""



    Is there anyway to get rid of that?
    Last edited by Strikerz95; 06-07-04 at 14:41.

  2. #2
    Join Date
    May 2004
    Posts
    28
    Well, I found the answer on my own! All i had to do was use a SELECT DISTINCT statement.

  3. #3
    Join Date
    Dec 2003
    Location
    Houston, TX
    Posts
    21
    Hey you know you should probably do a find replace all "@currenturl.edu" to "@fakeurl.edu".

    I'm sure that all those people won't appreciate it when some spammer's email spider program picks up their addy from this thread.
    I do not fear computers. I fear the lack of them.
    -Isaac Asimov (1920 - 1992)

  4. #4
    Join Date
    May 2004
    Posts
    28
    Yes, you are correct. Fortunately those are not real email addresses, just examples

Posting Permissions

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