0

I've got some tables in my MySQL DB and I need to create some other tables of the data in those existing tables based on data in an excel spreadsheet.

My current tables are: tbl_departments tbl_classes tbl_vendors

My excel spreadsheet has a master list showing all of the classes, vendors and departments. The data shows all of the classes and the vendors associated with those classes and the department that said classes belong to.

So, for example, the class "Jeans" has multiple rows listing "Wrangler", "Carhartt", "Key" etc. and the department that jeans belongs to.

How do I use the spreadsheet to create a new table like tbl_classes_vendors and tbl_departments_classes so I can search the classes and pull up a list of vendors based on a department?

Ofeargall
  • 5,340
  • 5
  • 28
  • 33
  • I did a pretty detailed example using Pentaho Kettle to achieve something very similar to this http://stackoverflow.com/q/4175566/164552 – rwilliams Nov 19 '10 at 18:52
  • related: http://stackoverflow.com/q/4136609/164552 – rwilliams Nov 19 '10 at 18:56
  • This appears to be very similiar to [3621798](http://stackoverflow.com/questions/3621798) or [8031193](http://stackoverflow.com/questions/8031193) – arober11 Dec 02 '12 at 23:21

1 Answers1

0

I usually read the whole Excel file into the database (I'm using Navicat for that, but that's your choice) and then I use UPDATE and INSERT INTO ... SELECT queries to build the new data.

AndreKR
  • 32,613
  • 18
  • 106
  • 168
  • Since the data was relatively simple, I scrubbed out all the commas and other offending characters and created a clean CSV. Then I created new tables with useful relationships like classes_products and vendors_classes. – Ofeargall Dec 02 '10 at 16:28
  • One other thing... If you've stumbled upon this and are trying to eliminate '*' from your EXCEL data before creating a CSV using the Find and Replace window you need to put the tilde '~' in front of the '*' otherwise EXCEL reads the '*' as a wildcard and imposes your requested replace on every record... – Ofeargall Dec 02 '10 at 16:31