SQL Server BCP copies 0 rows without error

The exercise is simple.

You have a text file with the following content:

route_id,route_short_name,route_long_name,route_desc,route_type,route_url
1-184,1,,,3,
2-184,2,,,3,
4-184,4,,,3,

and need to import this file into a table with the following schema:

CREATE TABLE [dbo].[BusRoutes](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RouteId] [varchar] (10) NOT NULL CONSTRAINT [NN_RouteId_BusRoutes] UNIQUE,
	[RouteInternalId] [int] NOT NULL DEFAULT -1,
	[Name] [varchar](10) NOT NULL,	
	[LongName] [varchar] (200) NULL,
	[Description] [varchar](200) NULL,
	[RouteType] [int],
	[RouteUrl] [varchar](300),
	[IsActive] [bit] NOT NULL CONSTRAINT [NN_IsActive_BusRoutes] DEFAULT 1,
 CONSTRAINT [PK_BusRoutes] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

The format file, you have defined as follows:

11.0
6
1       SQLCHAR             0       0     "," 		   2     RouteId                Latin1_General_CI_AS
2       SQLCHAR             0       0     "," 		   4     Name                   Latin1_General_CI_AS
3       SQLCHAR             0       0     ","		   5     LongName               Latin1_General_CI_AS
4       SQLCHAR             0       0     ","		   6     Description            Latin1_General_CI_AS
5       SQLCHAR             0       0     ","		   7     RouteType              ""
6       SQLCHAR             0       0     "\r\n"	   8     RouteUrl               Latin1_General_CI_AS

This format file looks suspect, but it worked last year!

You issue the following command to your Azure database from PowerShell or your favourite command line tool.

bcp %fullTablePath% in %data_dir% -f %format_file% -S %server% -U %username% -P %password% -k -F %first_row%

replacing those variables accordingly and cross your fingers.

SQL Server replies back without error but saying it imported zero rows. You spend a couple of days on this only to find out that there must be a blank line at the end of the data file, amongst other annoyances, for things to work and that even if the file looks “well formed”, BCP may not think so. You have even run this file through the GTFS FeedValidator which found problems with other files but not this one.

A guaranteed way to determine if your file is well formed is to open it in Notepad. If it looks well formed there, BCP will be able to read it. A file is well-formed in this context, if it is presented as specified in your format file. For example, if lines are supposed to be terminated with Windows EOL \r\n, you should not see this:

route_id,route_short_name,route_long_name,route_desc,route_type,route_url 1-184,1,,,3,2-184,2,,,3,4-184,4,,,3,
5-184,5,,,3,6-184,6,,,3,7-184,7,,,3,8-184,8,,,3,

Instead you should see the format presented earlier in this article.

Another way is to open your file in Notepad++ and using the Regular Expression search mode to look for \n, which is the UNIX end of line character set.  Assuming you are doing all this work on Windows you should do a search \n and replace with \r\n.  This should fix your problem.

Happy importing.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s