CODEWITHSUNDEEP
sql-serverdatabase
giraffe86
giraffe86

Reputation: 25

DB import from a txt file into SQL server doesn't work

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

Maxmind Free World Cities Database

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')

But the following error comes out:

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?

Upvotes: 1

Views: 599

Answers (1)

gofr1
gofr1

Reputation: 15977

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

Then load like this:

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;

On my old notebook, this insert lasts ~5 minutes

Upvotes: 1

Related Questions