How to import CSV file into MySQL with adding NULLS for empty fields?

Question

I have a CSV file from the US Census that looks like this:

"ZIP5","ZIP4","ZIP9","STATE CODE","STATE","COUNTY CODE","COUNTY NAME","CBSA CODE","CBSA  TITLE","CBSA LSAD","METRO DIVISION CODE","METRO DIVISION TITLE","METRO DIVISION LSAD","CSA   CODE","CSA TITLE","CSA LSAD"
"04841",,"04841","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04843",,"04843","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan     Statistical Area",,,,,,
"04846",,"04846","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04847",,"04847","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04848",,"04848","23","ME","027","Waldo County",,,,,,,,,
"04849",,"04849","23","ME","027","Waldo County",,,,,,,,,
"04850",,"04850","23","ME","027","Waldo County",,,,,,,,,
"04851",,"04851","23","ME","013","Knox County","40500","Rockland, ME","Micropolitan Statistical Area",,,,,,
"04852",,"04852","23","ME","015","Lincoln County",,,,,,,,,

The file has over 2 million records. Most of the records don't have data in all the fields.

Here is the MySQL record layout I defined for the above CSV file:

+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| id                   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| ZIP5                 | varchar(5)       | NO   |     | NULL    |                |
| ZIP4                 | varchar(5)       | NO   |     | NULL    |                |
| ZIP9                 | varchar(10)      | NO   |     | NULL    |                |
| STATE_CODE           | varchar(2)       | NO   |     | NULL    |                |
| STATE                | varchar(2)       | NO   |     | NULL    |                |
| COUNTY_CODE          | varchar(3)       | NO   |     | NULL    |                |
| COUNTY_NAME          | varchar(50)      | NO   |     | NULL    |                |
| CBSA_CODE            | varchar(5)       | NO   |     | NULL    |                |
| CBSA_TITLE           | varchar(50)      | NO   |     | NULL    |                |
| CBSA_LSAD            | varchar(50)      | NO   |     | NULL    |                |
| METRO_DIVISION_CODE  | varchar(5)       | NO   |     | NULL    |                |
| METRO_DIVISION_TITLE | varchar(50)      | NO   |     | NULL    |                |
| METRO_DIVISION_LSAD  | varchar(50)      | NO   |     | NULL    |                |
| CSA_CODE             | varchar(3)       | NO   |     | NULL    |                |
| CSA_TITLE            | varchar(50)      | NO   |     | NULL    |                |
| CSA_LSAD             | varchar(50)      | NO   |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

(I just realized I should define ZIP5 as a Primary key?)

I have read that if you have an empty field in a CSV file, you should change it to \N, but is there a way to do this easily? I could write a PHP program to do this, but with over 2 million records it would take a very long time and my server doesn't have a lot of RAM.

How can I import this CSV file to MySQL successfully the easiest way? Are there some parameters on the LOAD command in MySQL that would do this? The way it works now, it complains that ZIP5 has data truncation and when I look in MySQL it has quotes in the zip code and only the first 4 digits. Thanks!

ptheofan · Accepted Answer · 2012-10-13 19:05:25Z

1

For a start, I see no primary key on the table you post above. First must always have a primary key. Usually we add a column called id with AUTOINCREMENT. For Zip codes and stuff it's also convenient to describe a complex key of 2-3 columns. As always depends on the circumstances.

As for the import. You have some solutions

Run the script locally to generate SQL insert statements and then feed the data to the mysql server through any interface you have available.
upload the CSV file to the server and use the command line mysql to import the CSV. MySQL has a built in CSV importer, though I never liked it ;)
Run the script on the server and add a row at a time. In PHP you can either load the CSV line by line and INSERT on each line (remember to set_time_limit and memory_limit accordingly). Reminder, for step3, if you run it through a browser and not through command line then your browser will most probably time out. Rest assured through the script will not stop running until it's over.

I think I have a CSV importer (for HUGE CSV files - like geotagging). Let me know if you need it, I might be able to find it and post it here.

Unfortunately I could not find my csv importer. But looking at the php manual's first entry for fgetcsv and with a couple modifications...

set_time_limit(3600); // 1 hour max script execution time. Adjust it according to your expectations.
if (($handle = fopen("test.csv", "r")) !== FALSE) {
    // this will automate things but modify the csv head for each column to represent the actual column name in your table.
    $header = fgetcsv($handle, 1000, ",");
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $i = 0;
        $values = array();
        foreach($header as $key) {
            if (!empty($data[$i])) {
               $values[$key] = $data[$i];
           }
        }

        $keys = "`" . implode("`, `", array_keys($values)) . "`";
        $values = "'" . implode("', '", $values) . "'";
        $statement = "INSERT INTO `table_name` ({$keys}) VALUES ({$values})";
        // run the statement. the above is if you don't use PDO. For PDO transform accordingly. $values holds the column_name => value pairs. The values that can be null and should not be inserted you should give them default values in your mysql schema (table)
    }
    fclose($handle);
}

I hope this helps. Haven't tested it but looks ok ;)

edited Oct 13, 2012 at 19:05

answered Oct 13, 2012 at 11:33

ptheofan

2,3003 gold badges25 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Edward Over a year ago

Thanks for your helpful reply! Yes, if you could post it, that would be very helpful! Thanks!

Edward Over a year ago

I updated the table as you suggested to add AUTO_INCREMENT primary key. Can you explain what you mean by, "For Zip codes and stuff it's also convenient to describe a complex key of 2-3 columns." ? Thanks!

ptheofan Over a year ago

Depends on how the zip codes are organised in the country. I am not aware of how zip codes are organised in the US but for instance you could make ZIP5 and STATE_CODE the primary key. If ZIP5 is unique throughout the table then could as well just this column be the primary key. Regarding the CSV importer I wrote some time back then, I'll try to locate it (I'm sure I have it somewhere around) and post it here today.

user1473461 Over a year ago

ptheofan is correct about multiple fields may be required. We use multiple Census files and some have one state per ZIP Code and others will have multiple states. Most people don't know it, but there are ZIPs that cross state lines. ZIP Codes are defined by the USPS for their own internal use, based on the best route for a carrier to take, which sometimes it's better for them to cross a state line because their post office is closer to the area than the nearest post office in the other state.

air4x · Accepted Answer · 2012-10-13 14:28:55Z

0

Try the below LOAD command after changing the file path and if required the line ending.

LOAD DATA INFILE 'your_file.csv' IGNORE
INTO TABLE zipcodes
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(ZIP5, ZIP4, ZIP9, STATE_CODE, STATE, COUNTY_CODE, COUNTY_NAME, CBSA_CODE, 
CBSA_TITLE, CBSA_LSAD, METRO_DIVISION_CODE, METRO_DIVISION_TITLE, 
METRO_DIVISION_LSAD, CSA_CODE, CSA_TITLE, CSA_LSAD);

answered Oct 13, 2012 at 14:28

air4x

5,6831 gold badge26 silver badges36 bronze badges

Collectives™ on Stack Overflow

How to import CSV file into MySQL with adding NULLS for empty fields?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related