MySQL data type question

MySQL 5.6
I'm trying to use LOAD DATA INFILE to get .txt file data into my table.
I have a  field named acreage in my table and it is type "int"
Below are some examples of the acreage data contained in the .txt file.

Acreage
0.00
0.57
200.57
0.57
0.00
891.38
0.13

when I try to insert the data I get "Incorrect integer value" error.
I tried a couple different things like setting the field to decimal 8,2 but still get the same error.  I can insert the data if the field is set to varchar, but I need to be able to use this field in a numeric search.

Any help greatly appreciated!
Bang-O-MaticAsked:
Who is Participating?
 
Julian HansenCommented:
It is pretty straightforward to deal with the spaces. You can try something like this
LOAD DATA INFILE 'e:/projects/ee/d.txt' 
INTO TABLE t1 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(@acerage)
SET acerage = CASE 
   WHEN @acerage ='' THEN 0 
   ELSE @acerage 
END

Open in new window

0
 
arnoldCommented:
Can you post the show create table as well as the command you are using with load data
Infile
0
 
Julian HansenCommented:
This worked for me (tried with both float and decimal(8,2)
Test table
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `acerage` float DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Open in new window

Sample input
Acreage
0.00
0.57
200.57
0.57
0.00
891.38
0.13

Open in new window

SQL Query
LOAD DATA INFILE 'e:/projects/ee/d.txt' 
INTO TABLE t1 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(acerage);

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
F PCommented:
Use a Decimal type field. I usually set mine to (8, 2) for monetary fields which works for hundreds of thousands.

https://dev.mysql.com/doc/refman/5.0/en/precision-math-decimal-characteristics.html
0
 
F PCommented:
0
 
Bang-O-MaticAuthor Commented:
Thanks for the help!

Setting the field to decimal 8,2 works until it hits an empty field then I get

"Incorrect decimal value: '' for column 'Acreage' at row 6"

row 6 is empty.  Some fields have 0.00, some are just empty.
0
 
arnoldCommented:
Load data infile does not validate content it works on the basis that the data is correct and valid.

Validate_input.pl

#!/usr/bin/perl

while (<>) {
chomp();
s/^\s+$//;
If (length($_)  { $_='0.0';}
print "$_\n";
}

Open in new window

Use that to process your input and the write it out to a new file that will then be used with load data infile.
The other option is to filter out empty rows.
cat file | egrep -v  "^$"
That should exclude the empty line the ^ beginning of line, $ end of line. No space between them. If your data has space, that could pose an issue (the perl script example will strip out lines full of space/s.
0
 
Bang-O-MaticAuthor Commented:
I'm using a Coldfusion query:


<cfquery name="loadTable" datasource="mydatasource">

LOAD DATA INFILE '#replaceNoCase(ExpandPath( 'rets/land.txt' ),"\","/","all")#'
INTO TABLE myTable.wRes FIELDS TERMINATED BY '\t'
LINES terminated by '\r\n' IGNORE 1 LINES

(ListID,
Type,
Price,
AddressNumber,
AddressStreet,
City,
State,
Zipcode,
Status,
SubArea,
Subdivision,
Agent,
LOfficeID,
Acreage,
LotSizeDim,
LotAccess,
LotFeatures,
Directions,
ListDate,
UpdateDate,
HOAFee,
HOATerm,
PhotoCount,
ProjectName,
Remarks,
UnitNumber,
AvailableUtilities,
WaterFrontage,
WaterFront,
WaterView,
Zoning,
FTROwnership,
FeesInclude,
MainArea,
ParcelID,
Photos)

SET Class = "LAND", Acreage = nullif(Acreage,'')

</cfquery>
0
 
Bang-O-MaticAuthor Commented:
Thanks for the help!
0
 
Julian HansenCommented:
You are welcome - thanks for the points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.