Import World Cities Database

When you are working in data analysis or data science, it will not take a long time before you want to enrich your data with other sources. You can get data from all kinds of sources, like systems in your company, but also from external sources from the internet.

In this article, you will learn how to add the location data of cities to your database. Since a lot of visualization tooling, like Microsoft PowerBI, or Tableau, uses the latitude and altitude data, it is useful to add this to your data. However, most systems within companies do not store that kind of data. In this article, we explain how you can easily use the SQL DataTool to import data from the World cities database to your SQL Server database. 

A source that we have been using in the past to do exactly that, is the World Cities Database. You can download a limited version for free in comma separated file (CSV) or MS Excel format. A paid version is also available in SQL code (MySQL). To get this data in your MS SQL database, you might need some help. SQL DataTool offers an easy way to get this kind of data into your database, so you can spend your time and effort to get to work with the data.

The World Cities Database contains information about the cities and towns, longtitude and latitude, country codes, population and density etc. This information can be very useful in your management information and your location analysis. To be able to use this information, your first task is to import the world cities database into your own SQL Server database.

World Cities Datamodel

Before you import the data using the SQL DataTool, you first have to define the data model. This data model is used to create the table in the right format, assign the key to the table, and import the data in the right datatypes and lengths and precision. You can define the data model yourself, but we also have a data model that you can import in SQL DataTool directly. You can download the data model for the world cities database via the link below.

The data model itself is basically one table. The image below shows a screenshot of the data model of the World Cities database taken from the SQL DataTool.

The next step is to import the data to your SQL Server database. Of course, you first need to be connected to the right server and database. Using the SQL DataTool it is easy to add the same data to multiple databases on different SQL Server instances.

Import World Cities database

As mentioned, you can download the World Cities database in the format of a CSV-file or Excel-file. Using the SQL DataTool, it is possible to bulk import the data for both filetypes. In the next section, we will start how to import the world cities database from an Excel file.

World Cities database in Excel

When you have downloaded the World cities database as an Excel file, you should use the import 'excel files' functionality in SQL DataTool. This option is available on the 'Import data' screen in SQL DataTool.  First, select the data model ('Select system' and 'Select version') in which you have defined the table. Since a data model in SQL DataTool can contain multiple tables, you have to select the right table later in the process.

When you have downloaded and imported the data model that we have offered above, you have to select as system 'Worldcities' and as version 'worldcities'.

If you want to add a prefix to the table, you have to check the 'Table prefix' option. Here you can either enter your prefix, or you can check the 'Existing prefix' option and select a previously used prefix.

Next, you have to select as 'Source type' the 'MS Excel (.xls, .xlsx)' option. When you have done this, you can select the 'Source file', which is the worldcities.xlsx file from the location where you have downloaded the file to.

SQL DataTool will read all sheets from the selected Excel file. For the world cities database, there is only one sheet (named 'Sheet1') in the excel file. To import the data to the right table, select the worldcities table in the 'Table' column. The screenshot below shows how this looks like in the SQL DataTool.

You can also append data to a Table. If you want to do this, check the 'Append?' checkbox. In this case, you need to leave this box unchecked, since the world cities database is a complete set.

Now click on 'Get data', and the data will be imported to your MS SQL Database. When the import is finished, you will get a pop up telling you that the data was succesfully imported.

In the next part, we will show you how to import the data as a comma separated text file (CSV file).

World Cities database in CSV

A CSV, or comma separated, file is a plain text file format. Although you can open it in Excel, we do not recommend you to do this before importing the data using the SQL DataTool. Opening and saving a CSV text file in Microsoft Excel might change the format of the file itself (e.g. additional quotes (") will be added to each line).

To import a CSV file, you an start the same way as you did to import an Excel file. The selection of the data model (system and version) is the same, and you can define whether you want add a prefix to the table name or not. However, the source type is different. Instead of an Excel file, you need to select the 'Text files (.txt, .csv.)' option in the 'Source type' drop down. You can then browse to the location where you stored the world cities database csv file and select the file.

When you have selected the source type 'Text file', the 'Text file settings' section will show up. To import the World cities database using the CSV file, you have to enter the details of the text file in this 'Text file settings' section of the SQL DataTool. You can define the details of an import and save this in SQL DataTool. This way, you can reuse the details of an import easily. For the world cities database we have prepared the import settings details for you. You can download these import settings in the file below.

When you have imported the 'Saved imports' file, using the import feature, the settings will look like the image below.

You can also enter the settings yourself, and save the import settings to be able to reuse these settings later.

In the CSV file of the World cities database, the column separator is a comma. The file also contains text identifiers, which is the double quote. SQL DataTool will identify the datafields based on these settings. The decimal separator is a dot.

Now you only have to select the file and import the data by clicking on the 'Get data' button. The World cities database is now imported into your MS SQL server database in the right format, and the tablename is 'worldcities' (based on the data model defined earlier). Again, on succesfull import, a pop up will show up telling that you have succesfully imported the data into your database.

Since you have the right settings for SQL DataTool to import the World cities database, you are now ready to import every new update of the World cities database to your own SQL Server databases easily.

Now you have the world cities database information availble in your database, you can enrich your reporting and analytics with this data. You can now upgrade your dashboards with location data and add world maps to these dashboards.