Creating a database table with zip codes

Today I needed a database table in Sql Server that contains all Belgian zip codes and city names. Obviously I was not going to create this by hand, so I wanted to share how I did it because I think it’s a common requirement.

The first step was to find a list of zip codes and city names. On the site http://www.post.be/site/nl/residential/customerservice/search/postal_codes.html you can download this list in excel format. From this file I extracted the zipcode and city name and saved this as a csv-file, which then looked like:

1000;Brussel
1000;Bruxelles
1005;Ass. Réun. Com. Communau. Commune
1005;Brusselse Hoofdstedelijke Raad
1005;Conseil Region Bruxelles-Capitale
1005;Ver.Verg.Gemeensch.Gemeensch.Comm.
1006;Raad Vlaamse Gemeenschapscommissie
etc...

The second step was to create the database table that will contain this information, so I created a table called City with the following columns:

ZipCode, int
Name, varchar(100)

The next step was the actual import from the csv file into the table. To do this, I used the bulk copy technique: just open a new query window and execute the following statement:

BULK
INSERT City
FROM 'd:postcodes.csv'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = 'n'
)

And now the table City is filled with 2903 records:

City table

As a last step I added another column as the primary key:

Id, int, primary key (identity)

And now each entry has a primary key:

City table

I think this is probably the easiest way to create a table with zip codes and city names. Except maybe… if someone would give you the script to create and fill the City table 🙂 Well here it is (it seems I can only upload doc files, sorry for that): script_generate_zip_codes_belgium

Advertisements

One thought on “Creating a database table with zip codes

  1. Olivier Van Hege says:

    Hi Ludwig,

    You may find the following service interesting: http://www.geonames.org/

    It is apparently free and they also offer .NET (WCF) client libraries for accessing their services.

    Regards,
    Olivier

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s