[SOLVED] #1265 - Data truncated for column 'Price' at row 1

[chrisgarrison]

Hello,

I am trying to use MySQLAdmin to upload a csv file to insert data into a database table.

here is the code to create the table

CREATE TABLE `testproducts` (

  `ProductID` int(11) NOT NULL auto_increment,

  `Price` float(7,2) default '0.00',

  `Title` varchar(255) default NULL,

  `Description` text,

  `ModelNumber` varchar(255) default NULL,

  `InStock` int(11) default NULL,

  `manufacturer` varchar(255) NOT NULL default '0',

PRIMARY KEY  (`ProductID`)

) ENGINE=MyISAM AUTO_INCREMENT=149  ;

 

here is the code to insert data

 

INSERT INTO `testproducts` (`ProductID`, `Price`, `Title`, `Description`, `ModelNumber`, `InStock`, `manufacturer`) VALUES

(2,12.95,'Pro Silver Locking Carabiner','This is a Pro Silver Locking Carabiner that is designed using the highest quality manufacturing processes.  Each carabiner is hand inspected, and rated to the highest levels.\r\n\r\nThis is a sample product paragraph for the above graphic.  This can include a small note about what it is, how it was built, and the features the product posesses.\r\n\r\nThis paragraph can contain as much text as needed.  It could also be copied and pasted for speed.\r\nuse as much space here as needed and also be sure to include a description as it makes your products much more interesting to clients.','asd238923',1,'Black Diamond');

 

this all works fine..........

now i want to add data from a csv by clicking import in mysqladmin

then i browse to the txt file,

i select

CSV

Replace table data with file is checked

Ignore duplicate rows is checked

Fields terminated by ,

Fields enclosed by "

Fields escaped by \

Lines terminated by auto

Column names Price, Title, Description ,ModelNumber, InStock, manufacturer

here is my csv file

 

"Price""Title""Description""ModelNumber""InStock""manufacturer"

12.00,rtacab,Base 3 Drawer 15,001,yes,rzacabinets\

 

and here is my error

 

#1265 - Data truncated for column 'Price' at row 1

 

MYSQL

# Server version: 5.0.77-community-nt

# Protocol version: 10

 

Web server

 

    * Microsoft-IIS/6.0

    * MySQL client version: 5.0.51a

 

I do not get it. i can insert data, but i can not use the csv file. the float field is causing the error i believe.

 

 

[DEVILofDARKNESS]

You will have to show us the Csv file,

or is it this one?:

here is the code to insert data

 

INSERT INTO `testproducts` (`ProductID`, `Price`, `Title`, `Description`, `ModelNumber`, `InStock`, `manufacturer`) VALUES

(2,12.95,'Pro Silver Locking Carabiner','This is a Pro Silver Locking Carabiner that is designed using the highest quality manufacturing processes.  Each carabiner is hand inspected, and rated to the highest levels.\r\n\r\nThis is a sample product paragraph for the above graphic.  This can include a small note about what it is, how it was built, and the features the product posesses.\r\n\r\nThis paragraph can contain as much text as needed.  It could also be copied and pasted for speed.\r\nuse as much space here as needed and also be sure to include a description as it makes your products much more interesting to clients.','asd238923',1,'Black Diamond');

 

 

 

[chrisgarrison]

You will have to show us the Csv file,

or is it this one?:

here is the code to insert data

 

INSERT INTO `testproducts` (`ProductID`, `Price`, `Title`, `Description`, `ModelNumber`, `InStock`, `manufacturer`) VALUES

(2,12.95,'Pro Silver Locking Carabiner','This is a Pro Silver Locking Carabiner that is designed using the highest quality manufacturing processes.  Each carabiner is hand inspected, and rated to the highest levels.\r\n\r\nThis is a sample product paragraph for the above graphic.  This can include a small note about what it is, how it was built, and the features the product posesses.\r\n\r\nThis paragraph can contain as much text as needed.  It could also be copied and pasted for speed.\r\nuse as much space here as needed and also be sure to include a description as it makes your products much more interesting to clients.','asd238923',1,'Black Diamond');

 

 

no, here is my csv code

 

"Price""Title""Description""ModelNumber""InStock""manufacturer"

22.95,Catch More fish,jigging tips,zzz666666,1,sillycow\

 

 

 

[DEVILofDARKNESS]

I think it should be:

Price,Title,Description,ModelNumber,InStock,manufacturer
22.95,Catch More fish,jigging tips,zzz666666,1,sillycow

 

Check:

http://en.wikipedia.org/wiki/Comma-separated_values

[chrisgarrison]

I think it should be:

Price,Title,Description,ModelNumber,InStock,manufacturer
22.95,Catch More fish,jigging tips,zzz666666,1,sillycow

 

Check:

http://en.wikipedia.org/wiki/Comma-separated_values

 

nah, that's not it. I can set the csv file however I want. "" , it does not matter. if i change the variable to varchar, it works fine. the problem seems to be with floats and integers. theses variables seem to set off the error.

 

i need to get this going. I am going to try using varchar until I get a better answer. i do not what else to do??

 

i have another question

 

this is for a storefront. it comes with a bunch of data already. now i am trying to add a customers products to the products table provided with the software.

 

the only thing is the customer has quite a few less fields than the products table provided.

 

either i am going to have to make a bunch of fields null, or create a new table, and alter the code to pull from the new table and display on page.

[DEVILofDARKNESS]

Why do you not generate a php code with 'INSERT INTO ...' ???

Or how should users give the info to you?

[Mchl]

A bit unrealted, but still: do not store money as FLOAT... use DECIMAL instead.

[chrisgarrison]

the data is in a csv file, that is how i need to import the data. there are thousands of parts

[fenway]

Why doesn't the header row have commas?

[chrisgarrison]

Why doesn't the header row have commas?

 

i do not need them

[fenway]

Well, if you're actually importing that first row, mysql will barf.

[chrisgarrison]

Well, if you're actually importing that first row, mysql will barf.

 

nah, it works fine, like i said, i just can not import floats into the table from the csv file. if i change the field to varchar, everything works great.

[fenway]

Well, if you're actually importing that first row, mysql will barf.

 

nah, it works fine, like i said, i just can not import floats into the table from the csv file. if i change the field to varchar, everything works great.

Then it doesn't "work fine" -- it's complaining that the value from the first row -- the actual word "price" -- isn't a floating point value.  What a surprise.

[chrisgarrison]

Well, if you're actually importing that first row, mysql will barf.

 

nah, it works fine, like i said, i just can not import floats into the table from the csv file. if i change the field to varchar, everything works great.

Then it doesn't "work fine" -- it's complaining that the value from the first row -- the actual word "price" -- isn't a floating point value.  What a surprise.

 

sorry, i do not get it, the field is price, the value is 22.95, how is that not a floating point value??

[chrisgarrison]

Well, if you're actually importing that first row, mysql will barf.

 

nah, it works fine, like i said, i just can not import floats into the table from the csv file. if i change the field to varchar, everything works great.

Then it doesn't "work fine" -- it's complaining that the value from the first row -- the actual word "price" -- isn't a floating point value.  What a surprise.

 

sorry, i do not get it, the field is price, the value is 22.95, how is that not a floating point value??

 

oooppps, i am so sorry, you were right, it works, i see what you mean, and i know what i was doing wrong.

thanks