import IP2Location IP2LOCATION-LITE-DB5.CSV file to SQL server-CodePudding

I had a look to the IP2 Location database for SQL server 2019, BCP import or TSQL openrowset, Import Data wizard, all fail

Had no luck with the FMT file as it's the wrong version, no problem I guess I will generate it using

bcp [ip2location].[dbo].[IP2LOCATION-LITE-DB5] format nul -T -N -f D:\IP2LOCATION-LITE-DB5.CSV\DB5.fmt

The issue I have is an error:

Cannot bulk load CSV file. Invalid field parameters are specified for source column number 1 in the format file "D:\IP2LOCATION-LITE-DB5.CSV\DB5.FMT". All data fields must be either character or Unicode characters with terminator when CSV format is specified.

The SQL I use to test:

select top(10) * 
from openrowset(BULK N'D:\IP2LOCATION-LITE-DB5.CSV\IP2LOCATION-LITE-DB5.CSV'
,FORMATFILE = N'D:\IP2LOCATION-LITE-DB5.CSV\DB5.FMT'
, FORMAT='CSV') AS DATA

I can't seem to be able to import IP2LOCATION-LITE-DB5.csv

CodePudding user response：

Based on the FAQ page https://www.ip2location.com/faqs/db5-ip-country-region-city-latitude-longitude#database, you can create the table and import as below:

CREATE DATABASE ip2location
GO

USE ip2location
GO

CREATE TABLE [ip2location].[dbo].[ip2location_db5](
    [ip_from] bigint NOT NULL,
    [ip_to] bigint NOT NULL,
    [country_code] nvarchar(2) NOT NULL,
    [country_name] nvarchar(64) NOT NULL,
    [region_name] nvarchar(128) NOT NULL,
    [city_name] nvarchar(128) NOT NULL,
    [latitude] float NOT NULL,
    [longitude] float NOT NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [ip_to] ON [ip2location].[dbo].[ip2location_db5]([ip_to]) ON [PRIMARY]
GO 

BULK INSERT [ip2location].[dbo].[ip2location_db5]
  FROM 'D:\IP2LOCATION-LITE-DB5.CSV\IP2LOCATION-LITE-DB5.CSV'
  WITH
    (
      FORMAT = 'CSV',
      FIELDQUOTE = '"',
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '0x0D0A',
      TABLOCK
    )
GO