I have a massive flat text file with ~4M records that I would like to read into MySQL using Ruby/Rails, Python, or any other method.
The flat file is roughly this format: business_name,address,city,state,zip,employee_name
If there are multiple employees at the business, there will be one row for each employee with the business name, address, etc. repeated.
I'd like to read this in to two tables structured with the employees table having a foreign key like so:
businesses (id int, name varchar, address varchar, city varchar, etc.) employees (id int, name varchar, business_id int)
How do I go about efficiently reading this file in so that the employees/businesses are created and referenced as needed?
Note: it is NOT safe to assume that the file is sorted.
I've tried reading the file into SQL first and then using SQL to get a unique list of business_name/address/city/state combinations that I could then insert into the businesses table, but my server runs out of memory. And this is only with 1/4th of the records loaded.