LOAD DATA LOCAL INFILE does not load entire txt file

[sjuarez1979]

Here are the two sql commands:


CREATE TABLE Drop_5_18_2002 (ID TINYINT (3) DEFAULT '0' NOT NULL AUTO_INCREMENT, BBID DOUBLE, BBval FLOAT DEFAULT '0', UNIQUE(ID))

LOAD DATA LOCAL INFILE 'C:/WebContent/Primm/DataFiles/DropData/B5_18_2002.txt' INTO TABLE StateLine.Drop_5_18_2002 FIELDS TERMINATED BY ',' LINES TERMINATED BY '*' (BBID,BBVal)


It will not read all of the records specified in the file. Why not?

 

[KarveR]

how big is the file? ***************************************
Party on, dudes!

 

[sjuarez1979]

about 6K, does the size of the file affect the number of records mySQL reads in?

Thanks in advance!

Seth Juarez
Tech Consultant

 

[KarveR]

Yes but not normally until you exceed about a 800meg and then its down to timeouts or incorrect setup.

Also you don't need this DEFAULT '0' NOT NULL, the field does not exist until a record is placed in it and then it becomes populated.

Can you post your textfile?

***************************************
Party on, dudes!

 

[sjuarez1979]

Here:
510000003758,126,*
510000004098,138.75,*
510000003860,184.5,*
510000003720,140.75,*
510000004134,151.5,*
510000003740,59.75,*
510000003723,90,*
510000003786,109.75,*
510000004082,290.5,*
510000003878,193.25,*
510000003797,143.5,*
510000003795,145.5,*
510000003821,19,*
510000004141,0,*
510000004088,62.75,*
510000003845,25.5,*
510000004149,44.25,*
510000004139,51.5,*
510000003718,10.25,*
510000003767,0,*
510000004071,571.25,*
510000003857,136.75,*
510000004077,122.25,*
510000003753,76.25,*
510000003806,24,*
510000002465,312.75,*
510000003695,34.75,*
510000003809,0,*
510000004172,24.75,*
510000004095,183,*
510000003868,147.75,*
510000003773,0,*
510000003856,165,*
510000004092,91,*
510000004100,28.25,*
510000003858,0,*
510000003770,83.75,*
510000003815,0,*
510000004164,14,*
510000004118,42.5,*
510000004198,69,*
510000003768,18.25,*
510000003812,72.5,*
510000003732,44,*
510000004148,8.5,*
510000003807,72.25,*
510000003854,9.75,*
510000004226,45.5,*
510000003745,50.5,*
510000003800,16,*
510000003782,57.25,*
510000004131,100.25,*
510000003780,34.5,*
510000004206,154.5,*
510000004115,22.5,*
510000003703,11.5,*
510000003706,0,*
510000003757,14.5,*
510000003831,29,*
510000004090,13,*
510000004136,25.5,*
510000004121,442.25,*
510000003717,18.5,*
510000003801,114.25,*
510000004216,85.75,*
510000003825,183.5,*
510000004183,114.75,*
510000004135,0,*
510000003805,19.5,*
510000003790,5.75,*
510000003789,14.75,*
510000003881,33.5,*
510000003870,13.75,*
510000004223,23.5,*
510000004217,221.25,*
510000004089,0,*
510000003694,35,*
510000003735,42.75,*
510000004122,17,*
510000004106,95,*
510000003726,91,*
510000003798,57.75,*
510000003722,103.25,*
510000003762,26.75,*
510000004083,15,*
510000003748,102.25,*
510000003742,27,*
510000003784,44,*
510000004147,7,*
510000004120,43,*
510000003574,245.5,*
510000003842,82.25,*
510000003872,32.75,*
510000003794,45.25,*
510000003871,0,*
510000003792,46.75,*
510000003775,36.5,*
510000003746,15,*
510000003727,13,*
510000003730,21.25,*
510000004128,83.75,*
510000004151,23.75,*
510000004209,76.25,*
510000004111,103.25,*
510000003820,128.5,*
510000004127,407.5,*
510000004166,0,*
510000004140,22,*
510000003729,40.5,*
510000003859,13.25,*
510000004099,186.25,*
510000004078,0,*
510000003747,55.5,*
510000004070,30.75,*
510000003741,29,*
510000003701,107,*
510000003862,114,*
510000004103,248,*
510000004159,0,*
510000003827,150.25,*
510000004144,370,*
510000003708,12,*
510000004224,22.25,*
510000002390,94.75,*
510000003841,59.25,*
510000003713,170.5,*
510000004201,228.25,*
510000004173,96.5,*
510000003725,150.25,*
510000004142,93.75,*
510000003791,20,*
510000003744,140,*
510000004178,108,*
510000003721,149.75,*
510000004145,145.75,*
510000003765,0,*
510000003763,57,*
510000004167,99.5,*
510000003848,0,*
510000003707,110.75,*
510000003876,113.25,*
510000004179,100.25,*
510000003819,363.25,*
510000004116,0,*
510000004101,82.75,*
510000003774,348.25,*
510000003804,46.5,*
510000004161,86.75,*
510000004091,157.25,*
510000003777,0,*
510000003875,123.25,*
510000004214,120.5,*
510000003712,107.25,*
510000002770,240,*
510000002810,49.25,*
510000003764,171.5,*
510000003710,219.75,*
510000004080,186.5,*
510000003816,168.25,*
510000003760,164.25,*
510000003724,50.25,*
510000003866,57.75,*
510000003704,65,*
510000003772,0,*
510000003781,353.75,*
510000003737,77.75,*
510000004094,212.5,*
510000004130,236,*
510000004181,54,*
510000004175,313,*
510000004239,67.5,*
510000003702,79.25,*

Thats it!
Thanks in advance
Seth

 

[sjuarez1979]

You want to hear something funny? If you set the ID to only go to 127, of course it wont read the whole file! Silly me. The auto increment index key field was a tinyint(3) and could not go beyond 127, so it dropped out and didnt bring in the rest of the file. Well silly me! At least the client will be happy now!


Thanks for the help,
Seth Juarez
Technology Consultant (when I'm thinking at least)

 

[KarveR]

OK try loading it as a sql script which can be found here :

http://www.virtualkev.com/stuff/

right click the file seth.sql and choose "save as", then use whatever you like to run it on the server.
Its totally harmless and you can view the contents in notepad or similar before running it. ***************************************
Party on, dudes!