MySQL Datatypes

getyourbiglobst posted this at 00:41 — 19th January 2002.

Joined: Oct 2001

Hello-

i have created the following columns in my table:

apn INT(11) ---for a 6-10 digit number
company INT(1) ---for a 1 digit company code
property INT(11) ---for a 6-8 digit number
annualized DECIMAL (7,2) ---for a $ amt.
fee DECIMAL (6,2) ---for a $ amount

after i create the table i attempt to load a .csv like so:
LOAD DATA LOCAL INFILE '/nameoffile.csv' INTO TABLE table_name

the message says nothing is dropped, but there are tons of warnings? i check the table and the only thing that shows is the apn column.

am i declaring the datatypes properly?

do i already have to have a column with primary key, or can i add that column later?

i'm new to this.. thanks for your reply.

mairving posted this at 02:06 — 19th January 2002.

They have: 2,256 posts

Joined: Feb 2001

If you use the load data statement, MySQL is expecting a text file that is tab delimited, i.e. no commas. I think the proper syntax for a comma delimited file is:
LOAD DATA LOCAL INFILE "pathtofile/filename.csv" fields terminated by , into TABLE tablename;

You can also optionally select which fields to import. For instance, if your company name was the same, just leave the field out and change the column default to the company name. So if field 4 had a default value, just leave it off of your load data statement. Then do it like so:
LOAD DATA LOCAL INFILE "pathtofile/filename.csv" fields terminated by , into TABLE tablename (fieldname1, fieldname2, fieldname3, fieldname5);

Mark Irving
I have a mind like a steel trap; it is rusty and illegal in 47 states

Wil posted this at 18:27 — 19th January 2002.

They have: 601 posts

Joined: Nov 2001

It would also help to see a sample of the data you're trying to import into your database.

You're also going to save some memory by changing those to SMALLINT.

- wil

getyourbiglobst posted this at 19:50 — 22nd January 2002.

They have: 34 posts

Joined: Oct 2001

here are some actual column names and values.

APN
------
417666

COMPANY_ID
----------
2

PROPERTY_ID
-----------
8231501

ANNUALIZED
----------
444.00

FEE
----
14.67

wil- just curious as to why i should change to small INT?
thanks for the help.