CNB CNB - 2 months ago 8
SQL Question

Loading data to a table from a file

I need to load data to a table from a file using

LOAD DATA
command.
I've got a txt file that looks something like this:

1 "MARCA"#"MODELO"#"MATRICULA"#PRECIO
2 "CITROEN"#"PICASSA"#"CPG-2044"#12000
3 "CITROEN"#"PICASSA"#"CPR-1762"#12500
4 "CITROEN"#"C4"#"FPP-1464"#13500
5 "CITROEN"#"C4"#"FDR-4563"#13000
6 "CITROEN"#"C3"#"BDF-8856"#8000
7 "CITROEN"#"C3"#"BPZ-7878"#7500
8 "CITROEN"#"C2"#"CDR-1515"#5000
9 "CITROEN"#"C2"#"BCC-3434"#4500


Now, my first table is constructed as follows:

mysql> show columns from MARCAS;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID_MARCA | int(11) | NO | PRI | NULL | auto_increment |
| MARCA | varchar(50) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+


Now, I donĀ“t really know how to import data partially (as what I need to do is just load first 'column'. What I came up with is:

load data local infile /myfile.txt
into table MARCAS
fields terminated by '#'
lines terminated by '\n';


but that just does nothing (apart of suspending the terminal).
Help please?

Answer

You can also discard an input value by assigning it to a user variable and not assigning the variable to a table column: source: http://dev.mysql.com/doc/refman/5.7/en/load-data.html

load data local infile /myfile.txt
into table MARCAS(ID_MARCA, MARCA, @ignore1, @ignore2, @ignore3)
fields terminated by '#'
lines terminated by '\n';

footnotes:
Your query is most unusual in the sense that you have your column names in upper case and sql keywords in lowercase. The usual thing is to have it the other way round!

You have said your mysql console get's suspended, I do believe what you mean is that it takes a long time to return after this query is typed. If you have a large number of rows, there's nothing unusual in that.