load data infile order by line

Question

I :) I need to insert 13500 lines and +500 columns from csv. So, I use load data infile and it's work.

But, I need exactly the same order in my MySQL database and my Csv.

Actually, for example, the 1000 line of the csv can be at the 800 place in my base

I need something like "Order by column1" but I don't find the clue.

Thank for your help

Ps : I have 2primary keys (ref of products) and the are not in the mathematical order (like 1, 8, 4, etc.)

EDIT : My code

$dataload = 'LOAD DATA LOCAL INFILE "'.__FILE__.'../../../../bo/csv/'.$nomfichier.'"
            REPLACE
            INTO TABLE gc_csv CHARACTER SET "latin1"
            FIELDS TERMINATED BY "\t"
            IGNORE 1 LINES
        ';

I just take the csv and use data local inline with him... And the order is'nt perfectly respected, I don't know why...

My design Table

CREATE TABLE `csv` (
  `example` int(20) unsigned NOT NULL,
  `example` int(15) unsigned NOT NULL,
  `example` varchar(10) default NULL,
[...]
  `example` varchar(4) default NULL,
  PRIMARY KEY  (`RefCatSYS`,`IdProduit`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

What exactly are you ordering by? I don't feel like there's enough information. Do you have any code samples? — David Wyly

Kostja Kostja · Accepted Answer · 2015-04-16T06:43:33

Add an auto_increment column to your table, with DEFAULT NULL. When you load data with LOAD DATA INFILE, there will be no value for the column, and it will get assigned an automatically generated id. Select data ordered by the column.

kostja@annie:~$ sudo cat  /var/lib/mysql/test/foo.csv   
10
9
8
7
6
5
4
3
2
1
mysql> create table tmp (example int primary key, id int unique auto_increment default null);
Query OK, 0 rows affected (0.11 sec)
mysql> load data infile "foo.csv" into table tmp;
Query OK, 10 rows affected, 10 warnings (0.03 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 10
mysql> select * from tmp;
+---------+----+
| example | id |
+---------+----+
|      10 |  1 |
|       9 |  2 |
|       8 |  3 |
|       7 |  4 |
|       6 |  5 |
|       5 |  6 |
|       4 |  7 |
|       3 |  8 |
|       2 |  9 |
|       1 | 10 |
+---------+----+
10 rows in set (0.00 sec)

load data infile order by line

3 Answers