Bulk Insert Error - Unexpected end of file was encountered in the data file

[BxWill]

Receive the following error upon attempting to bulk insert a large CSV file (220 megabytes; 1,300,000 records) into a Sql Server 2012 database.

Msg 4832, Level 16, State 1, Line 1
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

[Bold]
Upon reviewing the CSV file, there is a comma within one of the fields - the field "City" contains data such as "New York City, New York."[/Bold]

Note, I was only able to view the entire contents of the CSV file via MS Access. I was not able to open with Delimit or Notepad++.


Also, the field "Assign Date" is displayed as "2013-01-12 12:00:00 AM" within MS Access.


Any insight as to if there is a method utilizing TSQL that will offer a resolution to this error.



Another comtemplated solution include the modificaiton of the CSV file - changing the field delimiter from a comma (,) to a pipe (|) using Awk or VBS Script.

However, I want to explore methods within TSQL initially and then consider Awk or VBS Script as a second option. It very well could be that Awk or VBS Script is maybe the preferred method to resolve "unclean CSV files" and to change the delimeter on CSV files without opening the file.


Complete code is below.

Use Inventory

IF OBJECT_ID('tempdb..#EquipmentData1') IS NOT NULL
BEGIN
	DROP TABLE #EquipmentData1
END
GO

CREATE TABLE 	[#EquipmentData1](		
		[AssignDate]			[datetime] NOT NULL,
		[ZipCode]			[varchar] (5)  NOT NULL,
		[City]				[varchar] (40)  NULL,
		[Comment]			[varchar] (150)  NULL,
		[AdjustmentFactor]		[varchar] (40)  NULL,
		
)	
GO


IF OBJECT_ID('Inventory..EquipmentData_2013') IS NOT NULL
BEGIN
	DROP TABLE EquipmentData_2013
END
GO

CREATE TABLE [EquipmentData_2013](		
		[AssignDate]			[datetime] NOT NULL,
		[ZipCode]			[varchar] (5)  NOT NULL,
		[City]				[varchar] (40)  NULL,
		[Comment]			[varchar] (150)  NULL,
		[AdjustmentFactor]		decimal(7,4) NULL,
		Constraint PK_EquipmentData_2013 Primary Key ([ZipCode])
)
GO

IF OBJECT_ID('Inventory..EquipmentData_2014') IS NOT NULL
BEGIN
	DROP TABLE EquipmentData_2014
END
GO

CREATE TABLE [EquipmentData_2014](		
		[AssignDate]			[datetime] NOT NULL,
		[ZipCode]			[varchar] (5)  NOT NULL,
		[City]				[varchar] (40)  NULL,
		[Comment]			[varchar] (150)  NULL,
		[AdjustmentFactor]		decimal(7,4) NULL,
		Constraint PK_EquipmentData_2014 Primary Key ([ZipCode])
)
GO


BULK INSERT #EquipmentData1
   	FROM 'C:\Data\CSVFilesToLoad\Test.CSV'
   WITH
      (
         	 FIELDTERMINATOR = ',',  
		 ROWTERMINATOR = '0x0a',    
		 FIRSTROW = 2
      );
GO