loading data from text file into table in database

Question

Can someone tell me what i'm doing wrong here it's got to be something simple first I created some tables with foreign keys and then tried to import the data from textfiles and had all sorts of errors without ever getting the data in so now I tried to just create the tables load the data in then add the foreign keys hoping that would work but it isnt either heres what i've done

create table Books (
ISBN Char(10) not null,
Title Varchar(50) not null,
Price Decimal(5,2) null,
Authors Varchar(50) null,
Pages int null,
PubYear int null,
QTY int null,
Constraint Books_PK primary key(ISBN)
);



create table customers (
customerid int not null,
company varchar(30) null,
firstname varchar(30) null,
lastname varchar(30) null,
street varchar(50) null,
city varchar(30) null,
state char(2) null default 'NE',
zip char(5) null,
phone char(10) null,
constraint customer_pk primary key(customerid)
);

create table orders (
orderid int not null,
customerid int not null,
orderdate date null,
shipdate date null,
shipping decimal(5,2) null,
salestax decimal(5,2) null,
constraint order_pk primary key(orderid)
);

create table orderinfo (
orderid int not null,
isbn char(10) not null,
qty int not null,
price decimal(5,2) not null,
detailid int not null auto_increment,
constraint orderinfo_pk primary key(detailid)
);

load data infile 'C:/lab8/books.txt
into table books;

it gives me an error saying DATA TOO LONG FOR COLUMN ISBN IN ROW 1

the content of the textfile is

0929306279,  Bell labs,  29.95,  Gehani,  269,  2008,  121
0929306260,  Java,  49.95,  Sahni & Kumar,  465,  2008,  35
0670031844,  White Mughals,  34.95,  Dalrymple,  459,  2008,  78
0439357624,  Born Confused,  16.95,  Hidier,  432,  2007,  11

clearly the ISBN is 10 characters so why won't it go into the table?

It works for me using MySQL 5.1

feeela
– feeela

2012-12-08 22:39:53 +00:00
Commented Dec 8, 2012 at 22:39 — feeela
– feeela, Commented Dec 8, 2012 at 22:39

Olaf Dietsche · Accepted Answer · 2012-12-08 22:52:18Z

2

If you have , as a separator, you must say so.

load data infile 'C:/lab8/books.txt'
into table books
fields terminated by ',';

According to LOAD DATA INFILE Syntax, the default is a tab character \t.

If you specify no FIELDS or LINES clause, the defaults are the same as if you had written this:

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

edited Dec 8, 2012 at 22:52

answered Dec 8, 2012 at 22:38

Olaf Dietsche

74.4k9 gold badges113 silver badges214 bronze badges

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

3 Comments

dsquaredtech Over a year ago

oh yeah I forgot to put that in there I was doing it before when I had all the trouble with the foreign keys and didn't do it this time I knew it had to be something absent minded Thanks. Now hopefully when I get all the data in the tables the foreign keys will work.

dsquaredtech Over a year ago

So it will default the next line or row by \n which is a new line but what does \t mean?

Olaf Dietsche Over a year ago

@dsquaredtech '\t' means tabulator. In String Literals you will find a table "Special Character Escape Sequences", where all these characters are listed. I fixed the answer accordingly.

Collectives™ on Stack Overflow

loading data from text file into table in database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related