# Database Discussions > MySQL >  Why can I only import 127 rows of information!?

## mote

Okay, I have a semi-colon delimited textfile that I'm trying to import into phpmyadmin...everytime I do it, everything seems to go into the right columns, except that I can only EVER import 127 rows! That's it! No more than that!
Does anyone have a clue why this might be? My life hangs in the balance....please help me  :Frown:

----------


## nicc777

It would help if you post a sample of say the first 10 lines as well as the last three lines. You can replace the values with dummy data if you want.

Reason I ask is that we first need to validate the syntax of the given file, before looking for other problems.

Cheers

----------


## mote

Here are the first few lines. They have 7 different columns but the 4th column is always a big one...a description of the tool:

pow;Bosch;3/8in. VSR Drill ;"3/8in. keyless chuck, variable speed trigger with reversing switch for greater control, lock-on switch for ease of operation, ergonomic handle for increased comfort.";$99;BOS1005VSRK;images/bosch/1005VSRK.jpg
pow;Bosch;3/8in. VSR Drill ;"6.5 Amp all ball bearing motor, 0-1100 RPM, one handed keyless chuck , variable speed, soft grip trigger, with reversing switch for greater control, two finger trigger design and lock-on button for ease of operation, ergonomic handle with soft grip for increased comfort, precision cut steel gears, magnesium front housing for increased durability, extra long 8' rubber cord with 3-prong grounded plug, 360° auxiliary handle with optional depth gauge.";$149;BOS1011VSR;images/bosch/1011VSR.jpg
pow;Bosch;1/2in. VSR Drill;"6.5 Amp all ball bearing motor, 0-850 RPM.Heavy duty keyed chuck.Variable speed, soft grip trigger, with reversing switch for greater control.Two finger trigger design and lock-on button for ease of operation.Ergonomic handle with soft grip for increased comfort.Precision cut steel gears.Magnesium front housing for increased durability, provides maximum control in tough applications.Extra long 8' rubber cord with 3-prong grounded plug.360° auxiliary handle with optional depth 
gauge.";$169;BOS1013VSR;images/bosch/1013VSR.jpg
pow;Bosch;1/2in. VSR Heavy-Duty Drill;1/2in. VSR HEAVY-DUTY DRILL 0-550 RPM 4.8A;$229;BOS1023VSR;images/noimage.jpg

The last few lines are:

woo;King;16in. 2HP Planer w/Stand;"Powerful 2 HP motor.Cutterhead raises and lowers on four precision 
ground columns.Anti-kickback fingers in front of the infeed roller to prevent wood kickback.Three knife cutterhead. Comes complete with stand, planing knives, knife setting guage and 4in dust collector outlet.Lever allows you to shift gears for changing feed rates for excellent results in planing hard and soft 
woods.";"$1,299";SA-406C;images/king/SA-406C.jpgwoo;Skil;7 1/4in. Wormdrive Circ Saw;7 1/4in WORMDRIVE CIRCULAR SAW 13A;$229;SKIHD77;images/noimage.gif
woo;Skil;7 1/4in. Magnesium in.Mag 77in. Wormdrive Circ Saw;7 1/4in MAGNESIUM in.MAG 77in WORMDRIVE CIRCULAR SAW;$249;SKIHD77M;images/noimage.gif\*The last few lines are:*

----------


## nicc777

I quick spot check brought the following under my attention:

In the first rows, the price is indicated by $99, where the last lines, they are indicated by "$1,299" ( note the qoutes ). The exporter that created the data might have interpreted the $1299 as a string type, hence exporting it as a string and not as a currency. 

Now, when importing data, mysql will go happily along until it meets the first error, which in this case might very well be the discrepency I noted. What you need to do is check where the first occurence of the "$x,xxx" is - if it's on Line 127/128 - you have found the problem. If not, I am going to ask that you post lines 126 to 128 as well, so that we can look some more at the area where the problem occurs.

Cheers

----------


## mote

