mysql LOAD DATA INFILE without FIELDS TERMINATED BY

jasilva Published at Dev

jasilva

Hello guys I need load a file to a table

I use

LOAD DATA INFILE 'D:....example.txt' INTO TABLE test_table
CHARACTER SET utf8
LINES TERMINATED BY '\r\n';

My file look..

   4STPS      0000000480000015426  20040425            42.480060981876  20150325  11
   4STPS      0000000480000015458  20040425           110.240060981880  20150325  11
   4STPS      0000000480000015492  20040425           242.500060981883  20150325  11
   4STPS      0000000480000015583  20040510            92.510060981888  20150325  11
   4STPS      0000000480000015584  20040510            92.490060981889  20150325  11
   4STPS      0000000480000015592  20040510            87.140060981890  20150325  11
   4STPS      0000000480000015631  20040510           117.500060981896  20150325  11

My table is ...

CREATE TABLE `test_table` (
  `me` varchar(4) DEFAULT NULL,
  `group_b` varchar(10) DEFAULT NULL,
  `agent` varchar(9) DEFAULT NULL,
  `element` varchar(10) DEFAULT NULL,
  `ini_vig` decimal(8,0) DEFAULT NULL,
  `amount` decimal(10,4) DEFAULT NULL,
  `recive` varchar(8) DEFAULT NULL,
  `ven_rbo` decimal(8,0) DEFAULT NULL,
  `ado` varchar(3) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When I execute get this error Error Code: 1406. Data too long for column 'me' at row 1

What I did was count the spaces between the fields are alphanumeric, numeric fields only add between fields.

I cant change file structure How I can change the table so you can save the information?

RolandoMySQLDBA

Your LOAD DATA INFILE sees only one column at the moment

You will need to do the following

STEP 01 : Create a table with Single Column to Load Bulk Data

use test
DROP TABLE bulkdata;
CREATE TABLE bulkkdata
(
    id  INT NOT NULL AUTO_INCREMENT,
    txt CHAR(84),
    PRIMARY KEY (id)
);

STEP 02 : Load the Single Column of the Bulk Data

LOAD DATA INFILE 'D:....example.txt' INTO TABLE bulkdata
CHARACTER SET utf8
LINES TERMINATED BY '\r\n' (txt);

STEP 02 : Use SUBSTR() to extract field from the `txt` field

INSERT INTO test_table (me,group_b,agent,element,...)
SELECT
    SUBSTR(txt,1,4),
    SUBSTR(txt,5,10),
    SUBSTR(txt,15,9),
    SUBSTR(txt,24,10),
    ...
FROM bulkdata;