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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bang-O-MaticAuthor Commented:
Thanks for the help!
0
Julian HansenCommented:
You are welcome - thanks for the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.