dbr.import issue

(5 posts) (2 voices)

Tags:

  1. ajdjackson, Member

    Hi

    Hope you are all keeping well and stay that the way.

    I have an issue with importing xlsx file.

    The calling report is:

    CREATE PROCEDURE `sp_DBR_Trans_Raw_Import`() BEGIN

    select 'dbr.import.options', 'format', 'xlsx';

    select 'dbr.import', 'sp_DBR_trans_raw_importer_do';

    END

    The importing procedure is:

    CREATE PROCEDURE `sp_DBR_trans_raw_importer_do`( `inpharm_id` varchar(30), `ingender` varchar(1), `inage` int(11), `inrx_origin` varchar(45), `indrug_name` varchar(100), `inndc` varchar(20), `instatus` varchar(45), `insub_status` varchar(45), `indate_received` date, `indate_processed` date, `indate_triaged` date, `inshipment_carrier` varchar(45), `infill_num` int(11), `inpat_insur_plan` varchar(45), `ingroup_num` varchar(45), `inbin` varchar(10), `inpcn` varchar(20), `inprimary_payer` varchar(45), `insecondary_payer` varchar(45), `inprimary_copay` float, `insecondary_copay` float, `inprescriber_spec` varchar(45), `innpi` int, `inprescriber_name` varchar(50), `instreet` varchar(45), `incity` varchar(45), `instate` varchar(2), `inzip` varchar(10), `inqty_written` int, `inqty_dispensed` int , `inday_supply` int(11)) BEGIN

    insert into tbltrans_raw values ( `pharm_id`, `gender`, `age`, `rx_origin`, `drug_name`, `ndc`, `status`, `sub_status`, `date_received`, `date_processed`, `date_triaged`, `shipment_carrier`, `fill_num`, `pat_insur_plan`, `group_num`, `bin`, `pcn`, `primary_payer`, `secondary_payer`, `primary_copay`, `secondary_copay`, `prescriber_spec`, `npi`, `prescriber_name`, `street`, `city`, `state`, `zip`, `qty_written`, `qty_dispensed`, `day_supply`); END

    the table structure is:

    CREATE TABLE `tbltrans_raw` ( `pharm_id` varchar(30) NOT NULL, `gender` varchar(1) DEFAULT NULL, `age` int(11) DEFAULT NULL, `rx_origin` varchar(45) DEFAULT NULL, `drug_name` varchar(100) DEFAULT NULL, `ndc` varchar(20) DEFAULT NULL, `status` varchar(45) NOT NULL, `sub_status` varchar(45) NOT NULL, `date_received` date NOT NULL, `date_processed` date DEFAULT NULL, `date_triaged` date DEFAULT NULL, `shipment_carrier` varchar(45) DEFAULT NULL, `fill_num` int(11) DEFAULT NULL, `pat_insur_plan` varchar(45) DEFAULT NULL, `group_num` varchar(45) DEFAULT NULL, `bin` varchar(10) DEFAULT NULL, `pcn` varchar(20) DEFAULT NULL, `primary_payer` varchar(45) DEFAULT NULL, `secondary_payer` varchar(45) DEFAULT NULL, `primary_copay` float DEFAULT NULL, `secondary_copay` float DEFAULT NULL, `prescriber_spec` varchar(45) DEFAULT NULL, `npi` int(11) DEFAULT NULL, `prescriber_name` varchar(50) DEFAULT NULL, `street` varchar(45) DEFAULT NULL, `city` varchar(45) DEFAULT NULL, `state` varchar(2) DEFAULT NULL, `zip` varchar(10) DEFAULT NULL, `qty_written` int(11) DEFAULT NULL, `qty_dispensed` int(11) DEFAULT NULL, `day_supply` int(11) DEFAULT NULL, PRIMARY KEY (`pharm_id`,`status`,`sub_status`,`date_received`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Running the importer I get the following errors:

    and the table looks like this:

    I've no idea where the duplicate error '0000-00-00' is coming from.

    Any thoughts?

    Thanks

    Jake

  2. myDBR Team, Key Master

    We would need the data to see where the problem comes from.. Do you have any triggers in the tbltrans_raw-table?

    If you cannot pinpoint the problem, send a sample XLSX-file and we'll take a look.
    --
    myDBR Team

  3. ajdjackson, Member

    Hi

    I’ve just sent some sample data to support@mydbr.com

    Thanks

    Jake

  4. myDBR Team, Key Master

    Jake,
    looking a bit closer, the syntax of your procedure does not look be correct:

    The code:

    values (
    `pharm_id`,
    `gender`,
    `age`,
    ...

    should be like

    values (
    inpharm_id,
    ingender,
    inage,
    ...

    i.e. use variables instead of column names.

    MySQL should flag this code to be incorrect to begin with, but for some peculiar reason it does not.

    --
    myDBR Team

  5. ajdjackson, Member

    Doh!

    I’ve written a few of these before but didn’t twig on this time.

    Many thanks

    Jake


Reply

You must log in to post.