Successful excel file import into MySQL says copied all 533 rows, but only 127 are showing up!

Does the excel file contain duplicate rows/primary keys?

There isn't really a limit on number of rows the server will probably full over first.

Actually, I should've been more clear- I'm quite new at all of this so I do apologize.

The file that I imported was a Comma-delimited file that I created from an Excel document. So, yes I did try that.

When you open up the CSV in Excel- the ID column does not have any repeating numbers at all, and there are no duplicate columns...

- Jeff

When you reopen the CSV in Excel are all the columns / rows correct, i.e. no cells have two cell values or have "" when you don't expect it?

Do any of the records themselves have commas in them? (just covering the basics)

Also make sure that all the records have the fields required by your mysql tables.

Try splitting your data into chunks of 150 records, then import them separetly. You might find that one batch has errors in one or more records.

Yeah, when I open up the CSV Excel file, everything looks normal. Well, sometimes some longer numbers because ###### only because the column isn't wide enough- but.. just adjust the column and it's fine. There aren't any quotes (") anywhere in any cell. I do have a comma in a few fields though like, for example, there is an address field which might say ... Rockland,NewMexico   but other than periods in an email address, there is no other random punctuation in any field. Some fields are blank- but they are supposed to be- because we don't have someone's phone number for example, so that field is left with nothing in it.

I asked about commas because if you're importing a comma delimited field, when phpMyadmin is trying to read the records it's assuming a new column starts when it finds a comma. So it might be thinking that your address field is 2 fields instead of one.

Try using a TAB delimited field.

also, for fields that are empty in the file you are importing. Make sure that the corresponding field in the table is not set to "required"

I am sure excel exports CSV as "value","value" so a , in a value should not matter, but no harm trying.

I will try the TAB format instead and I'll see what happens-

Also I do have some rows that are duplicates- but they aren't exact duplicates- like the first and last name is exactly the same but other information down the row is different, that shouldn't matter right? They both have unique ids....

I'll try the TAB format but I won't have access to a computer later tonight- I'll write back with the status tomorrow.

I did write one response that for some reason, I just noticed, didn't get posted-

When I'm saving the Excel file as a CSV, a window pops up:

Table.csv (or even .txt in the case of the TAB format) may contain features that are not compatible with Text (Tab delimited) or Comma (Comma delimited). Do you want to keep the workbook in this format?

To keep this format, which leaves out any incompatible features, click YES (this is what I select)
To preserve the features, click no. Then save a copy in the latest Excel format.
To see what might be lost, click Help.

Yes, No, Help

When I read the help file, the types of things it tells you are lost by saving it as a CSV or Text Tab file, aren't in any of the fields- so I'm really not sure what features it is talking about, but I figure that if I click Yes, to leave out the incompatible features, then it isn't much of an issue- but since we are troubleshooting, thought it was important to mention.

- Jeff

Can you paste a line of the CSV which fails to insert, replace private information with x's but leave everything else intact.

I have a guess at this, which is far from what many people are thinking, and it may or may not be correct.  I had an issue quite like this where about 100-160 records would write but the rest, no matter how many more, would fail.  This occurred in a windoze environment.  When I moved the same exact (yes, completely unchanged) script  to my Linux server, every record was entered flawlessly, every time.

My initial thought on that problem was:

Yet another open source project that does not play friendly with Micro$oft. Projects ported to windoze do not always work as expected.

My Workaround:  Only move about 110-120 records at a time, and watch to make sure that they are all successful.

My Solution: Format hard drive and install a linux disto.

You may already be running this script on a linux platform. If so, please disregard this comment.

One addition, this message:

Table.csv (or even .txt in the case of the TAB format) may contain features that are not compatible with Text (Tab delimited) or Comma (Comma delimited). Do you want to keep the workbook in this format?


is completely normal.  That is the message you get no matter what when you try to save an Excel file as tab/comma delimited.  Just select yes to save.  That message is what I like to call "Micro$oft trying to do more for you."  This only refers to losing proprietary excel formatting when saving as this file type (which is what you want).

