> I am not using load data, I am using the table import wizard.
Yes. The WB wizard is a black box. There are reports (eg
https://stackoverflow.com/questions/33296569/mysql-workbench-table-data-import-wizard-extremely-slow) that it's slow; that argues that it doesn't use Load Data.
> The input member file I sent only had 4 data rows + one header row. But the error above lists a row 5. What am I missing there?
Prob'ly a trailing EOL here.
Quote
I noticed on the Server Status window in workbench that it says the "general" log file is turned off. Only the error one and the one for slow queries is enabled. Would they be in the general one if I had it enabled?
No, errors will be written to the MySQL error log.
Quote
We get the files in as .txt then I import them in Excel in order to add the key column and the three or four columns to the far right.
The references are in other spreadsheet cells/pages?
Quote
Then, I save as .csv from Excel. My assumption was that types would be "neutral" once saved as .csv and that MySQL would bring them in as the type of the column. I guess not.
Spreadsheet rules <> RDBMS rules!
Quote
Should I be saving them as text instead? I have to be able to use excel to add the columns. One of the columns for the member file includes using xlookup to assign practice.
Again, the claims table DDL is outlandishly wrong for an RDBMS. If there are going to be queries on such a table, its major enumerating columns need to be broken out to child tables, more like this...
CREATE TABLE `united_claims` (
`United_Clm_Key` bigint unsigned PRIMARY KEY AUTO_INCREMENT,
`COMPANY_CODE` varchar(5) DEFAULT NULL,
`LOB` varchar(30) DEFAULT NULL,
`Svc_Type` varchar(20) DEFAULT NULL,
`SEQ_CLAIM_ID` varchar(50) DEFAULT NULL,
`Claim_Number` varchar(36) DEFAULT NULL,
`Claimline_Number` smallint DEFAULT NULL,
`Claimline_AdjFlag` char(1) DEFAULT NULL,
`Claim_SvcBeginDate` date DEFAULT NULL,
`Claim_SvcEndDate` date DEFAULT NULL,
`Claim_PaidDate` date DEFAULT NULL,
`Claim_PrintDate` date DEFAULT NULL,
`CHECK_NUMBER` varchar(16) DEFAULT NULL,
`Claimline_ProcCode` varchar(8) DEFAULT NULL,
`Claimline_Mod1` varchar(3) DEFAULT NULL,
`Claimline_Mod2` varchar(3) DEFAULT NULL,
`Claimline_Mod3` varchar(3) DEFAULT NULL,
`Claimline_Mod4` varchar(3) DEFAULT NULL,
`Claimline_RevCode` varchar(8) DEFAULT NULL,
`Claimline_RecDate` date DEFAULT NULL,
`Claimline_AdjudDate` date DEFAULT NULL,
`Claimline_SvcBeginDate` date DEFAULT NULL,
`Claimline_SvcEndDate` date DEFAULT NULL,
`SvcProvID` varchar(17) DEFAULT NULL,
`SvcProvFullName` varchar(75) DEFAULT NULL,
`SvcProvNPI` varchar(10) DEFAULT NULL,
`SvcProvSpecCode` varchar(15) DEFAULT NULL,
`SvcProvType` varchar(15) DEFAULT NULL,
`SvcProvParStat` char(1) DEFAULT NULL,
`AttProvID` varchar(17) DEFAULT NULL,
`AttProvFullName` varchar(75) DEFAULT NULL,
`AttProvNPI` varchar(10) DEFAULT NULL,
`RefProvID` varchar(17) DEFAULT NULL,
`RefProvFullName` varchar(75) DEFAULT NULL,
`VendorIDInternal` varchar(20) DEFAULT NULL,
`VendorFullName` varchar(55) DEFAULT NULL,
`VendorTIN` varchar(9) DEFAULT NULL,
`MemberSubscriberID` varchar(14) DEFAULT NULL,
`PAT_CONTROL_NO` varchar(38) DEFAULT NULL,
`MemberDOB` date DEFAULT NULL,
`MemberLName` varchar(35) DEFAULT NULL,
`MemberMI` char(1) DEFAULT NULL,
`MemberFName` varchar(30) DEFAULT NULL,
`MemberCIN` varchar(20) DEFAULT NULL,
`MemberMedicareID` varchar(12) DEFAULT NULL,
`MemberGender` char(1) DEFAULT NULL,
`MemberZip` varchar(15) DEFAULT NULL,
`POS1` varchar(6) DEFAULT NULL,
`POS2` varchar(6) DEFAULT NULL,
`POS3` varchar(6) DEFAULT NULL,
`Prim_Diag` varchar(4) DEFAULT NULL,
`ICD_FLAG` varchar(5) DEFAULT NULL,
`APR_DRG_Code` varchar(6) DEFAULT NULL,
`Inst_Bill_Type` varchar(3) DEFAULT NULL,
`Admit_Date` date DEFAULT NULL,
`Auth_Number` varchar(16) DEFAULT NULL,
`Admit_Code` varchar(6) DEFAULT NULL,
`Admit_Hr` varchar(50) DEFAULT NULL,
`Discharge_Hr` varchar(50) DEFAULT NULL,
`Claim_PtStat` char(2) DEFAULT NULL,
`Claimline_ResStat` char(1) DEFAULT NULL,
`Claimline_ProcessStat` char(1) DEFAULT NULL,
`Claim_Kind` varchar(8) DEFAULT NULL,
`Quantity` varchar(20) DEFAULT NULL,
`Total_Billed` varchar(20) DEFAULT NULL,
`Billed_Amt` varchar(20) DEFAULT NULL,
`RX_NDC` varchar(20) DEFAULT NULL,
`RX_GenericFlag` varchar(20) DEFAULT NULL,
`RX_DaySupply` varchar(20) DEFAULT NULL,
`RX_DispFee` varchar(20) DEFAULT NULL,
`RX_IngredCost` varchar(20) DEFAULT NULL,
`RX_FormFlag` varchar(20) DEFAULT NULL,
`RX_DateWrit` varchar(20) DEFAULT NULL,
`RX_BrandName` varchar(20) DEFAULT NULL,
`RX_DrugStrength` varchar(20) DEFAULT NULL,
`RX_GPI` varchar(20) DEFAULT NULL,
`RX_GPIDesc` varchar(20) DEFAULT NULL,
`RX_ControlledFlag` varchar(20) DEFAULT NULL,
`RX_CompoundCode` varchar(20) DEFAULT NULL,
`Claimline_NetPaid` varchar(20) DEFAULT NULL,
`Claim_OrigNum` varchar(36) DEFAULT NULL,
`Claim_Status` char(1) DEFAULT NULL,
`Plan_Desc` varchar(240) DEFAULT NULL,
`Payer` varchar(8) DEFAULT NULL,
`YearMonth` varchar(8) DEFAULT NULL,
`Orig_Filename` varchar(50) DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
CREATE TABLE member_claim_diags (
united_clm_key bigint unsigned,
idx tinyint unsigned not null
foreign key(united_clm_key) references united_members(united_claim_key),
diag varchar(30),diag_poa char(1) default null,
PRIMARY KEY(united_clm_key,idx)
);
CREATE TABLE member_claim_ICDs (
united_clm_key bigint unsigned,
idx tinyint unsigned not null
foreign key(united_clm_key) references united_members(united_claim_key),
icd_proc varchar(30) default null,
icd_proc_date date default null,
PRIMARY KEY(united_clm_key,idx)
);
CREATE TABLE member_claim_ICDs (
united_clm_key bigint unsigned,
idx tinyint unsigned not null
foreign key(united_clm_key) references united_members(united_claim_key),
cond_code varchar(90) defauilt null
PRIMARY KEY(united_clm_key,idx)
);
... and I've not even done full 3rd Normal Form, what else needs breaking out depends on how this table's data and its child data are to be queried.
Normally, this is done at input time, eg with a Trigger. With thousands of inout rows, probably the best solution for you would to load the Excel data into a
MyISAM table; that'll solve the input speed problem, it'll be fast with Load Data Infile. Then write a routine to populate the InnODB claims table and its InnoDB child tables from the input table. Then you have queryable data. With the table as it is, queries would be extremely difficult to write, debug and maintain (ie the rules of normalisation aren't cosmetic, they're intrinsic to RDBMS logic) and would perform like comatose pigs.
Quote
I looked at the columns shown in the error messages:
COMPANY_CODE VARCHAR(5) - always equal to UHGNY
MemberSubscriberID VARCHAR(14) - largest is 121602544
MemberMI CHAR(1) - all blank or one character
MemberGender CHAR(1) - all M or F, no blanks
MemberDOB DATE - all dates formatted yyyy-mm-dd, no single quotes or quotes in input file
MemberState VARCHAR(6) - all values two characters
ProviderNPI VARCHAR(10) - largest is 1992972269
ProviderTIN VARCHAR(9) - largest is 510567466
EligStart DATE - all dates formatted yyyy-mm-dd, no single quotes or quotes in input file
EligEnd DATE - all dates formatted yyyy-mm-dd, no single quotes or quotes in input file
Many, perhaps all such errors can got round by surrounding output values with quotes.