Re: autoincrement id missing when i do load data local into file
Thanks for you response
This is the only table in which i came across this problem
Here is the show create table
CREATE TABLE `tbl_chart_preset_vitals_growth_percentiles` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`patient_type` enum('Infants','Adults') NOT NULL,
`gender_id` tinyint(3) unsigned NOT NULL,
`vital_types_id` tinyint(3) unsigned NOT NULL,
`age` float NOT NULL,
`p3` varchar(15) NOT NULL,
`p5` varchar(15) NOT NULL,
`p10` varchar(15) NOT NULL,
`p25` varchar(15) NOT NULL,
`p50` varchar(15) NOT NULL,
`p75` varchar(15) NOT NULL,
`p85` varchar(15) NOT NULL,
`p90` varchar(15) NOT NULL,
`p95` varchar(15) NOT NULL,
`p97` varchar(15) NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_gender_id` (`gender_id`),
KEY `fk_vital_types_id` (`vital_types_id`),
CONSTRAINT `tbl_chart_preset_vitals_growth_percentiles_ibfk_1` FOREIGN KEY (`gender_id`) REFERENCES `tbl_preset_gender` (`id`),
CONSTRAINT `tbl_chart_preset_vitals_growth_percentiles_ibfk_2` FOREIGN KEY (`vital_types_id`) REFERENCES `tbl_chart_preset_vital_types` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1788 DEFAULT CHARSET=latin1
Sample of the csv file...and no the csv does not contain the column for the autoincrement id.
Here is the upload command
load data local infile '/home/micro/bmiagerev.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P85,P90,P95,P97);
patient_type|vital_types_id|Sex|Agemos|P3|P5|P10|P25|P50|P75|P85|P90|P95|P97
Adults|1|1|24|14.52095|14.73732|15.09033|15.74164|16.57503|17.55719|18.16219|18.60948|19.33801|19.85986
Adults|1|1|24.5|14.50348|14.71929|15.07117|15.71963|16.54777|17.52129|18.11955|18.56111|19.27890|19.79194
Adults|1|1|25.5|14.46882|14.68361|15.03336|15.67634|16.49443|17.45135|18.03668|18.46730|19.16466|19.66102
Adults|1|1|26.5|14.43460|14.64843|14.99620|15.63403|16.44260|17.38384|17.95700|18.37736|19.05567|19.53658
Adults|1|1|27.5|14.40083|14.61379|14.95969|15.59268|16.39224|17.31871|17.88047|18.29125|18.95187|19.41849
Adults|1|1|28.5|14.36755|14.57969|14.92385|15.55226|16.34334|17.25593|17.80704|18.20892|18.85317|19.30665
Adults|1|1|29.5|14.33478|14.54615|14.88866|15.51275|16.29584|17.19546|17.73667|18.13031|18.75949|19.20097
Adults|1|1|30.5|14.30257|14.51319|14.85414|15.47414|16.24972|17.13726|17.66932|18.05538|18.67078|19.10132
Adults|1|1|31.5|14.27093|14.48084|14.82027|15.43639|16.20495|17.08130|17.60495|17.98408|18.58695|19.00761
Adults|1|1|32.5|14.23989|14.44909|14.78707|15.39951|16.16150|17.02753|17.54351|17.91635|18.50792|18.91973
Adults|1|1|33.5|14.20948|14.41798|14.75453|15.36345|16.11933|16.97592|17.48496|17.85215|18.43363|18.83758
Adults|1|1|34.5|14.17972|14.38750|14.72264|15.32822|16.07843|16.92645|17.42927|17.79143|18.36400|18.76106
Adults|1|1|35.5|14.15063|14.35767|14.69142|15.29379|16.03876|16.87907|17.37639|17.73414|18.29895|18.69006
Adults|1|1|36.5|14.12223|14.32851|14.66086|15.26016|16.00030|16.83376|17.32627|17.68022|18.23842|18.62449
Adults|1|1|37.5|14.09453|14.30002|14.63096|15.22731|15.96304|16.79048|17.27889|17.62963|18.18231|18.56425
I am not sure what you mean by corresponding results..i guess this is what you meant..sorry if i am wrong
load data local infile '/home/micro/bmiagerev.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P85,P90,P95,P97);
Query OK, 438 rows affected (0.16 sec)
Records: 438 Deleted: 0 Skipped: 0 Warnings: 0
load data local infile '/home/micro/lenageinf.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 74 rows affected, 1 warning (0.05 sec)
Records: 74 Deleted: 0 Skipped: 0 Warnings: 0
load data local infile '/home/micro/wtage.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 436 rows affected, 1 warning (0.08 sec)
Records: 436 Deleted: 0 Skipped: 0 Warnings: 0
load data local infile '/home/micro/wtageinf.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 76 rows affected, 1 warning (0.01 sec)
Records: 76 Deleted: 0 Skipped: 0 Warnings: 0
load data local infile '/home/micro/statage.csv' into table tbl_chart_preset_vitals_growth_percentiles fields terminated by '|' ignore 1 lines (patient_type,vital_types_id,gender_id,Age,P3,P5,P10,P25,P50,P75,P90,P95,P97);
Query OK, 436 rows affected, 1 warning (0.07 sec)
Records: 436 Deleted: 0 Skipped: 0 Warnings: 0
Please ignore the warnings in 4 of the load data commands..i am not uploading a the data for the column p85
There is another thing i noticed.... the total count of the records are
select count(*) from tbl_chart_preset_vitals_growth_percentiles;
+----------+
| count(*) |
+----------+
| 1460 |
+----------+
however the autoincrement id in show create table is way beyond the count(*).
In order to correct this problem i take a single csv dump after i load each file and then upload it back as a single file into the table.
Hope this info helps you find the mistake i am making or the bug in the software.