Importing the USDA SR22 into MySQL

Some of you who were interested in my post on importing SR21 may have been waiting for this one. I know it's been a few months since SR22 came out, but I didn't have a need to import it until just now. There are changes to this new version, but they are minimal, and you may have already patched the code yourself.

Specifically, two new fields were added to the ABBREVS table: Vit_D_mcg and Vit_D_IU. This brings the total column count in that table from 51 to 53. That number is the only change in the Perl file, and those two columns were the only additions to the SQL file. With those in place, I was able to import the new database without a problem. For the lazy and/or efficient, here are the new versions of the files:

import_sr22.pl
sr22.sql

Based on the reponse to my last post, I expect more troubleshooting questions on this post. For those who know what you're doing, you can stop reading now. Everyone else, check here before asking.

• You need to have at least Perl 5.8.6 installed.


• You need to have the Perl DBI installed, and DBD::mysql.


• In RHEL/CentOS/Fedora, these packages should be called perl-DBI and perl-DBD-MySQL.


• In Ubuntu/Debian, these packages should be called libdbi-perl and libdbd-mysql-perl.


• When you download the files, make sure you save the Perl script as import_sr22.pl, not import_sr22.txt.


• This script assumes you've downloaded the abbreviated file. It is a separate download from the full version, so make sure you don't miss it.

I think that covers all the questions I was asked previously. For those who are interested, The Eloquent Geek posted a non-Perl way of doing this on the last post. I haven't tried it myself, but for your reference:

For those who do not want to use the perl here is how you import the data from the command line client:
load data infile '/file_path/TABLE_NAME.txt' into table TABLE_NAME fields terminated by '^' optionally enclosed by '~' ;

Just substitute the table name per table.