MySQL Forums
Forum List  »  PHP

Importing a Tab-Delimited file into multiple tables
Posted by: Geoffrey Hoffman
Date: December 27, 2010 04:01PM

I've written custom code for this over and over and I'm tired of re-inventing the wheel.
Basically the problem is this. You receive a spreadsheet. You export it to a tab-delimited (or CSV or whatever) text file, so you can import it into MySQL.
Spreadsheets are de-normalized. Your database (hopefully) is normalized. So, what's the best way to import the data into multiple normalized tables?
Specifically, The data in column1 (values repeated down thousands of rows) needs an insert into table1 such that id1 primary key is the insert_id for subsequent rows with the same column1 value.
The data in column2 (repeated down hundreds of rows) needs table1.id1 as a foreign key, and gets auto increment table2.id2 as pkey created.
Column3 needs table2.id2 as fkey, and table3.id3 created as a pkey.
... to Column N.

Solutions given to me include:
1) Create an import table, and process rows once they're in MySQL.
2) nested foreach, read the text file line by line, run it from php on the command line.
3) Generate UUIDs (eg SHA1 hashes) then go matching & finding based on this other piece of info *(unique to each row in the spreadsheet)

Seems like a lot of smart people would've figured out something open ended and reusable for this problem already. Any suggestions?

Options: ReplyQuote


Subject
Written By
Posted
Importing a Tab-Delimited file into multiple tables
December 27, 2010 04:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.