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 add more information to your data. You can get data from all kinds of sources, like systems in your company, but also from the internet. Say, for example, you want to add to your data the location of cities. 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 CSV or Excel format. A paid version is also available in SQL (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.

World Cities Datamodel

Before you import the data using the SQL DataTool, you first have to define the datamodel. You can define the datamodel yourself, but we also have a datamodel that you can import in SQL DataTool directly.

The datamodel itself is basically one table. The image below shows the datamodel of the World Cities database.

World Cities datamodel
World Cities datamodel

Next step is to import the data to the database. Of course, you first need to be connected to the right server and database.

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 use both filetypes. We first start with importing the 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. First, select the datamodel (system and version) where the table has been defined. When you have downloaded and imported the datamodel above, the system is 'Worldcities' and the version 'worldcities'. Next, select the worldcities.xlsx files from the location where you have downloaded the file to.

There is only one sheet (named 'Sheet1') in the excel file. Select the worldcities table to import the data to, like the screenshot below.

World cities as Excel
World cities database as Excel

Now click on Get data, and the data will be imported to your MS SQL Database.

In the next part, we will show you how to import the data as a CSV file.

World Cities database in CSV

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 import 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 the World cities database using the CSV file, the Text file settings needs to be entered. You can download the settings in the file below.

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

World cities database import settings
World cities database import settings

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 datamodel defined earlier).

World cities database imported data
World cities database imported data

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 database easily.

Contact us if you want to have a walkthrough on how to use the SQL DataTool to import data to your SQL Server database, or look at the manual.

 

Please contact us when you have questions, suggestions or remarks about our product or services.