how do I combine 4 files into a single table?

Posted on 2011-04-29
Medium Priority
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

Assisted Solution

PranjalShah earned 1000 total points
ID: 35495404
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 16

Accepted Solution

Walter Ritzel earned 1000 total points
ID: 35495665
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

ID: 35734032
Thanks for your comments and your help.

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month14 days, 17 hours left to enroll

840 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