Results 1 to 10 of 10

Thread: MySQL-Help Importing data into tables and ignoring existing data

  1. #1
    Join Date
    Dec 2010
    Location
    Madison, Wisconsin
    Beans
    163
    Distro
    Ubuntu

    MySQL-Help Importing data into tables and ignoring existing data

    I'm new to MySQL and I have a question about importing data from csv files. I've spent the better part of today googling, trying new solutions, then dropping the table and starting over when a potential solution didn't work.

    I have a table, with two columns set as the primary key, and 12 subsequent columns where the data will live for each month. I get a csv file each month and I want to import the data into this table. The csv file has three columns, the two primary keys, and the sales figure for that month. I can import one month without issue, but then when I import the subsequent month, the data from the first month is erased, replaced with NULL.

    Any help would be much obliged, as I'm out of ideas.

    Thanks!

  2. #2

    Re: MySQL-Help Importing data into tables and ignoring existing data

    You can't change the database design? The table you describe... well, it's just a spreadsheet. What advantage do you hope to get from putting it in a relational database?

    That said, the UPDATE statement should do what you want. Assuming you're creating records with something like
    Code:
    INSERT INTO ugly_table ( key_1, key_2, january ) VALUES ( 'x', 'y', 200.0 );
    the following will change ugly_table.february without erasing ugly_table.january:
    Code:
    UPDATE ugly_table
    SET february=250.0
    WHERE key_1='x' AND key_2='y';
    Last SQL I did was Informix so I could be misrepresenting the syntax.

  3. #3
    Join Date
    Dec 2010
    Location
    Madison, Wisconsin
    Beans
    163
    Distro
    Ubuntu

    Re: MySQL-Help Importing data into tables and ignoring existing data

    I realize it is essentially a spreadsheet but it is an extremely large spreadsheet that has exceeded the capacity of excel.

    The code you posted makes sense with individual examples, however I'm running into problems with large imports of data.

  4. #4

    Re: MySQL-Help Importing data into tables and ignoring existing data

    Well, post your process and we'll try to help you figure out what's going wrong.

  5. #5
    Join Date
    Nov 2009
    Beans
    1,081

    Re: MySQL-Help Importing data into tables and ignoring existing data

    Adding a separate numerical column for 'month' (and dropping the twelve month-specific columns) would bypass much of the problem.

    Then either rebuild the pkey to use it, or use an independent arbitrary pkey and enforce uniqueness via constraints.

  6. #6
    Join Date
    May 2008
    Location
    UK
    Beans
    1,450
    Distro
    Ubuntu 8.04 Hardy Heron

    Re: MySQL-Help Importing data into tables and ignoring existing data

    Quote Originally Posted by Some Penguin View Post
    Adding a separate numerical column for 'month' (and dropping the twelve month-specific columns) would bypass much of the problem.

    Then either rebuild the pkey to use it, or use an independent arbitrary pkey and enforce uniqueness via constraints.
    +1 that is how i would do it

    In case it isn't clear

    You table is :

    Key_1
    Key_2
    Month_Number
    Sales_for_Month

    You might want to include a year coloumn too so you can keep a longer spread of data.
    Tony - Happy to try to help.
    Unless otherwise stated - all code posted by me is untested. Remember to Mark the Thread as Solved.
    Ubuntu user number # 24044 Projects : TimeWarp - on the fly Backups

  7. #7
    Join Date
    Dec 2010
    Location
    Madison, Wisconsin
    Beans
    163
    Distro
    Ubuntu

    Re: MySQL-Help Importing data into tables and ignoring existing data

    OK, thank you I'll try it that way. Most of the keys are repeated each month though, so should I drop the keys entirely, or use an independent arbitrary pkey, as Some Penguin suggested? Or add the month_num to the key?

  8. #8
    Join Date
    May 2008
    Location
    UK
    Beans
    1,450
    Distro
    Ubuntu 8.04 Hardy Heron

    Re: MySQL-Help Importing data into tables and ignoring existing data

    Your primary key must uniquely identify the row - so since the two keys you already have don't unqiuely identify the row - so you have 12 rows now per year, then yes - you have to include the month/year.

    I don't like the idea of an arbritary key to be honest - it means you have to involve other data (i.e. something that tells you the arbritary key from your original key1, key2, month etc) before you can easily query the data.

    Also keeping the keys in the table means you can also build individual indexes later should you find you need them.
    Tony - Happy to try to help.
    Unless otherwise stated - all code posted by me is untested. Remember to Mark the Thread as Solved.
    Ubuntu user number # 24044 Projects : TimeWarp - on the fly Backups

  9. #9
    Join Date
    Dec 2010
    Location
    Madison, Wisconsin
    Beans
    163
    Distro
    Ubuntu

    Re: MySQL-Help Importing data into tables and ignoring existing data

    Quote Originally Posted by Tony Flury View Post
    Your primary key must uniquely identify the row - so since the two keys you already have don't unqiuely identify the row - so you have 12 rows now per year, then yes - you have to include the month/year.

    I don't like the idea of an arbritary key to be honest - it means you have to involve other data (i.e. something that tells you the arbritary key from your original key1, key2, month etc) before you can easily query the data.

    Also keeping the keys in the table means you can also build individual indexes later should you find you need them.
    Great, thanks for your help Tony, I'll report back Monday or Tuesday when I'm back at work.

  10. #10
    Join Date
    Dec 2010
    Location
    Madison, Wisconsin
    Beans
    163
    Distro
    Ubuntu

    Re: MySQL-Help Importing data into tables and ignoring existing data

    Success! Thanks again for your help, Tony, et all. Going forward, I will use 4 column tables, with 3 columns assigned as the key.

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
  •