Cannot insert Integer data into MySQL, it inserts as a 0

907 views php
5

Overview:

I have a module on my site that allows the user to import .txt files (with data separated by semicolons and escaped by newlines) which then I grab and do a LOAD DATA LOCAL INFILE with to insert on the database. The script works, but not as intended. Possible points of interest:

  • Text file is encoded as UTF-16LE
  • Database/table default charset is utf8, InnoDB engine.

The issue:

The script only inserts the correct data when all columns on the database are set to VARCHAR. If I try to change the ones that are meant to contain numbers to INT, the script inserts a 0 (zero) where the data should be.

Debug attempts:

Have tried inserting random hardcoded data on the script and it does work. This led me to believe the issue laid on file encoding.

I've tried manipulating the text file's encoding but that doesn't seem to help though.

Have also tried converting the file to .csv, .xlsx (using a different script with PhpSpreadsheet) but it yields the same results.

Code:

PHP:

$servername = "localhost";
$username = "user";
$password = "pass";
$dbname = "databse";
$file = str_replace("\\", "\\\\", $_FILES["polizas"]["tmp_name"]);
if (is_uploaded_file($_FILES["polizas"]["tmp_name"])) {
    try {
        $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE => true));
        $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $status = $pdo->exec(
            "LOAD DATA LOCAL INFILE '" . $file . "'
                IGNORE
                INTO TABLE `polizas`
                FIELDS TERMINATED BY ';'
                LINES TERMINATED BY '\n'
                IGNORE 1 LINES
                (`seccion`, `propuesta`, `poliza`, `endoso`, `tipo_endoso`, `desc_tipo_endoso`, `pol_que_renueva`, `iva_cond`, `tipo_doc`, `nro_doc`, `apellido`, `domicilio`, `cod_postal`, `telefono`, `localidad`, `provincia`, `fecha_emision`, `fecha_inicio`, `fecha_vcto`, `canal_pago`, `desc_canal_pago`, `origen`, `productor`, `organizador`, `moneda`, `item`, `marca`, `modelo`, `cero_Km`, `motor`, `chasis`, `patente`, `tipo_carroceria`, `desc_tipo_carroceria`, `cobertura`, `desc_cobertura`, `cod_acc1`, `desc_acc1`, `valor_acc1`, `cod_acc2`, `desc_acc2`, `valor_acc2`, `cod_acc3`, `desc_acc3`, `valor_acc3`, `cod_acc4`, `desc_acc4`, `valor_acc4`, `suma_aseg`, `prima`, `recarg`, `der_emision`, `sellos`, `imp_tasas`, `otros`, `bonif`, `iva`, `adminis_financ`, `premio`, `cant_cuotas`, `ajuste`, `tipo_vehiculo`, `desc_tipo_vehiculo`, `codigo_uso`, `descripcion_uso`, `ano_fab`, `nro_prestamo`, `vcto_cuota1`, `vcto_prestamo`, `tipo_prestamo`, `tipo_operacion`, `seccion_pol_paquete`, `poliza_pol_paquete`)"
        );
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}

Text file example:

seccion;propuesta;poliza;endoso;tipo endoso;desc tipo endoso;pol que renueva;iva;tipo doc;nro doc;apellido;domicilio;cod postal;telefono;localidad;provincia;fecha emision;fecha inicio;fecha vcto;canal pago;desc canal pago;origen;productor;organizador;moneda;item;marca;modelo;cero Km;motor;chasis;patente;tipo carroceria;desc tipo carroceria;cobertura;desc cobertura;cod acc1;desc acc1;valor acc1;cod acc2;desc acc2;valor acc2;cod acc3;desc acc3;valor acc3;cod acc4;desc acc4;valor acc4;suma aseg;prima;recarg;der emision;sellos;imp tasas;otrod;bonif;iva;adminis financ;premio;cant cuotas;ajuste;tipo vehiculo;desc tipo vehiculo;codigo uso;descripcion uso;año fab;nro prestamo;vcto cuota1;vcto prestamo;tipo prestamo;tipo operacion;seccion pol.paquete; poliza pol.paquete; 
4;0;894606;707641;65;Anul a Prorrata;0000000;IN;DNI;25035153;BLANCO ALEJANDRO DANIEL;B JARDIN MZA F CASA 9;05570;;SAN MARTIN;13;01/11/2018;27/09/2018;03/11/2018;08;Cobranza Normal;DC;0052000001;00520;0;;;;;;;;;;;;;;;;;;;;;;;;-0,00;-1082,25;-0,00;-0,00;-0,00;-25,40;-17,32;-0,00;-242,42;-72,12;-1439,51;1;;0;NO DEFINIDO;0;NO DEFINIDO;0000;;;;;2;;;
4;2874759;945239;3;29;Refacturacion;0000000;CF;DNI;35210070;GENEM KEVIN DANILO;UNION 00;05596;263154279978;SANTA ROSA;13;01/11/2018;01/11/2018;01/12/2018;04;SO-Sobre;WEB;0012900077;00129;0;1;12340;CHEVROLET CORSA 1.4 4 P GL    L/09 (CLASSIC);N;T85032983;8AGSB19Y0AR169362;IRW910;1;SEDAN;C2;R.C. C/LTE. PERD.TOTAL X ACC. Y TOT.Y PARC X INC Y/O ROBO O HURTO;0;;0,00;0;;0,00;0;;0,00;0;;0,00;134000,00;524,11;0,00;0,00;8,33;12,22;0,00;0,00;116,65;31,36;692,67;1;20,00;1;AUTOMOVIL;101;AUTOS Y JEEPS NACIONALES O ASIMILAD;2010;;;;;1;;;
4;0;1001592;0;1;Poliza Nueva;0000000;CF;DNI;23649998;SILVA FELIX ALBERTO;CALLE LOS CHARABONES SN MONTEC;05570;263154320848;GENERAL SAN MAR;13;01/11/2018;01/11/2018;01/12/2018;08;Cobranza Normal;DC;0012900094;00129;0;1;17148;FIAT UNO S   1.4 3 

answered question

int(11) is useless, its just the same to int

Didn't know about that, thank you for pointing it out!

1 Answer

1

when you omit ENCLOSED BY '"' then all fields are enclosed and treated as strings.

posted this

Have an answer?

JD

Please login first before posting an answer.