Sorry I haven't written in a few days- weekend was busier than expected.

Anyway- I went to export the TAB file from excel- not a problem. However, when I signed into the phpMyAdmin, I am unable to import TAB files. All I have the option to import is CSV, CSV using Load Data, or SQL.

What I'm going to try to do is to split up the CSV file and do separate imports to see if that helps. Granted- this is by no means ideal because the CSV file has like six hundred records and I'll have to be using the 'import' option often to get new data into the database- but for the sake of troubleshooting, I'm going to try it and report back in a little bit.

The database is a Web-based database, so no files are being accessed from my computer. A user can input information into the database via a .php page that is housed on my webspace.

As a response to Evilolive- I believe that my webspace is housed on a Linux server. I could be wrong- I honestly don't know much about it, but it was my understanding that if it were a Microsoft server, I'd have to be using ASP, not PHP. Correct me if I'm wrong though.

Ok I'll write back in a bit. And for NCOO, I will paste a line from the line for you- when I write a response back to see how it went.

- Jeff

Well- I got it to work- but I'm honestly not sure what was going on.

For the first time ever, it gave me an error when I went to import it and not all of the fields copied over. It usually just says "success! 129 entries copied" ... when the file had like over 500. But when I went to do it again, this time it gave me an error saying that there was a duplicate entry at line 127.

I looked all around line 127 for anything of the sort, but found absolutely nothing. No weird punctuations hanging around, definitely no exact dupe, but I copied all of the fields in the document, and then pasted it into a brand new one. Since excel sheets scroll to infinity, my initial feeling was, eh, maybe there is something weird 100,000 cells away somehow. Well, when I pasted it into the new document, saved in, and then imported it, everything worked just fine.

So, I'm really not sure what the issue was. I scrolled out pretty far and for as far as I saw, there wasn't anything funky, but pasting it into a new excel/csv file seemed to work....

so, I guess this issue.... is ... done?

EH! WRONG!

Spoke to soon, I guess....

Although it said it was fine, when I go back, I see that it does indeed stop at record 127.

1-127 are fine, but then on line 128, it displays one last record which is someone with the last name beginning with "Y" which is one of the last rows in the file of 533 entries.

So, something is funky with line 127, but I can't figure it out for the life of me.... it looks fine.

Here it is:

id         first      last        address         town     state    zip          phone              course  major    term
127      XXX    XXXX  XX XXX XXXX    Xville      XX   XXXXX  XXX-XXX-XXXX   XXX       XXX      XXXXX      
email                                   profession        date created
XXXXX@XXXX.XXX                 XXXXX            X/X/XXXX

What I'm going to do now is to take this row out of the file, and see what happens....

Sorry for the false alarm of solving this...

I tried to split the csv file from records 1-127 and then 128-end

Interesting- line 127 imports just fine on the first file

But then I go to import the second half, and I get the same following error:


MySQL said: Documentation
#1062 - Duplicate entry '127' for key 1

Update--

yeah, I've tried just about everything. Once it gets to record 127, it just ... stops. I even tried creating a new line for it (not by means of importing) but just tried to insert a line in, it won't let me do it. It just gives me the same #1062 error- as if the table can't hold more than 127 rows....

Also- since I'm not sure what the issue is, I'm trying everything-

At first my csv files- the first line of the excel file was all of the column names, and then the second line was "1" and then started to contain information. But then I thought, hey wait, phpMyAdmin requires you to type in the column names in a text field before you submit the file for import- I thought to myself .. Hey, maybe you don't need to have them in your csv file also- maybe just have line 1 in excel be the first record-- this seemed to import just fine without the column names listed- which was cool, but it still didn't solve the problem of not getting past record 127

The problem has been fixed-

The records were stopping at 127 because the id field was type tinyint(4).

So if you ever hear someone come across the same issue at row 127, that's likely to be it.

-Jeff