Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2008
    Posts
    10
    Plugin Contributions
    0

    Default combining 2 spreadsheets

    I have a spreadsheet with product Ids , price, and manufacturer. I have a seperate spreadsheet that has product Ids and description.

    The two spreadsheets have a common column.. Product Ids.
    The two sheets have a different number of rows.
    How can I combine the two sheets together?

  2. #2
    Join Date
    Feb 2006
    Location
    Tampa Bay, Florida
    Posts
    9,625
    Plugin Contributions
    123

    Default Re: combining 2 spreadsheets

    I would use a database for a job like this.

    load spreadsheet 1 into a table called manufacturer
    load spreadsheet 2 into a table called description
    select the two tables joined on product id, export this data to a csv.
    Import the csv into an excel spreadsheet.
    That Software Guy. My Store: Zen Cart Modifications
    Available for hire - See my ad in Services
    Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.
    Do you benefit from Zen Cart? Then please support the project.

  3. #3
    Join Date
    Jul 2008
    Posts
    10
    Plugin Contributions
    0

    Default Re: combining 2 spreadsheets

    I created a database in OpenOffice Base.
    I loaded 3 spreadsheets into the database. They are manufacturer, descriptions, and inventory.
    All three have a "common" field called item_id.
    I set the item_id in each of the tables as a primary key.
    When I try to set up a relation between the three primary keys, I get an error message that says Integrity Constraint Violation.
    Each of the item_id fields has a different amount of records.

    How can I link the 3 tables?

  4. #4
    Join Date
    Feb 2006
    Location
    Tampa Bay, Florida
    Posts
    9,625
    Plugin Contributions
    123

    Default Re: combining 2 spreadsheets

    select * from manufacturer m, descriptions d, inventory i
    where i.products_id = d.products_id and d.products_id = m.products_id;

    This will give you a complete set of records for items that have a record in each table.
    That Software Guy. My Store: Zen Cart Modifications
    Available for hire - See my ad in Services
    Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.
    Do you benefit from Zen Cart? Then please support the project.

  5. #5
    Join Date
    Jul 2008
    Posts
    10
    Plugin Contributions
    0

    Default Re: combining 2 spreadsheets

    Quote Originally Posted by swguy View Post
    select * from manufacturer m, descriptions d, inventory i
    where i.products_id = d.products_id and d.products_id = m.products_id;
    SWGUY, Thanks for your help. I think I am almost there, but I am still having a some trouble with the SQL. It gives me an error Table not found in statement [select * from manufacturer]

    I tried changing products_id to item_id but I still get the error.

    Thanks again for your help.

  6. #6
    Join Date
    Feb 2006
    Location
    Tampa Bay, Florida
    Posts
    9,625
    Plugin Contributions
    123

    Default Re: combining 2 spreadsheets

    I don't know what you called your manufacturer table. I thought you said there were three spreadsheets you had loaded into three tables. If the table name is foo you want

    select * from foo m, descriptions d, inventory i
    where i.products_id = d.products_id and d.products_id = m.products_id;

    check the spelling and case of the table name. You're almost there.
    That Software Guy. My Store: Zen Cart Modifications
    Available for hire - See my ad in Services
    Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.
    Do you benefit from Zen Cart? Then please support the project.

  7. #7
    Join Date
    Jul 2008
    Posts
    10
    Plugin Contributions
    0

    Default Re: combining 2 spreadsheets

    Got it working! Base is awesome!!

    Thanks for your help.

  8. #8
    Join Date
    Feb 2006
    Location
    Tampa Bay, Florida
    Posts
    9,625
    Plugin Contributions
    123

    Default Re: combining 2 spreadsheets

    Congratulations! You're a database guy now. :)
    That Software Guy. My Store: Zen Cart Modifications
    Available for hire - See my ad in Services
    Plugin Moderator, Documentation Curator, Chief Cook and Bottle-Washer.
    Do you benefit from Zen Cart? Then please support the project.

 

 

Similar Threads

  1. v139h Spreadsheets....do I need em.
    By dunerunner in forum Managing Customers and Orders
    Replies: 9
    Last Post: 27 Jun 2012, 11:01 PM
  2. Easy Populate spreadsheets
    By Serious in forum All Other Contributions/Addons
    Replies: 2
    Last Post: 28 Feb 2011, 02:05 PM
  3. Help with Importing products from Excel Spreadsheets...
    By BenhamCollectibles in forum General Questions
    Replies: 5
    Last Post: 29 Oct 2009, 06:27 AM

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
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR