From CSV into MySQL database

[rcouser]

How would I go about doing the following:

 

I have a csv like this

"Division","Section","Group","Product Code","Description","Description + Secondary Description"

"Division 1","Section 1","Group 1","BMSLPL25","Test Name","Test Description"

"Division 1","Section 1","Group 2","BMSLPL26","Test Name 2","Test Description 2"

"Division 2","Section 2","Group 2","BMSLPL27","Test Name 3","Test Description 3"

 

I have a database structured like this

Divisions

---

id

name

parent_id

 

Groups

---

id

name

division_id

 

Products

---

id

code

description

secondary_description

 

Section is a sub division. What is the best way to get the information from CSV into this database? Should I have another table and store the CSV data as is and then query that to make the other tables.

Any help much appreciated.

[.josh]

I'd personally import into a single table first and then create your other tables from that, yes.  Mostly because it will be less work involved on the php end to pop data.  No having to to keep running through csv file and looping multiple times, etc..

[rcouser]

Thanks for your reply. What about creating an array from the CSV and using that instead of querying the database?

[smoseley]

I've done this type of thing many times, and there are many variables to consider.

 

1) If you're going to do this often, then

  a) If you'll have multiple CSV formats, you should create a "mappings" table that maps your CSV data to your DB so that you can wire everything together for import, or

  b) If you'll only have one CSV ever, create a php file that can parse out the data and import it properly

 

Either way, create a CSV parser that can process the file line-by-line and record a status for each line successfully written so you can restart it if it fails and pick up where it left off. 

 

If you're importing very large CSVs (more than 2-3 mb), use an Iterator model for parsing them (not simple).  I've imported multi-GB CSVs in the past.  They will break PHP (consume too much memory) if you try to parse them into an array.

 

2) if you're only doing this once, use a temporary table with LOAD DATA: http://dev.mysql.com/doc/refman/5.1/en/load-data.html  then use INSERT SELECT to distribute the data to the proper tables.

[rcouser]

Brilliant smoseley, cheers for the help