Member
Registered: May 2009
Location: ACT - Australia
Distribution: Opensuse x86_64 (Latest)
Posts: 132
Rep:
|
need help with mysqlimport importing data problem
Hello,
I need help with mysqlimport importing data problem.
data loaded into first field only ,it should go across fields.
How is that done ?
Thanks
--Glenn
import data example
##
I85,Gleeson,John William
I86,Gleeson,Peter Graham
I87,McRae,Heather Joy
I88,Chambers,Judith Ann
I89,Chambers,Helen Margaret
I90,Chambers,Elizabeth Joyce
I91,Haberecht,Robert Paul
I92,Haberecht,Susan Gai
I93,Haberecht,John Graham
I94,Haberecht,Robyn Elizabeth
I95,Haberecht,Cathryn Ann
I96,Haberecht,Wendy Louise
I97,Taylor,Neil
I98,Taylor,David Neil
.
.
##
drop table people;
create table people (
INDIID varchar(30) ,
Surname varchar(30) ,
Given varchar(30) ,
Sex varchar(30) ,
BirDate varchar(30) ,
BirPlace varchar(30) ,
DeaDate varchar(30) ,
DeaPlace varchar(30)
);
MariaDB [test]> create table people (
-> INDIID varchar(30) ,
-> Surname varchar(30) ,
-> Given varchar(30) ,
-> Sex varchar(30) ,
-> BirDate varchar(30) ,
-> BirPlace varchar(30) ,
-> DeaDate varchar(30) ,
-> DeaPlace varchar(30)
-> );
Query OK, 0 rows affected (0.09 sec)
MariaDB [test]>
# mysqlimport --local test people.csv
test.people: Records: 890 Deleted: 0 Skipped: 0 Warnings: 7120
MariaDB [test]> create table people (
-> INDIID varchar(30) ,
-> Surname varchar(30) ,
-> Given varchar(30) ,
-> Sex varchar(30) ,
-> BirDate varchar(30) ,
-> BirPlace varchar(30) ,
-> DeaDate varchar(30) ,
-> DeaPlace varchar(30)
-> );
Query OK, 0 rows affected (0.08 sec)
MariaDB [test]> exit;
Bye
mysqlimport --ignore-lines=1 --fields-terminated-by=,--columns='INDIID,Surname,Given,Sex,BirDate,BirPlace,DeaDate,DeaPlace' --local -u root -p Database /path/to/csvfile/TableName.csv
#
MariaDB [test]> select INDIID from people;
+--------------------------------+
| INDIID |
+--------------------------------+
| I85,Gleeson,John William,M,1 A |
| I86,Gleeson,Peter Graham,M,17 |
|7,McRae,Heather Joy,F,,,,
| I88,Chambers,Judith Ann,F,10 J |
| I89,Chambers,Helen Margaret,F, |
| I90,Chambers,Elizabeth Joyce,F |
| I91,Haberecht,Robert Paul,M,,, |
| I92,Haberecht,Susan Gai,F,28 O |
| I93,Haberecht,John Graham,M,29 |
| I94,Haberecht,Robyn Elizabeth, |
| I95,Haberecht,Cathryn Ann,F,31 |
| I96,Haberecht,Wendy Louise,F,8 |
|or,Neil,M,,,,
| I98,Taylor,David Neil,M,5 Feb |
|