DB从txt文件导入到SQL服务器不工作 [英] DB import from a txt file into SQL server doesn't work

问题描述

我试图将以下数据库（在txt文件中）导入我的SQL服务器数据库：

I'm trying to import the following database (which is in a txt file) into my SQL server database:

Maxmind免费世界城市数据库

我已定义一个适当的表，然后尝试以下查询导入：

I've defined a proper table and then tried the following query to import:

BULK INSERT Cities FROM 'C:\Users\***************\worldcitiespop.txt'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')

但是会出现以下错误：

消息4866，级别16，状态8，第1行批量加载失败。对于第1行第7列，数据文件中的
列过长。验证是否正确指定了
字段终止符和行终结符。消息7301，
级别16，状态2，行1无法从OLE DB提供程序BULK为链接服务器
（null）获取所需的接口
（IID_IColumnsInfo / p>

Msg 4866, Level 16, State 8, Line 1 The bulk load failed. The column is too long in the data file for row 1, column 7. Verify that the field terminator and row terminator are specified correctly. Msg 7301, Level 16, State 2, Line 1 Cannot obtain the required interface ("IID_IColumnsInfo") from OLE DB provider "BULK" for linked server "(null)".

似乎认为没有新的行行终结符。
我可以做什么来正确导入数据库？

It seems as thought there is no new line row terminator. What can I do to import the DB correctly?

推荐答案

首先创建 worldcitiespop。 fmt 文件（文件最后需要一个空行）：

At first create worldcitiespop.fmt file with this content (file needs a blank line at the very end):

10.0
7
1  SQLCHAR  0  10 ","        1  Country                SQL_Latin1_General_Cp437_BIN
2  SQLCHAR  0  100 ","        2  City                SQL_Latin1_General_Cp437_BIN
3  SQLCHAR  0  100 ","        3  AccentCity                SQL_Latin1_General_Cp437_BIN
4  SQLCHAR  0  100 ","        4  Region                SQL_Latin1_General_Cp437_BIN
5  SQLCHAR  0  10 ","        5  Population                SQL_Latin1_General_Cp437_BIN
6  SQLCHAR  0  20 ","        6  Latitude                SQL_Latin1_General_Cp437_BIN
7  SQLCHAR  0  20 "\n"      7  Longitude        SQL_Latin1_General_Cp437_BIN

然后加载如下：

INSERT INTO Cities
SELECT  a.Country, 
        a.City, 
        a.AccentCity, 
        a.Region, 
        CAST(a.[Population] as int) as [Population],
        CAST(a.Latitude as decimal(10,7)) as Latitude, 
        CAST(a.Longitude as decimal(10,7)) as Longitude
FROM OPENROWSET( 
    BULK 'D:\worldcitiespop.txt', FORMATFILE = 'D:\worldcitiespop.fmt', FIRSTROW = 2
    ) AS a;

在我的旧笔记本上，此插入持续约5分钟

On my old notebook, this insert lasts ~5 minutes

这篇关于DB从txt文件导入到SQL服务器不工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！