将Maxmind导入Server

Question

我已经从MaxMind-http://www.maxmind.com/app/geolitecountry下载了GeoLiteCountry CSV文件。使用给我的格式作为标准(这样就可以成为一个自动化的任务)，我正在尝试将所有的数据导入到一个表中。

我创建了一个新的表IPCountries2，其中的列与提供的列完全匹配：

FromIP       varchar(50),
ToIP         varchar(50),
BeginNum     bigint,
EndNum       bigint,
CountryCode  varchar(50),
CountryName  varchar(250)

使用我可以找到的各种代码块，我无法使用字段终止符和行终止符使其工作：

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
    FIELDTERMINATOR = '","',
    ROWTERMINATOR = '\n'
)
GO

这样做的结果是插入了一行，除了最后一行已经溢出了下一行(如果我没有限制的话，大概是整个数据库)。此外，第一个单元格在开始时有一个引号。

我环顾四周，发现了一个叫做格式化文件的东西(从未使用过这些文件)。做了一个看起来像：

10.0
6
1   SQLCHAR     0   50  "," 1   FromIP      ""
2   SQLCHAR     0   50  "," 2   ToIP        ""
3   SQLBIGINT   0   19  "," 3   BeginNum    ""
4   SQLBIGINT   0   19  "," 4   EndNum      ""
5   SQLCHAR     0   50  "," 5   CountryCode ""
6   SQLCHAR     0   250 "\n"    6   CountryName ""

但这一错误出现在两行文字上：

Msg 4867，第16级，状态1，第1行 第1行第3列(BeginNum)的大容量加载数据转换错误(溢出)。

它做了10次，然后停止，因为最大的错误计数。

如果我把第一个方法放入Excel并重新保存，我就能得到第一个方法，这就去掉了引号。但是，我不想依赖这个方法，因为我希望它每周都会自动更新，而不必手动打开和重新保存。

我不介意我最终使用的两种方法中的哪一种，只要它与一个干净的文件一起工作。我看过他们的文档，但他们只有PHP或MS访问的代码。

编辑

CSV文件中的一些行：

"1.0.0.0","1.0.0.255","16777216","16777471","AU","Australia"
"1.0.1.0","1.0.3.255","16777472","16778239","CN","China"
"1.0.4.0","1.0.7.255","16778240","16779263","AU","Australia"
"1.0.8.0","1.0.15.255","16779264","16781311","CN","China"
"1.0.16.0","1.0.31.255","16781312","16785407","JP","Japan"
"1.0.32.0","1.0.63.255","16785408","16793599","CN","China"
"1.0.64.0","1.0.127.255","16793600","16809983","JP","Japan"
"1.0.128.0","1.0.255.255","16809984","16842751","TH","Thailand"
"1.1.0.0","1.1.0.255","16842752","16843007","CN","China"
"1.1.1.0","1.1.1.255","16843008","16843263","AU","Australia"
"1.1.2.0","1.1.63.255","16843264","16859135","CN","China"
"1.1.64.0","1.1.127.255","16859136","16875519","JP","Japan"
"1.1.128.0","1.1.255.255","16875520","16908287","TH","Thailand"

更新

经过一些持久化之后，我能够用原来的方法(没有格式文档) 95%地完成工作。但是，它略有改变，如下所示：

BULK INSERT IPCountries2
FROM 'c:\Temp\GeoIPCountryWhois.csv'
WITH
(
    FIELDTERMINATOR = '","',
    ROWTERMINATOR = '"'
)
GO

所有的事情都是在正确的领域中进行的，我唯一的问题是在第一栏中有一个引号。一些样本数据：

FromIP  ToIP    BeginNum    EndNum  CountryCode Country
 "2.21.248.0    2.21.253.255    34994176    34995711    FR  France
 "2.21.254.0    2.21.254.255    34995712    34995967    EU  Europe
 "2.21.255.0    2.21.255.255    34995968    34996223    NL  Netherlands

Answer 1

成功。四处搜寻和另一个论坛的一些帮助终于让我找到了我的解决方案。对于那些需要类似解决方案的人，请继续阅读：

最后我使用了格式化文件方法--我不确定是否可以使用字段终止符和行终止符。

我的SQL代码如下所示：

CREATE TABLE #TempTable
(
    DuffColumn  varchar(50),
    FromIP      varchar(50),
    ToIP        varchar(50),
    BeginNum    bigint,
    EndNum      bigint,
    CountryCode varchar(50),
    CountryName varchar(250)
)

BULK
INSERT #TempTable
FROM 'c:\Temp\GeoIPCountryWhois.csv'
WITH
(
    FORMATFILE = 'C:\Temp\format.fmt'
)

INSERT INTO IPCountries2 (FromIP, ToIP, BeginNum, EndNum, CountryCode, Country)
    SELECT FromIP, ToIP, BeginNum, EndNum, CountryCode, CountryName FROM #TempTable

正如我在研究中发现的那样，有必要有一个无用的专栏，它只是简单地捕捉到了第一句话。

我的格式文件看起来如下：

10.0
7
1   SQLCHAR     0   1   ""      1   DuffColumn  ""
2   SQLCHAR     0   50  "\",\"" 2   FromIP      ""
3   SQLCHAR     0   50  "\",\"" 3   ToIP        ""
4   SQLCHAR     0   19  "\",\"" 4   BeginNum    ""
5   SQLCHAR     0   19  "\",\"" 5   EndNum      ""
6   SQLCHAR     0   50  "\",\"" 6   CountryCode ""
7   SQLCHAR     0   250 "\"\n"  7   CountryName ""

注意，尽管最终被存储为BIGINT，BeginNum和EndNum都是作为SQLCHARS传入的，否则insert会对数字执行奇数乘法(关于以字节而不是数字的形式读取它，我并不完全理解)。

仅此而已。完全自动化此脚本的最后一件事是首先截断表，以便清除旧记录。然而，这可能并不是每个人都需要的。

Answer 2

试试这个命令。我所做的就是从你的FIELDTERMINATOR中删除双引号：

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
)
GO

您的数据字段实际上以逗号结尾，而不是用引号包装的逗号。我还建议构建一个临时/导入表，与源文件的数据类型完全匹配，在本例中如下所示：

FromIP       varchar(50),
ToIP         varchar(50),
BeginNum     varchar(50),
EndNum       varchar(50),
CountryCode  varchar(50),
CountryName  varchar(250)

BeginNum和EndNum的源数据实际上是字符串，而不是bigint。一旦将数据导入到暂存表中，就可以转换这些数据。

Answer 3

declare @sql varchar(1000)
declare @filename varchar(100) = 'C:\Temp\GeoIPCountryWhois.csv'

set @sql = 
'BULK INSERT geoip FROM ''' + @filename + ''' 
WITH
(
CHECK_CONSTRAINTS,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''' + char(0x0A) + '''
)'
exec (@sql)