sql-serverbcp

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

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

can someone take a look and let me know what am I missing?

Solution

  • The problem is in your datetime fields, the last 2 on the csv file.
    You should remove the dash between date and time, and insert a space in its place.

    For example in you first row change from this

    2021-06-25-18:05:01|2021-06-25-18:05:01

    to this

    2021-06-25 18:05:01|2021-06-25 18:05:01