how do I combine 4 files into a single table?

Posted on 2011-04-29
Last Modified: 2012-08-13
I have 4 product files, each file is 20,000 lines. The files

product file 1
product_id, short_description

description file 2
product_id, long_description

price file 3
price, product_id

inventory file 4
product_id, quantity, location

I need a single table that looks like:

product_id, short_description, long_description, price, quantity, location

Note that in file 3, the product_id isn't in the first column.

This isn't a school test, I am trying to help a friend upload some data into an Intuit web "store" and they are very specific about how they will accept a file. It has to be a comma delimited file. I figured it would be easier to pull these flat files into a data base and then export it as a comma delimited file but I am stuck trying to figure out how to merge the tables.

Thanks in advance for your help

Question by:SteveJ
    LVL 8

    Assisted Solution

    I dont know if this will be the best way, but it should work.

    Step 1:

    Create a product table same as the product file 1. So now in the new table you have the values for product_id and short_description.

    Step 2:

    Add remaining fields to the product table.

    Step 3:

    Now one by one add the data from different tables.

    from table description file 2

    UPDATE product a, table_description b set a.long_description = b.long_description where a.product_id=b.product_id

    You can do the same for the remaining two tables.

    Hope it helps
    LVL 15

    Accepted Solution

    Here is the solution I would suggest:
    1) create a table with all the fields:
    create table product_final (
    product_id varchar(50),
    short_description varchar(50),
    long_description varchar(500),
    price number(22,7),
    quantity number(10),
    location varchar(100)

    2) use the following command to load the files:

    load data local infile 'file1.csv' into table tblProd1
    fields terminated by ','
    enclosed by '"'
    lines terminated by '\n'
    (product_id, short_description)

    load data local infile 'file2.csv' into table tblProd2
    fields terminated by ','
    enclosed by '"'
    lines terminated by '\n'
    (product_id, long_description)

    load data local infile 'file3.csv' into table tblProd3
    fields terminated by ','
    enclosed by '"'
    lines terminated by '\n'
    (product_id, price)

    load data local infile 'file4.csv' into table tblProd4
    fields terminated by ','
    enclosed by '"'
    lines terminated by '\n'
    (product_id, quantity, location)

    3) insert the information into the final table;

    insert table product_final (product_id, short_description, long_description, price, quantity, location)
    select p1.product_id,
    from tblProd1 p1 inner join tblProd2 p2 on p1.product_id = p2.product_id
                               inner join tblProd3 p3 on p1.product_id = p3.product_id
                               inner join tblProd4 p4 on p1.product_id = p4.product_id;



    LVL 16

    Author Closing Comment

    Thanks for your comments and your help.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now