woo;Porter Cable;3-1/4 HP Speedmatic Plunge Router;"15 Amps, AC only. 
Electronic five-speed with soft start 10,000, 13,000, 16,000, 19,000, 21,000 RPM. 
Maintains constant speed under load. 
1/2in.  collet. 
New auto-release collet system ends frozen bits. 
Unique design puts bottom bearing closer to work for minimum bit run-out and vibration. 
3in.  plunge range with fully enclosed springs. 
Depth rod, and 6 position step turret. 
Precision machined aluminum motor housing and base. 
100% sealed ball bearings. "; $588.00 ;DEL7539;images/delta/7539.jpg
cor;Porter Cable;19.2 volt Cordless Router;"19. 2 Volts, 2 Amp/hour battery for more power & longer runtime. 
Interchangeable Network battery system powers drill, hammer-drill, circular saw, reciprocating saw, jig saw,router and more. 
Fully compatible with all model 690 bases - can be used as a plunge router in the 6931 base or as a D-Handle router in the 6911 base.  Fixed base, model 1001, is included. 
Powerful 600 Watt, 23,000 RPM motor handles most jobs that a 1-1/2 Peak HP router can do. 
Long-Life motor brushes are replaceable. 
Slide-in battery allows compact size and great balance with weight positioned directly over your work. 
Convenient spindle-lock feature so bits can be changed with only one wrench. "; $389.99 ;DEL9290;images/delta/9290.jpg
woo;Porter Cable;3 X 21 Dustless Belt Sander;3in.  X 21in.  DUSTLESS BELT SANDER VS W/CASE; $349.00 ;DEL9352VS;images/noimage.gif
woo;Delta;Variable Speed Profile Sander Kit;"All the features of our standard profile sander. 
Variable speed from 2,100 to 6,000 strokes per minute for added control of your removal rate. 
50% increase in stroke length for added removal capability. 
Standard Equipment: Standard hook & loop sanding pad #14444, single profile mounting plate #14445, offset profile mounting plate #14446, 9 different sanding profiles, one sheet each of 80, 120, 220, grit hook & loop paper, assorted profile sanding sheets, #44444 carrying case, and operating manual. 
A dust wand may be purchased as an accessory. "; $199.00 ;DEL9444VS;images/delta/9444vs.jpg
woo;Porter Cable;Quik-Change Bayonet Saw Kit ;"Powerful 6 amp motor powers through even the hardest materials. 
Quik-Change blade clamp lets you change blades in seconds. 
Unique guide system keeps blade running straight and true whether in vertical or angle cuts up to 45 degrees left or right. 
Accepts tang-shark blades for reduced blade breakage. 
Four-position selection lever for choice of reciprocating blade stroke or three degrees of orbit. 
Tilting base can be adjusted with built-in wrench, base includes detents for accurate 15 degree, 30 degree, and 45 degree positioning left and right. 
Extra-large base provides greater stability in all cuts and can be adjusted to enable cuts close to a vertical surface. 
Dust blower for clearing line of cut.  May be turned off for metal cutting with coolants. 
Cust collection capability when used with accessory vacuum collector and your vac. "; $299.99 ;DEL9543;images/delta/9543.jpg
woo;Porter Cable;1-3/4 Peak HP Router;"120 Volts, AC only/10 Amps/23,000 RPM 1/4in.  and 1/2in.  bit capacity. 
New auto-release collet system ends frozen bits. 
Solid, die-cast and precision machined aluminum motor housing and base for greater accuracy and durability. 
Micrometer depth-of-cut control. 
Permanently sealed precision ball-bearing constuction. 
Heavy-duty motor with high torque power. 
Dust sealed proof switch. 
lever release height adjustment. 
replaceable glass and smooth, non-marking base. 
Includes router, 1/4in.  & 1/2in.  collet, heavy-duty carrying case, wrenches and manual. "; $209.00 ;DEL9690LR;images/delta/9690lr.jpg

----------


## nicc777

Well, for one the data export is not consistent. As I mentioned earlier with the currency, I see a similar ( but on a different field ) thing on line 128:

Your dataline seems to follow the following pattern:

x;x;x;"x";$xxx.xx;x;x

Line 128 has the following pattern:

x;x;x;x;$xxx.xx;x;x

This also fits with the previous post of mine where the line in question had the following pattern:

x;x;x;"x";"$x,xxx";x;x

I can only say that you should really get the data file consistent in all aspects, and then try again.

Cheers

----------


## mote

I was thinking that perhaps it was my "prodDesc" column which was giving me problems because of the way it's structured. So I decided to delete that and the price column and just import the rest into phpmyadmin.

Alas, I'm getting only 127 rows again...this is driving me crazy. Maybe it's the version I have or something...anyway here's lines 125 to 130 if you can spot what might be wrong. I can't spot anything..

125;"woo";"Porter Cable";"3-1/4 HP Speedmatic Plunge Router";"DEL7539";"images/delta/7539.jpg"
126;"cor";"Porter Cable";"19.2 volt Cordless Router";"DEL9290";"images/delta/9290.jpg"
127;"woo";"Porter Cable";"3 X 21 Dustless Belt Sander";"DEL9352VS";"images/noimage.gif"
128;"woo";"Delta";"Variable Speed Profile Sander Kit";"DEL9444VS";"images/delta/9444vs.jpg"
129;"woo";"Porter Cable";"Quik-Change Bayonet Saw Kit ";"DEL9543";"images/delta/9543.jpg"
130;"woo";"Porter Cable";"1-3/4 Peak HP Router";"DEL9690LR";"images/delta/9690lr.jpg"

----------


## nicc777

I can't see anything strange either. As you say - maybe it has something to do with versioning, although I can't see how that should make a difference. I even tried to duplicate you problem on my side - but all I get is success. I tried with upto 100000 rows ( containing random data ).

I use phpMyAdmin 2.3.2 with MySQL 3.23.47 on Linux Mandrake 8.2.

Hope you can find the problem.

Cheers

----------


## mote

Friggin hell! Apparently 'tinyint' can only deal with up to 127 rows...by just CHANGING that to something else like 'smallint' or even 'integer' makes everything work....GEEZ...I've been trying to solve this one for 2 weeks!!! I'm PISSED!

Thanks for your help..

----------


## nicc777

That is true. I never thought you might be using tinyint, so I never bothered to ask. As the MysQL doc's say:

TINYINT[(M)] [UNSIGNED] [ZEROFILL]
    A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

Cheers

----------


## rwendel

If you are using any of the 'int' (tinyint, smallint(short), int, bigint(long)) types for a record id (Primary Key that is auto_increment with each INSERTed record), then you should definitly change those to UNSIGNED, unless of course you have them starting at the lower bound for that data type (-128 for tinyint, -32768 for smallint, -2147483648 for int, and -9223372036854775808 for bigint). If you make these columns unsigned then you have double the possible magnitude of the number.

----------

