I needed a quick list of countries and time zones and I wanted to add it to my database so I could manipulate it easily for a new application we’re developing (details on that coming soon). I looked around but didn’t find what I wanted so after a bit of searching I took a few static select lists and copied them into Excel. I then imported the lists into my database using ColdFusion. In case they help anyone I’ve made the lists available here as CSV files.
Time Zones (.csv, 4 KB)
Countries (.csv, 4 KB)
<!--- get and read the CSV-TXT file ---> <cffile action="read" file="time_zones.csv" variable="csvfile"> <!--- loop through the CSV-TXT file on line breaks and insert into database ---> <cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#"> <cfquery name="importcsv" datasource="my_dsn"> INSERT INTO time_zones (offset,name) VALUES ('#listgetAt('#index#',1, ',')#','#index#',2, ',')#') </cfquery>> </cfloop> </cffile>
The ColdFusion code assumes that you have setup a Datasource named “my_dsn” and that you have created a database with a table named “time_zones” that has three columns: time_zone_ID (set to automatically increment as a Primary Key), offset (with a FLOAT data type), and name (VARCHAR data type).
The code snippet reads in the CSV file and inserts it into the database, you should be able to do something very similar using the language of your choice. Feel free to download the .CSV files and play around with them or use them in your own projects. If you have any improvements to make to them, such as more information on things like daylight savings time or country codes) feel free to send them to us and we’ll make them available them here.