将IP2Location IP2LOCATION-LIT-DB5.CSV文件导入server

Question

我查看了SQL server 2019、BCP导入或TSQL openrowset、import向导的IP2位置数据库，都失败了。

FMT文件没有任何进展，因为它是错误的版本，没有问题，我想我将使用

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

我遇到的问题是一个错误：

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.

用于测试的SQL：

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

我似乎无法进口IP2LOCATION-LITE-DB5.csv

Answer 1

根据常见问题页面https://www.ip2location.com/faqs/db5-ip-country-region-city-latitude-longitude#database，您可以创建表并导入如下：

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