CODEWITHSUNDEEP
sqlexcelcsvimportphpmyadmin
Rory
Rory

Reputation: 75

csv import from excel into phpmyadmin missing data

I am currently trying to upload data from an excel spreadsheet into a sql server run through a university phpmyadmin. I have been able to upload other files successfully. However, when I upload one particular file with 3 columns and 20 rows, it instead uploads the 3 columns and only data for the 3 rows. I have been advised to tick the box that says "replace existing data" and have selected left "columns enclosed with" blank. This is the data I was hoping to upload:

FlyCrew01   Yes 1
FlyCrew02   Yes 2
FlyCrew03   No  3
FlyCrew04   Yes 4
FlyCrew05   No  5
FlyCrew06   Yes 6
FlyCrew07   Yes 7
FlyCrew08   Yes 8
FlyCrew09   Yes 9
FlyCrew10   Yes 10
FlyCrew11   Yes 11
FlyCrew12   Yes 12
FlyCrew13   Yes 13
FlyCrew14   Yes 14
FlyCrew15   Yes 15
FlyCrew16   Yes 16
FlyCrew17   Yes 17
FlyCrew18   Yes 18
FlyCrew19   Yes 19
FlyCrew20   Yes 20

The end result after uploading is this however:

Full texts  
Crew_ID Ascending
Available
Licence_Number

Edit Edit
 Copy Copy
 Delete Delete
FlyCrew0
Yes
9

Edit Edit
 Copy Copy
 Delete Delete
FlyCrew1
Yes
19

Edit Edit
 Copy Copy
 Delete Delete
FlyCrew2
Yes
20

As you can see, it is missing the data for the other 17 rows. This is the create table command I was using before I uploaded the data:

CREATE TABLE Unavailability 
(Crew_ID varchar (8) NOT NULL,
Available text (3),
Licence_Number int (8),
CONSTRAINT pk11 primary key (Crew_ID));

Can someone please tell me what I can do to fix this as I want to prevent it happening to other tables as well. Thanks.

Upvotes: 1

Views: 732

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

Your crew_id field has a data length of 8 characters, yet every single value you provided in your example is 9 characters long. Try changing the length to 9, ie:

CREATE TABLE Unavailability 
(Crew_ID varchar (9) NOT NULL,
Available text (3),
Licence_Number int (8),
CONSTRAINT pk11 primary key (Crew_ID));

The inserts should otherwise be valid as you can see here - http://sqlfiddle.com/#!8/74544/1/0

Upvotes: 1

Related Questions