I am trying to import the CSV with these contents into my SQL server database.

The data :

tranche_d_age
<18
18_24
25_34
35_44
45_60
>60

The format file :

14.0
1
1       SQLCHAR             0       0      "\n"   2     tranche_d_age_id                                      French_CI_AS

In the database, I created a table with the following specifications :

DROP TABLE IF EXISTS [dbo].[ref_tranches_age]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ref_tranches_age](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tranche_d_age_id] [varchar](max) NULL
)
GO

Here is the bcp command I issue:

bcp [xxx].dbo.tranches_age in 'ref_tranches_age.csv' -U 'xxx' -P 'xxx' -S 'xxx' -f bcp_ref_tranches_age.fmt

And the response:

Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Invalid character value for cast specification

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 22    

Seems to me that the response contains 7 error messages for 7 lines in the csv file. (I might be wrong)

I found these 2 threads saying I shouldn't have blanks in my source file & I shouldn't insert into the identity column. Which, unless I'm wrong, I am not doing.

0 Answers