Help & Support

Database structure and formats

General information

All our files are based on the same structure, however and depending on the country, some fields may remain void.

Database structure :

Primary key :

Content :

	Description	Encoding	Extension
	Microsoft Excel	UTF-8	.xls
	Microsoft Access	UTF-8	.mdb
	Text delimited	UTF-8	.csv
	Unformatted ASCII Text	ASCII	.asc
	XML structure	UTF-8	.xml
	MySQL database	UTF-8	.sql
	SQL Server 5	UTF-16	.dat

Importing data in your application

You can easily import a GeoPostcodes database in a wide range of applications.

Microsoft Excel (.xls)

The .xls format is compatible with all Excel versions but is limited to 65536 rows and may not be working for non-Latin chars. If you have Excel 2007 or 2010 you can use the .csv file to get up to 1 million rows.

In case of problem, you can import the .csv file manually :

- In the File|Open menu, select your GeoPostcodes text file (GeoPC_xx.csv)
- In import wizard, select Delimited data type and 65001 : Unicode (UTF-8) encoding
- Click on Next, select Semicolon as delimiter and " as text qualifier, click on Finish

Maximum size :

- Excel 2003 and earlier : 65536 rows (.xls)
- Excel 2007/2010 : 1048576 rows (.csv)

Microsoft Access (.mdb)

We do not provide native Access databases, however it is really easy to import a GeoPostcodes database in Access, here is how to do :

- Open or create a new Access database
- Go to File|Get External Data|Import in the menu, select your file (GeoPC_xx.csv)
- Select Delimited, click on Advanced and select Unicode (UTF-8) as Code Page
- Click Next, select Semicolon as delimiter and " as Text qualifier
- Tick First row contains field names and click on Finish

Text delimited (.csv)

This is a very common format, most applications can import text delimited files. There is one record per line, fields are separated by a semicolon, text are enclosed by double quotes ("). UTF-8 encoding.

Unformatted ASCII Text (.asc)

The format of this file is the same than above .csv but without accented or special characters used in foreign languages. You will find more information on the ASCII format on this page.

XML structure (.xml)

Another common format, can be used for website development.

MySQL database (.sql)

The .sql file contains both queries and data to insert a new GeoPostcodes table in your MySQL database.

phpMyAdmin :

Click on the Import tab, select your GeoPC_xx.sql file and utf8 as Character set.

Command line :

# mysql --default-character-set=utf8 --user=username --password=password database

mysql> source GeoPC_xx.sql

If you prefer to create the table and import the data manually, here is the query :

DROP TABLE IF EXISTS GeoPC;

CREATE TABLE GeoPC (
  Country varchar(2) NOT NULL,
  Language varchar(2) NOT NULL,
  ID bigint(20) NOT NULL,
  ISO2 varchar(6) NOT NULL,
  Region1 varchar(60) NOT NULL,
  Region2 varchar(60) NOT NULL,
  Region3 varchar(60) NOT NULL,
  Region4 varchar(60) NOT NULL,
  ZIP varchar(10) NOT NULL,
  City varchar(60) NOT NULL,
  Area1 varchar(80) NOT NULL,
  Area2 varchar(80) NOT NULL,
  Lat double NOT NULL,
  Lng double NOT NULL,
  TZ varchar(30) NOT NULL,
  UTC varchar(10) NOT NULL,
  DST varchar(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

LOAD DATA INFILE '/path/GeoPC_xx.csv' INTO TABLE yourdb.GeoPC FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n';

Microsoft SQL Server (.dat)

To import a GeoPostcodes table in Microsoft SQL Server, you need to use the GeoPC_xx.dat file with the BULK INSERT command :

CREATE TABLE [GeoPC] (
  [Country] [nvarchar](2) NULL,
  [Language] [nvarchar](2) NULL,
  [ID] [bigint] NULL,
  [ISO2] [nvarchar](6) NULL,
  [Region1] [nvarchar](60) NULL,
  [Region2] [nvarchar](60) NULL,
  [Region3] [nvarchar](60) NULL,
  [Region4] [nvarchar](60) NULL,
  [ZIP] [nvarchar](10) NULL,
  [City] [nvarchar](60) NULL,
  [Area1] [nvarchar](80) NULL,
  [Area2] [nvarchar](80) NULL,
  [Lat] [float] NULL,
  [Lng] [float] NULL,
  [TZ] [nvarchar](30) NULL,
  [UTC] [nvarchar](10) NULL,
  [DST] [nvarchar](1) NULL
)
GO

BULK INSERT GeoPC FROM 'Path\GeoPC_xx.dat' WITH (FIELDTERMINATOR = ';', DATAFILETYPE = 'widechar')

If you get a Msg 4864 error, you will have to change the decimal symbol from comma to dot in your regional settings (or replace all dots in the GeoPC_xx.dat file).

If you have any other questions, please feel free to contact us.

Copyright © 2007-2012 by GEODATA LTD - All rights reserved

About us  |  Terms of use  |  Contact us