Data import error of tls206_part02.csv
Posted: Mon Mar 29, 2021 3:44 pm
Dear all,
Environment:
PATSTAT Autumn 2020
MS SQL Server 2019 on Windows 10
I used the build-in scripts (createDatabaseSQLserver.sql and alltablescreate.sql) to create the database and tables. When I imported tls206_part02.csv, I encountered some problems. My command and error log are provided as follows. I will be very grateful for your advice or help.
Table: dbo.tls206_person
Data: tls206_part02.csv
BULK INSERT dbo.tls206_person FROM 'D:\PATSTAT_Autumn_2020\data\tls206_part02.csv' WITH (FORMAT = 'CSV', FIRSTROW = 2, TABLOCK, ERRORFILE = 'F:\Error\tls206_part02.csv');
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8092915, column 2 (person_name).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8179139, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8594950, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8684855, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 9337528, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10067197, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10452815, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10470125, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10837299, column 2 (person_name).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10862858, column 2 (person_name).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10862902, column 2 (person_name).
Msg 4865, Level 16, State 1, Line 85
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 85
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 85
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Many thanks,
Frank
Environment:
PATSTAT Autumn 2020
MS SQL Server 2019 on Windows 10
I used the build-in scripts (createDatabaseSQLserver.sql and alltablescreate.sql) to create the database and tables. When I imported tls206_part02.csv, I encountered some problems. My command and error log are provided as follows. I will be very grateful for your advice or help.
Table: dbo.tls206_person
Data: tls206_part02.csv
BULK INSERT dbo.tls206_person FROM 'D:\PATSTAT_Autumn_2020\data\tls206_part02.csv' WITH (FORMAT = 'CSV', FIRSTROW = 2, TABLOCK, ERRORFILE = 'F:\Error\tls206_part02.csv');
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8092915, column 2 (person_name).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8179139, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8594950, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 8684855, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 9337528, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10067197, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10452815, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10470125, column 3 (person_name_orig_lg).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10837299, column 2 (person_name).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10862858, column 2 (person_name).
Msg 4863, Level 16, State 1, Line 85
Bulk load data conversion error (truncation) for row 10862902, column 2 (person_name).
Msg 4865, Level 16, State 1, Line 85
Cannot bulk load because the maximum number of errors (10) was exceeded.
Msg 7399, Level 16, State 1, Line 85
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 85
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
Many thanks,
Frank