The exercise is simple.
You have a text file with the following content:
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:
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.