need to do a bulk insert any help would be great

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

need to do a bulk insert any help would be great

Author

Topic

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-02-06 : 16:26:30

Copyright (c) 2007 MaxMind LLC. All Rights Reserved.
locId,country,region,city,postalCode,latitude,longitude,metroCode,areaCode
1,"O1","","","",0.0000,0.0000,,
2,"AP","","","",35.0000,105.0000,,
3,"EU","","","",47.0000,8.0000,,
4,"AD","","","",42.5000,1.5000,,
5,"AE","","","",24.0000,54.0000,,
6,"AF","","","",33.0000,65.0000,,
7,"AG","","","",17.0500,-61.8000,,
8,"AI","","","",18.2500,-63.1667,,
9,"AL","","","",41.0000,20.0000,,

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'GeoLoc')BEGIN
DROP TABLE GeoLoc
END

CREATE TABLE GeoLoc (
locId bigint,
country nvarchar(2),
region nvarchar(3),
city nvarchar(100),
postalCode nvarchar(10),
latitude nvarchar(15),
longitude nvarchar(15),
metroCode nvarchar(5),
areaCode nvarchar(5)
)

SET @SQL1='BULK INSERT GeoLoc FROM ''' + @GeoLiteCity_Location + ''' WITH (FIRSTROW = 3, formatfile=''c:\pathto\GeoLiteCity-Location.fmt'')'
EXEC(@sql1)
CREATE CLUSTERED INDEX Geo_Info_Look
ON GeoLoc
([locId], [country], [region], [city], [latitude], [longitude])

Any help would be awesome.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 16:45:37

Are you getting an error?
The only thing I would do is specify the database and schema as well as the table name. Are you getting an error?

SET @SQL1='BULK INSERT databasename.dbo.GeoLoc FROM ''' + @GeoLiteCity_Location + ''' WITH (FIRSTROW = 3, formatfile=''c:\pathto\GeoLiteCity-Location.fmt'')'

Poor planning on your part does not constitute an emergency on my part.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-02-06 : 16:49:24

I have the sql file and fmt

Any help would be awesome.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-02-06 : 16:55:55

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 4. 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)".

Use [IMPORT]
EXEC MaxMind_Import
@GeoLiteCity_Blocks = 'c:\inetpub\wwwroot\OmegaLove\OmegaLove.Web\VbScripts\GeoLiteCity_20090601\GeoLiteCity_Blocks.csv',
@GeoLiteCity_Location= 'c:\inetpub\wwwroot\OmegaLove\OmegaLove.Web\VbScripts\GeoLiteCity_20090601\GeoLiteCity_Location.csv'

Any help would be awesome.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 17:00:30

quote:

The column is too long in the data file for row 1, column 4. Verify that the field terminator and row terminator are specified correctly.

I would love to check the data in your format file and your data file for you, but by DBCC OPENWORMHOLE function isn't enabled.

Verify that your format file is correct, and that the destination table structure matches it...

Poor planning on your part does not constitute an emergency on my part.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-02-06 : 17:01:06

http://blog.netnerds.net/2007/01/maxmind-geoip-import-maxmind-city-csv-into-sql-server-2005/

look at this site. I am stuck.

Any help would be awesome.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-02-06 : 17:03:35

USE [IMPORT]
GO
/****** Object: Table [dbo].[GeoLoc] Script Date: 02/06/2011 16:02:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GeoLoc](
[locId] [bigint] NULL,
[country] [nvarchar](2) NULL,
[region] [nvarchar](3) NULL,
[city] [nvarchar](100) NULL,
[postalCode] [nvarchar](10) NULL,
[latitude] [nvarchar](15) NULL,
[longitude] [nvarchar](15) NULL,
[metroCode] [nvarchar](5) NULL,
[areaCode] [nvarchar](5) NULL
) ON [PRIMARY]

USE [IMPORT]
GO
/****** Object: Table [dbo].[GeoLook] Script Date: 02/06/2011 16:03:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GeoLook](
[startIpNum] [bigint] NULL,
[endIpNum] [bigint] NULL,
[locId] [bigint] NULL
) ON [PRIMARY]

Any help would be awesome.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 17:11:47

The error was pretty clear, it is the format file. The data actually contained in the file contains more columns than the format file is specifying. This is likely caused by the import table not matching the source data columns, or the row-terminator not being specified properly.

What does the format file look like..and I assume that first table is where it is going?

I suggest creating a Import_GeoLoc table which matches ..exactly the CSV source and then create a format file for it.

BCP import.dbo.import_geoloc format nul -c -T -f 'c:\pathto\importgeoloc.fmt'

look at the resulting file and make sure the resulting format is consistent and has the right terminator specified..

Poor planning on your part does not constitute an emergency on my part.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-02-06 : 17:21:45

8.0
9
1 SQLCHAR 0 0 ",\"" 1 locId ""
2 SQLCHAR 0 0 "\",\"" 2 country ""
3 SQLCHAR 0 0 "\",\"" 3 region ""
4 SQLCHAR 0 0 "\",\"" 4 city ""
5 SQLCHAR 0 0 "\"," 5 postalCode ""
6 SQLCHAR 0 0 "," 6 latitude ""
7 SQLCHAR 0 0 "," 7 longitude ""
8 SQLCHAR 0 0 "," 8 metroCode ""
9 SQLCHAR 0 0 "\r\n" 9 areaCode ""

BCP import.dbo.import_geoloc format nul -c -T -f 'c:\inetpub\wwwroot\OmegaLove\OmegaLove.Web\VbScripts\GeoLiteCity_20090601\importgeoloc.fmt'

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Any help would be awesome.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-06 : 17:49:49

the path name doesn't have to be in quotes. That was my bad from copying your path and not editing it. It's not like I am actually running this code...just trying to point you down the right.

BCP import.dbo.import_geoloc format nul -c -T -f c:\inetpub\wwwroot\OmegaLove\OmegaLove.Web\VbScripts\GeoLiteCity_20090601\importgeoloc.fmt

That format file looks severely troubled. It should look more like this:

8.0
9
1 SQLCHAR 0 0 "," 1 locId SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 0 "," 2 country SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 0 "," 3 region SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 0 "," 4 city SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 0 "," 5 postalCode SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 0 "," 6 latitude SQL_Latin1_General_CP1_CI_AS
7 SQLCHAR 0 0 "," 7 longitude SQL_Latin1_General_CP1_CI_AS
8 SQLCHAR 0 0 "," 8 metroCode SQL_Latin1_General_CP1_CI_AS
9 SQLCHAR 0 0 "\r\n" 9 areaCode SQL_Latin1_General_CP1_CI_AS

Plus , even though the source data is numbers, you are loading into varchar (SQLCHAR) columns so those red sections need to have the proper lengths of the fields.
Format file instructions http://msdn.microsoft.com/en-us/library/ms191516.aspx

Poor planning on your part does not constitute an emergency on my part.

mcupryk
Yak Posting Veteran

91 Posts

Posted - 2011-02-06 : 20:02:25

BCP import.dbo.import_geoloc format nul -c -T -f c:\inetpub\wwwroot\OmegaLove\OmegaLove.Web\VbScripts\GeoLiteCity_20090601\GeoLiteCity-Blocks.fmt

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.

Any help would be awesome.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-07 : 04:51:15

The folder OmegaLove.Web has a dot that caused the problem. Can you put entire file names in quoble quotes and try?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources