CODEWITHSUNDEEP
mysqlsql
jasilva
jasilva

Reputation: 750

mysql LOAD DATA INFILE without FIELDS TERMINATED BY

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?

Upvotes: 2

Views: 901

Answers (2)

fancyPants
fancyPants

Reputation: 51928

You can use a variable in the LOAD DATA INFILE statement to hold the line first, then assign each column calculations on this variable. This way you don't need a helper table to first load the data into it and process it afterwards.

LOAD DATA INFILE 'D:....example.txt' INTO TABLE bulkdata
CHARACTER SET utf8
LINES TERMINATED BY '\r\n' (@my_fancy_variable)
SET column1 = SUBSTR(@my_fancy_variable, 1, 4),
column2 = SUBSTR(@my_fancy_variable, 5, 10),
...
;

Read more about it in the manual:

The column list can contain either column names or user variables. With user variables, the SET clause enables you to perform transformations on their values before assigning the result to columns.

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;

The SET clause can be used to supply values not derived from the input file. The following statement sets column3 to the current date and time:

LOAD DATA INFILE 'file.txt' INTO TABLE t1 (column1, column2) SET column3 = CURRENT_TIMESTAMP;

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column:

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @dummy, column2, @dummy, column3);

Use of the column/variable list and SET clause is subject to the following restrictions:

  • Assignments in the SET clause should have only column names on the left hand side of assignment operators.

  • You can use subqueries in the right hand side of SET assignments. A subquery that returns a value to be assigned to a column may be a scalar subquery only. Also, you cannot use a subquery to select from the table that is being loaded.

  • Lines ignored by an IGNORE clause are not processed for the column/variable list or SET clause.

  • User variables cannot be used when loading data with fixed-row format because user variables do not have a display width.

Upvotes: 2

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44373

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;

GIVE IT A TRY !!!

Upvotes: 1

Related Questions