Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 - When reading table in SQL

Question

I an creating a table Dates in Spark SQL. I am able to read the data when I do a select * on this newly created table Dates in the notebook. However when I do a select * on this table in SQL server, it throws me the following error

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

I have read that this happens when a bulk insert is being done where the SQL server tries to insert the table header into the columns and the data type mismatch occurs. However, this is a newly created table. How do I resolve this issue? Please suggest

Answer 1

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

This error usually occurs when there is a type mismatch or invalid character in the data being inserted into the table.

1st Method: You can try using the configured date column to be "smalldatetime" instead of "datetime".

2nd Method: Create a format file with bcp (SQL Server)

Since you mentioned you are using the new SQL server table, use a format file based on the data-type option specified (-n, -c, -w, or -N) and the table or view delimiters.

When you bulk import into a SQL Server table or bulk export data from a table, you can use a format file as a flexible system for writing data files.

Syntax:

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

Reference: Use native format to import or export data (SQL Server)

Bulk load data using the COPY statement

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