Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (Year)

Try using a format file since your data file only has 4 columns. Otherwise, try OPENROWSET or use a staging table.

myTestFormatFiles.Fmt may look like:

9.0
4
1       SQLINT        0       3       ","      1     StudentNo      ""
2       SQLCHAR       0       100     ","      2     FirstName      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       100     ","      3     LastName       SQL_Latin1_General_CP1_CI_AS
4       SQLINT        0       4       "\r\n"   4     Year           "

_{(source: microsoft.com)}

This tutorial on skipping a column with BULK INSERT may also help.

Your statement then would look like:

USE xta9354
GO
BULK INSERT xta9354.dbo.Students
    FROM 'd:\userdata\xta9_Students.txt' 
    WITH (FORMATFILE = 'C:\myTestFormatFiles.Fmt')

In my case, I was dealing with a file that was generated by hadoop on a linux box. When I tried to import to sql I had this issue. The fix wound up being to use the hex value for 'line feed' 0x0a. It also worked for bulk insert

bulk insert table from 'file' 
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '0x0a')

We use the bulk insert as well. The file we upload is sent from an external party. After a while of troubleshooting, I realized that their file had columns with commas in it. Just another thing to look for...