• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

how do I combine 4 files into a single table?

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

Steve
0
Steve Jennings
Asked:
Steve Jennings
2 Solutions
 
PranjalShahCommented:
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
0
 
Walter RitzelSenior Software EngineerCommented:
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,
          p1.short_description,
          p2.long_description,
          p3.price,
          p4.quantity,
          p4.location
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;

commit;

 

0
 
Steve JenningsIT ManagerAuthor Commented:
Thanks for your comments and your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now