SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Updating data with a tab delimited text file

    Hello again. Here's a description of what I'm trying to accomplish.

    I have a mysql database with the following columns:
    | ItemNumber | Description |UntiPrice | QuantityAvailable | GrossWeight | SearchDescription | ProName |

    I also have a tab delimited text file database with the same columns in the same order, except the columns are not named.(I'm not even sure if you can name the colums in a tab delimited text database. Maybe one of you guys know)

    what I need to do is import this data to mysql and update "quantityavailable" where the "itemsnumber"s match and if an item number in the text file doesn't exist in the mysql database I need it to be added.

    I'm not sure that this is even possible, or where I would begin. Hopefully one of you guys can help me out with this one.

    Thanks in advance

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    yes, it is very much possible

    the first step is loading the data into mysql

    try the LOAD DATA INFILE command, and store the file into its own table

    then you will use the following query:

    INSERT INTO existing_table ( columns )
    SELECT columns from just_loaded_table
    ON DUPLICATE KEY UPDATE existing_table.quantityavailable = existing_table.quantityavailable + just_loaded_table.quantityavailable
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks a lot.
    I'll give that a shot and let you know how it works out for me.

  4. #4
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I tried that out. Everything works just fine except that it adds the previous quantity available with the quantity available that I'm importing into the table, which is not what I'm wanting it to do. I want it to replace quantity available with the number that I'm importing in and not add them together.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by sikk66 View Post
    I want it to replace quantity available with the number that I'm importing in and not add them together.
    so, just change the SET, yes?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Dec 2006
    Location
    Atlanta, GA
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Got it!

    You're a life saver!

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    sorry, i should have said UPDATE, not SET

    glad you knew what i meant

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •