Description:
Importing CSV file shows in the log window with immediate and persistent errors. I right-clicked on the database created for the import to start the data transfer.
Error example:
-Row import failed with error: ("Incorrect integer value: " for column 'total_cases' at row 1", 1366)
-Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265)
-Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265)
Originally brought CSV file into NotePad++ to get data types for 26 columns for CREATE TABLE statement. Confirmed no validation errors in N++. There are 85,171 data lines with one line for column name. There might be something going on with the entry_date column. N++ is reading it as Datetime even though the CSV shows yyyy-mm-dd. However, the errors seem to point to data truncation, and incorrect values -- on line 1.
I create the table with the entry_date column set to Datetime. I right click on the table, choose import CSV, accept the defaults, open the log window and start the run. The errors are immediate and persistent. Further, when I cancel the job, I get two dialog boxes to confirm cancel. Then a MySQL splash screen appears (Cannot access a disposed object. Object name: '_Progress Bar'.) asking to log a bug report. After either closing or clicking on Report Bug, the workbench closes after a few seconds.
From the error messages referencing row 1, does this mean the column names or the first data line?
How to repeat:
This is the first 10 rows of the CSV from N++:
iso_code,continent,location,entry_date,population,total_cases,new_cases,new_cases_smoothed,total_deaths,new_deaths,new_deaths_smoothed,total_cases_per_million,new_cases_per_million,new_cases_smoothed_per_million,total_deaths_per_million,new_deaths_per_million,new_deaths_smoothed_per_million,reproduction_rate,icu_patients,icu_patients_per_million,hosp_patients,hosp_patients_per_million,weekly_icu_admissions,weekly_icu_admissions_per_million,weekly_hosp_admissions,weekly_hosp_admissions_per_million
AFG,Asia,Afghanistan,2020-02-24,38928341,1,1,,,,,0.026,0.026,,,,,,,,,,,,,
AFG,Asia,Afghanistan,2020-02-25,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,,
AFG,Asia,Afghanistan,2020-02-26,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,,
AFG,Asia,Afghanistan,2020-02-27,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,,
AFG,Asia,Afghanistan,2020-02-28,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,,
AFG,Asia,Afghanistan,2020-02-29,38928341,1,0,0.143,,,0,0.026,0,0.004,,,0,,,,,,,,,
AFG,Asia,Afghanistan,2020-03-01,38928341,1,0,0.143,,,0,0.026,0,0.004,,,0,,,,,,,,,
AFG,Asia,Afghanistan,2020-03-02,38928341,1,0,0,,,0,0.026,0,0,,,0,,,,,,,,,
AFG,Asia,Afghanistan,2020-03-03,38928341,2,1,0.143,,,0,0.051,0.026,0.004,,,0,,,,,,,,,
N++ column detection window:
Format=CSVDelimited
ColNameHeader=True
DateTimeFormat=yyyy-mm-dd
DecimalSymbol=.
NumberDigits=3
Col1="iso_code" Text Width 8
Col2="continent" Text Width 13
;Col2="continent" Enumeration Africa|Asia|Europe|North America|Oceania|South America
Col3="location" Text Width 32
Col4="entry_date" Datetime Width 10
Col5="population" Integer Width 10
Col6="total_cases" Integer Width 9
Col7="new_cases" Integer Width 6
Col8="new_cases_smoothed" Float Width 10
Col9="total_deaths" Integer Width 7
Col10="new_deaths" Integer Width 5
Col11="new_deaths_smoothed" Float Width 9
Col12="total_cases_per_million" Float Width 10
Col13="new_cases_per_million" Float Width 9
Col14="new_cases_smoothed_per_million" Float Width 8
Col15="total_deaths_per_million" Float Width 8
Col16="new_deaths_per_million" Float Width 7
Col17="new_deaths_smoothed_per_million" Float Width 7
Col18="reproduction_rate" Float Width 5
;Col18="reproduction_rate" Float 99.99
Col19="icu_patients" Integer Width 5
Col20="icu_patients_per_million" Float Width 7
Col21="hosp_patients" Integer Width 6
Col22="hosp_patients_per_million" Float Width 8
Col23="weekly_icu_admissions" Float Width 8
Col24="weekly_icu_admissions_per_million" Float Width 7
Col25="weekly_hosp_admissions" Float Width 10
Col26="weekly_hosp_admissions_per_million" Float Width 8
N++ Validation:
Inspected 85172 lines, no data errors found, time elapsed 00:00:00.437
Table setup statement:
CREATE TABLE ppcovid19.coviddeath (
iso_code Text,
continent Text,
location Text,
entry_date Datetime,
population Integer,
total_cases Integer,
new_cases Integer,
new_cases_smoothed Float,
total_deaths Integer,
new_deaths Integer,
new_deaths_smoothed Float,
total_cases_per_million Float,
new_cases_per_million Float,
new_cases_smoothed_per_million Float,
total_deaths_per_million Float,
new_deaths_per_million Float,
new_deaths_smoothed_per_million Float,
reproduction_rate Float,
icu_patients Integer,
icu_patients_per_million Float,
hosp_patients Integer,
hosp_patients_per_million Float,
weekly_icu_admissions Float,
weekly_icu_admissions_per_million Float,
weekly_hosp_admissions Float,
weekly_hosp_admissions_per_million Float
);
Suggested fix:
I exported the same dataset earlier in the week and it was successful. I redid it to avoid sorting dates as text on the workbench.
Description: Importing CSV file shows in the log window with immediate and persistent errors. I right-clicked on the database created for the import to start the data transfer. Error example: -Row import failed with error: ("Incorrect integer value: " for column 'total_cases' at row 1", 1366) -Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'new_cases_smoothed' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265) -Row import failed with error: ("Data truncated for column 'reproduction_rate' at row 1", 1265) Originally brought CSV file into NotePad++ to get data types for 26 columns for CREATE TABLE statement. Confirmed no validation errors in N++. There are 85,171 data lines with one line for column name. There might be something going on with the entry_date column. N++ is reading it as Datetime even though the CSV shows yyyy-mm-dd. However, the errors seem to point to data truncation, and incorrect values -- on line 1. I create the table with the entry_date column set to Datetime. I right click on the table, choose import CSV, accept the defaults, open the log window and start the run. The errors are immediate and persistent. Further, when I cancel the job, I get two dialog boxes to confirm cancel. Then a MySQL splash screen appears (Cannot access a disposed object. Object name: '_Progress Bar'.) asking to log a bug report. After either closing or clicking on Report Bug, the workbench closes after a few seconds. From the error messages referencing row 1, does this mean the column names or the first data line? How to repeat: This is the first 10 rows of the CSV from N++: iso_code,continent,location,entry_date,population,total_cases,new_cases,new_cases_smoothed,total_deaths,new_deaths,new_deaths_smoothed,total_cases_per_million,new_cases_per_million,new_cases_smoothed_per_million,total_deaths_per_million,new_deaths_per_million,new_deaths_smoothed_per_million,reproduction_rate,icu_patients,icu_patients_per_million,hosp_patients,hosp_patients_per_million,weekly_icu_admissions,weekly_icu_admissions_per_million,weekly_hosp_admissions,weekly_hosp_admissions_per_million AFG,Asia,Afghanistan,2020-02-24,38928341,1,1,,,,,0.026,0.026,,,,,,,,,,,,, AFG,Asia,Afghanistan,2020-02-25,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,, AFG,Asia,Afghanistan,2020-02-26,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,, AFG,Asia,Afghanistan,2020-02-27,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,, AFG,Asia,Afghanistan,2020-02-28,38928341,1,0,,,,,0.026,0,,,,,,,,,,,,, AFG,Asia,Afghanistan,2020-02-29,38928341,1,0,0.143,,,0,0.026,0,0.004,,,0,,,,,,,,, AFG,Asia,Afghanistan,2020-03-01,38928341,1,0,0.143,,,0,0.026,0,0.004,,,0,,,,,,,,, AFG,Asia,Afghanistan,2020-03-02,38928341,1,0,0,,,0,0.026,0,0,,,0,,,,,,,,, AFG,Asia,Afghanistan,2020-03-03,38928341,2,1,0.143,,,0,0.051,0.026,0.004,,,0,,,,,,,,, N++ column detection window: Format=CSVDelimited ColNameHeader=True DateTimeFormat=yyyy-mm-dd DecimalSymbol=. NumberDigits=3 Col1="iso_code" Text Width 8 Col2="continent" Text Width 13 ;Col2="continent" Enumeration Africa|Asia|Europe|North America|Oceania|South America Col3="location" Text Width 32 Col4="entry_date" Datetime Width 10 Col5="population" Integer Width 10 Col6="total_cases" Integer Width 9 Col7="new_cases" Integer Width 6 Col8="new_cases_smoothed" Float Width 10 Col9="total_deaths" Integer Width 7 Col10="new_deaths" Integer Width 5 Col11="new_deaths_smoothed" Float Width 9 Col12="total_cases_per_million" Float Width 10 Col13="new_cases_per_million" Float Width 9 Col14="new_cases_smoothed_per_million" Float Width 8 Col15="total_deaths_per_million" Float Width 8 Col16="new_deaths_per_million" Float Width 7 Col17="new_deaths_smoothed_per_million" Float Width 7 Col18="reproduction_rate" Float Width 5 ;Col18="reproduction_rate" Float 99.99 Col19="icu_patients" Integer Width 5 Col20="icu_patients_per_million" Float Width 7 Col21="hosp_patients" Integer Width 6 Col22="hosp_patients_per_million" Float Width 8 Col23="weekly_icu_admissions" Float Width 8 Col24="weekly_icu_admissions_per_million" Float Width 7 Col25="weekly_hosp_admissions" Float Width 10 Col26="weekly_hosp_admissions_per_million" Float Width 8 N++ Validation: Inspected 85172 lines, no data errors found, time elapsed 00:00:00.437 Table setup statement: CREATE TABLE ppcovid19.coviddeath ( iso_code Text, continent Text, location Text, entry_date Datetime, population Integer, total_cases Integer, new_cases Integer, new_cases_smoothed Float, total_deaths Integer, new_deaths Integer, new_deaths_smoothed Float, total_cases_per_million Float, new_cases_per_million Float, new_cases_smoothed_per_million Float, total_deaths_per_million Float, new_deaths_per_million Float, new_deaths_smoothed_per_million Float, reproduction_rate Float, icu_patients Integer, icu_patients_per_million Float, hosp_patients Integer, hosp_patients_per_million Float, weekly_icu_admissions Float, weekly_icu_admissions_per_million Float, weekly_hosp_admissions Float, weekly_hosp_admissions_per_million Float ); Suggested fix: I exported the same dataset earlier in the week and it was successful. I redid it to avoid sorting dates as text on the workbench.