Dear Melissa Data…..
Melissadata is a data service provider that provides zip code data among other things. I recently had the opportunity to work with their ZIPdata. They provide thier data in downloadable .zip file. These files consist of (I assume) MSSQL dumps of thier database. Since work id not a Microsoft shop but rather and open source shop I had the pleasure of creating a Perl script that would take this data and make it appropriate to be loaded into a postres database. Now Melissadata was nice enough to separate the table creation from the data but in at least one instance the table create did not match the data. So I have some suggestions for the Melissadata team so they can offer everyone a better product
1. The *.sql files (the files with the create tables in them) should have at the minimum a standard SQL version that is appropriate for creating basic tables in MySQL PostgresSQL and Oracle.
2. The *.dat files should be exported in tab delimited format. This makes importation of this data into the chosen database really simple. This way I don’t have to parse each line based upon some string length to parse out the fields.
3. Double check that the fields that you are providing in the data files match the number of fields that are being created in the .sql file. (Hint the census.sql is missing the 62-64 age column). This broke my script because trusted that your .sql files were accurate and inline with your published users guide.
4. Your file names should match the names of the tables that the data or table create is associated with. Any standard dump (mysql_dump pg_dump) will do this for you.
Now that I have written this script I would be happy to write another one for Melissadata or anyone else who would like to import this data into their database. If you would like me to write you one let me know.
Share This-->