bcp error: Text column data incomplete and Invalid character value for cast specification

no_name_id 21 Reputation points
2021-06-25T21:24:05.69+00:00

I have some CSV files which I would like to load in a database table which is as follows

DB TABLE

CREATE TABLE [EVT_STREAM].EventON [PRIMARY]

CSV file content is as follows

TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 260, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 66, 'event_string': 'STERILE_ADAPTER_PARTIAL_BEGIN', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:01|2021-06-25-18:05:01
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 9042, 'event_string': 'SA_SW2_PRESENT', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:01|2021-06-25-18:05:01
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 260, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 66, 'event_string': 'STERILE_ADAPTER_PARTIAL_BEGIN', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:01|2021-06-25-18:05:01
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 9040, 'event_string': 'SA_SW1_PRESENT', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:02|2021-06-25-18:05:02
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 100, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 63, 'event_string': 'STERILE_ADAPTER_ON', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:03|2021-06-25-18:05:03
TEST_1|server|{'message_type': 0, 'rsdb': {'array_index': 0, 'data_id': 162, 'data_id_string': 'SUDB_PSC', 'key': 3067, 'key_index': 3, 'key_string': 'SUDB_KEY_PSC_USM_STERILE_ADAPTER_ON', 'new_value': 1, 'old_value': 0, 'rsdb_id': 268521760}}|2021-06-25-18:05:04|2021-06-25-18:05:04
TEST_1|server|{'message_type': 32, 'snapshot_kk': {'snapshot_data': [53563, 53563], 'snapshot_kk_entry': [{'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}, {'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}], 'snapshot_kk_hdr': {'gmt_timestamp': 1563473826, 'manip_bitfield': 543, 'mid': 0, 'psc_model_type': 0, 'snapshot_session': 9, 'ss_timestamp': 53563, 'ssc1_model_type': 1, 'ssc2_model_type': 0, 'table_idx': 0, 'trigger': 1, 'trigger_mid': 3, 'trigger_str': 'SNAPSHOT_TRIGGER_STERILE_ADAPTER_ON'}}}|2021-06-25-18:05:04|2021-06-25-18:05:04
TEST_1|server|{'message_type': 32, 'snapshot_kk': {'snapshot_data': [53563, 53563], 'snapshot_kk_entry': [{'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}, {'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}], 'snapshot_kk_hdr': {'gmt_timestamp': 1563473826, 'manip_bitfield': 543, 'mid': 0, 'psc_model_type': 0, 'snapshot_session': 9, 'ss_timestamp': 53563, 'ssc1_model_type': 1, 'ssc2_model_type': 0, 'table_idx': 1, 'trigger': 1, 'trigger_mid': 3, 'trigger_str': 'SNAPSHOT_TRIGGER_STERILE_ADAPTER_ON'}}}|2021-06-25-18:05:05|2021-06-25-18:05:05
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 916833, 'a1': 916839, 'a2': 808385, 'a3': 16, 'event': 9040, 'event_string': 'SA_SW1_PRESENT', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:06|2021-06-25-18:05:06
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 100, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 63, 'event_string': 'STERILE_ADAPTER_ON', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:06|2021-06-25-18:05:06

BCP command is as follows and errors

I have some CSV files which I would like to load in a database table which is as follows

DB TABLE 

CREATE TABLE [EVT_STREAM].[Event](
    [SystemName] [varchar](25) NOT NULL,
    [Router] [varchar](128) NULL,
  [Event] [nvarchar](max) NULL,
  [ReceivedAt] [datetime] NOT NULL,
  [InsertedAt] [datetime] NOT NULL
)ON [PRIMARY]

CSV file content is as follows 

TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 260, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 66, 'event_string': 'STERILE_ADAPTER_PARTIAL_BEGIN', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:01|2021-06-25-18:05:01
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 9042, 'event_string': 'SA_SW2_PRESENT', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:01|2021-06-25-18:05:01
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 260, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 66, 'event_string': 'STERILE_ADAPTER_PARTIAL_BEGIN', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:01|2021-06-25-18:05:01
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 0, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 9040, 'event_string': 'SA_SW1_PRESENT', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:02|2021-06-25-18:05:02
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 100, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 63, 'event_string': 'STERILE_ADAPTER_ON', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:03|2021-06-25-18:05:03
TEST_1|server|{'message_type': 0, 'rsdb': {'array_index': 0, 'data_id': 162, 'data_id_string': 'SUDB_PSC', 'key': 3067, 'key_index': 3, 'key_string': 'SUDB_KEY_PSC_USM_STERILE_ADAPTER_ON', 'new_value': 1, 'old_value': 0, 'rsdb_id': 268521760}}|2021-06-25-18:05:04|2021-06-25-18:05:04
TEST_1|server|{'message_type': 32, 'snapshot_kk': {'snapshot_data': [53563, 53563], 'snapshot_kk_entry': [{'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}, {'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}], 'snapshot_kk_hdr': {'gmt_timestamp': 1563473826, 'manip_bitfield': 543, 'mid': 0, 'psc_model_type': 0, 'snapshot_session': 9, 'ss_timestamp': 53563, 'ssc1_model_type': 1, 'ssc2_model_type': 0, 'table_idx': 0, 'trigger': 1, 'trigger_mid': 3, 'trigger_str': 'SNAPSHOT_TRIGGER_STERILE_ADAPTER_ON'}}}|2021-06-25-18:05:04|2021-06-25-18:05:04
TEST_1|server|{'message_type': 32, 'snapshot_kk': {'snapshot_data': [53563, 53563], 'snapshot_kk_entry': [{'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}, {'is_float': 0, 'kk_id': 12, 'kk_id_str': 'SYS_CYCLE_COUNT_GBL', 'num_dofs': 1}], 'snapshot_kk_hdr': {'gmt_timestamp': 1563473826, 'manip_bitfield': 543, 'mid': 0, 'psc_model_type': 0, 'snapshot_session': 9, 'ss_timestamp': 53563, 'ssc1_model_type': 1, 'ssc2_model_type': 0, 'table_idx': 1, 'trigger': 1, 'trigger_mid': 3, 'trigger_str': 'SNAPSHOT_TRIGGER_STERILE_ADAPTER_ON'}}}|2021-06-25-18:05:05|2021-06-25-18:05:05
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 916833, 'a1': 916839, 'a2': 808385, 'a3': 16, 'event': 9040, 'event_string': 'SA_SW1_PRESENT', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:06|2021-06-25-18:05:06
TEST_1|server|{'message_type': 2, 'super_evt_non_error': {'a0': 100, 'a1': 0, 'a2': 0, 'a3': 0, 'event': 63, 'event_string': 'STERILE_ADAPTER_ON', 'history_buffer_id': 3, 'history_buffer_string': 'HISTORY_BUFFER_UI_EVT', 'mid': 3, 'mid_string': 'AIM4'}}|2021-06-25-18:05:06|2021-06-25-18:05:06

BCP command is as follows and errors 

bcp EVT_STREAM.Event in fcboqgiuny.csv -S sw2sql01.dv.local,1517 -U appdbowner -P appdbowner1 -t  "|"  -c

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Text column data incomplete
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
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

BCP copy in failed
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,642 questions
Accepted answer
  1. Erland Sommarskog 100.8K Reputation points MVP
    2021-06-25T21:40:43.177+00:00

    The dates have a funky format: 2021-06-25-18:05:06. The hyphen between the day and the hour is unexpected and causes things to south.

    I think the best bet is import into a staging table with string columns instead of datetime and then add a second step to interpret the dates.

    1 person found this answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Seeya Xi-MSFT 16,436 Reputation points
    2021-06-28T04:21:28.987+00:00

    Hi @no_name_id ,

    Time type data must be formatted with ToString ("yyyy-MM-dd HH:mm:ss.fff"), otherwise there may be errors in bcp import.

    To add: The structure of the table may also cause bcp errors.
    To quickly check out, whether the imported and exported two table structures are consistent, use the following command: 

    bcp {DBName.dbo.Table} format nul -n -f aa.txt -S %DBSERVER% -U"%DBUSERNAME%" -P"%DBPWD%"

    In the display result, you can see all the field information of the imported and exported files. The error can be judged by the inconsistency of the two file information.

    Best regards,
    Seeya

    If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. no_name_id 21 Reputation points
    2021-06-29T16:34:02.133+00:00

    yes having incorrect date format was the issue, many thanks.

    0 comments