Mysql - Load data infile - skip column in Mysql version 4

Question

I'm trying to import some data to database using: load data infile.

For example:

I have table (name: tab) with two columns: colA, colB.

In file there are three columns, example file content: value1|value2|value3

I want that value2 goes to colA and value3 goes to colB. I don't need value1.

In Mysql 5 I did it like this:

LOAD DATA INFILE <filename>
REPLACE
INTO TABLE tab
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r'
(@dummy, colA, colB);

It works great but the problem is when I try do it in Mysql 4. There is an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@dummy, colA, colB)' at line 6

I checked in mysql documentation: http://dev.mysql.com/doc/refman/4.1/en/load-data.html and in mysql 4 values from file can't be save to user variable (in my example to @dummy).

Do you have any ideas how to import (using load data infile) only some columns from file in Mysql 4?

Thanks

Why on EARTH are you using MySQL 4? It's not 1990 anymore... — eggyal
– eggyal, Commented Dec 27, 2013 at 13:52
I'm not using. There is a very old application using it and i had to work with it. This is also very big application which is using by many customers and upgrade to mysql 5 is not so easy. :P — user3139560
– user3139560, Commented Sep 16, 2014 at 8:11

eggyal · Accepted Answer · 2013-12-27 13:54:48Z

1

You could load the data into a temporary table and then use REPLACE ... SELECT:

CREATE TEMPORARY TABLE tmptab LIKE tab;
ALTER TABLE tmptab ADD COLUMN dummy TEXT;

LOAD DATA INFILE <filename>
  INTO TABLE tmptab
  FIELDS TERMINATED BY '|'
  LINES TERMINATED BY '\r'
  (dummy, colA, colB);

REPLACE INTO tmp (colA, colB) SELECT (colA, colB) FROM tmptab;

DROP TEMPORARY TABLE tmptab;

answered Dec 27, 2013 at 13:54

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3139560 Over a year ago

Hi, Thanks for answer. It is a good idea but this solve require to create another table, and i afroid that can be slow. Maybe there is a solution without creating temporary table ?

user3139560 Over a year ago

Finally i done it this way. It worked not so bad (although i had many files with many rekords). Thanks

Ataboy Josef · Accepted Answer · 2014-11-13 06:15:06Z

0

A comment to the last answer(Since I am unable to comment):

If the code:

LOAD DATA INFILE <filename>
  INTO TABLE tmptab
  FIELDS TERMINATED BY '|'
  LINES TERMINATED BY '\r'
  (dummy, colA, colB);

-leads to some warning, then use:

LOAD DATA INFILE <filename>
  INTO TABLE tmptab
  FIELDS TERMINATED BY '|'
  LINES TERMINATED BY '\r\n'
  (@dummy, colA, colB);

Thank You.

answered Nov 13, 2014 at 6:15

Ataboy Josef

2,1113 gold badges24 silver badges28 bronze badges

Collectives™ on Stack Overflow

Mysql - Load data infile - skip column in Mysql version 4

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related