将Maxmind CSV导入到SQL Server中
我已经从Maxmind下载GeoLiteCountry
CSV文件 - http://www.maxmind.com/app/geolitecountry。使用给我的格式作为标准(以便这可以成为一个自动化的任务),我试图将所有的数据导入到一个表中。将Maxmind CSV导入到SQL Server中
我创建了一个新的表提供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 ""
但对BIGINT线这样的错误:
消息4867,级别16,状态1,行1
为行批量加载数据转换错误(溢出) 1,第3列(BeginNum)。
它做了10次,然后停止因为最大错误计数。
我能够得到第一个方法,如果我把它放入Excel并重新保存,这将删除引号。但是,我不想依赖这种方法,因为我希望这个方法每周自动更新一次,而不必手动打开并重新保存。
我不介意我最终使用的两种方法中的哪一种,只要它使用干净的文件。我看了他们的文档,但他们只有PHP或MS Access的代码。
编辑
从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
成功。 Searching around和another forum的一些帮助终于让我看到了我的解决方案。对于那些需要类似解决方案的人,请继续阅读:
我结束了使用格式文件方法 - 是否可以使用fieldterminators和行终止符我不确定。
我的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,否则插不上号奇数乘(某事关于读取它作为字节而不是数字,我没有完全理解它)。
就是这样。完全自动完成此脚本的最后一件事是首先截断表以清除旧记录。但是,这可能不是每个人的需要。
试试这个命令。我所做的一切是从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。您可以在将数据导入暂存表后将其转换。
请看我对OP的评论,可悲的是这并没有帮助 – Chris 2012-03-20 16:55:21
我刚使用这篇文章,http://www.webstein.net/blog/importing-maxmind-ip-database-into-sql-server。
在7分钟内完成!
感谢劳尔。
它节省了我很多的痛苦处理与其他文章如何导入这样的文件。
我做的唯一的额外事情是从两个文件中删除第一行。我使用了Notepad ++,它也完成了这项工作。
欢迎:) – ra00l 2013-11-18 17:04:25
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)
您可以发布CSV文件的前几行数据吗? – datagod 2012-03-20 16:42:56
啊是我多么愚蠢,在我的OP中找到 – Chris 2012-03-20 16:45:35
在你的原始批量插入中,你指定FIELDTERMINATOR为'“,”'。为什么逗号周围有双引号? – datagod 2012-03-20 16:48:25