LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Software (https://www.linuxquestions.org/questions/linux-software-2/)
-   -   need help with mysqlimport importing data problem (https://www.linuxquestions.org/questions/linux-software-2/need-help-with-mysqlimport-importing-data-problem-4175606551/)

Glenn D. 05-23-2017 09:14 PM

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 |

norobro 05-23-2017 09:40 PM

The following works to import .csv files into MySQL. Don't know about MariaDB:
Code:

mysql> load data local infile '/path/to/filename.csv' into table tablename fields terminated by ',' lines terminated by '\n';


All times are GMT -5. The time now is 10:40 AM.