Share via

Loading GeoLite City Data Into SQL Server Using the SQL Server Management Studio Import Data Wizard

  • Article

 

Author: Ed Katibah (Microsoft)

You can use SQL Server Management Studio to load the GeoLite City data files using the SQL Server Import and Export Wizard.  To launch the wizard, you will need to right-click on the desired database in the Object Explorer.  Select Tasks and then Import Data… from the menu options presented (Image 1):

 
Image 1

Select Next when the “Welcome…” splash screen is displayed (Image 2):

 
Image 2

At the Choose a Data Source screen (Image 3) do the following:

  1. Select “Flat File Source” from the pull-down menu
  2. Make sure that “General” is specified
  3. Browse to find the desired file
  4. Format should be “Delimited”
  5. Text qualifier should be a double quote symbol (“)
  6. The header row delimiter should be {LF}
  7. Header rows to skip should be set to 1
  8. Name the columns in the table using the first row in the data

Then select Next to move to the next screen.

 
Image 3

This screen (Image 4) should not require any input from you but check it to be certain, then select Next.

 
Image 4

This screen (Image 5) should not require any input from you but check it to be certain, then select Next.

 
Image 5

On the Select Source Tables and Views screen (Image 6), you will want to do the following:

    1  Change the Destination name for the table to be created.  
    2  Edit the column Mapping by selecting the Edit Mapping… button

The screens following the Select Source Tables and Views cover items 1 (Image 7) and 2 (Image 8), above.  When complete, select Next.

 
Image 6

Item 1: select GeoLiteCityBlocks for the name of the table to be created from the GeoLiteCity-Block.csv data file.  When processing the GeoLiteCity-Location.csv data file, change the name of the output table to GeoLiteCityLocations (Image 7).


Image 7

After selection of the destination name, choose the Edit Mappings… button.

You will need to change the Column Mappings for both GeoLite City data files.  Below is the column mapping for the GeoLiteCity-Blocks.csv file. 

Select OK when done (Image 8) and go to Save and Run Package screen (Image 9).

 
Image 8

For the GeoLiteCity-Location.csv file, you will be presented with the following Column Mapping screen (Image 8a).  Select Edit SQL...:


Image 8a

 You will need to paste the following create table DDL into the SQL statement window on the Create Table SQL Statement screen (Image 8b):

CREATE TABLE [dbo].[GeoLiteCityLocations](

     [locId]      [int]          NOT NULL,

     [country]    [nvarchar](5)  NULL,

     [region]     [nvarchar](55) NULL,

     [city]       [nvarchar](55) NULL,

     [postalCode] [nvarchar](10) NULL,

     [latitude]   [decimal](8,5) NULL,

     [longitude]  [decimal](8,5) NULL,

     [metroCode]  [nvarchar](5)  NULL,

     [areaCode]   [nvarchar](5)  NULL,

 CONSTRAINT [PK_GeoIPCityLocations] PRIMARY KEY CLUSTERED

 ([locId] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY];


Image 8b

After selecting OK, you are then returned to the Column Mappings screen (Image 8c).  You can safely ignore the warning message at the bottom of the screen.

 
Image 8c

 You can select Finish at the Save and Run Package screen (Image 9) to run the package.


Image 9

The following screen is presented at the successful conclusion of the data loading process (Image 10)

 
*Image 10

*The warning message, under Executing Action, looks worrisome but is was due to a server license getting ready to expire.