Hi,
I am trying to load up my new database using MySQL. I am getting “error 1136: count does not match value count”. and none of the tables are getting data. Also five of the link tables have not even been formatted. Any ideas?
I have defined the columns in the tables to be precisely the same as those in the source file.
Here is the script of my commands:
DROP TABLE IF EXISTS excel_table;
CREATE temporary TABLE excel_table (
journalId INT not null,
Preview VARCHAR(255),
month VARCHAR(255),
year VARCHAR(255),
performerId INT not null,
Name VARCHAR(255),
Surname VARCHAR(255),
artsId INT not null,
Art VARCHAR(255),
groupId INT not null,
Groupname VARCHAR(255),
contactId INT not null,
LandLine VARCHAR(255),
mobile VARCHAR(255),
email VARCHAR(255),
website VARCHAR(255),
Address1 VARCHAR(255),
Address2 VARCHAR(255),
locationId int not null,
Locale VARCHAR(255),
County VARCHAR(255)
) DEFAULT CHARSET utf8;
LOAD DATA LOCAL INFILE ‘C:/wamp/www/brayarts/allDetails.csv’
INTO TABLE excel_table
CHARACTER SET utf8
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"’
LINES TERMINATED BY ’
’
IGNORE 1 LINES;
DROP TABLE IF EXISTS performers;
CREATE TABLE performers (
performerId int NOT NULL AUTO_INCREMENT ,
Name VARCHAR(255),
Surname VARCHAR(255),
primary key (performerId)
) DEFAULT CHARSET utf8;
INSERT INTO performers
SELECT distinctrow NULL, performerId, Name, Surname
FROM excel_table
ORDER BY Surname;
DROP TABLE IF EXISTS journals;
CREATE TABLE journals (
journalId INT not null AUTO_INCREMENT ,
Preview VARCHAR(255),
month VARCHAR(255),
year VARCHAR(255),
primary key (journalId)
) DEFAULT CHARSET utf8;
INSERT INTO journals
SELECT distinctrow NULL, journalId, Preview, Month, Year
FROM excel_table
ORDER BY journalId;
DROP TABLE IF EXISTS arts;
CREATE TABLE arts (
artsId INT not null AUTO_INCREMENT ,
Art VARCHAR(255),
primary key (artsId)
) DEFAULT CHARSET utf8;
INSERT INTO arts
SELECT distinctrow NULL, artsId, Art
FROM excel_table
ORDER BY art;
DROP TABLE IF EXISTS groups;
CREATE TABLE groups (
groupId INT not null AUTO_INCREMENT ,
Groupname VARCHAR(255)
primary key (groupId)
) DEFAULT CHARSET utf8;
INSERT INTO groups
SELECT distinctrow NULL, groupId, groupName
FROM excel_table
ORDER BY groupID;
DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts (
contactId INT not null AUTO_INCREMENT ,
LandLine VARCHAR(255),
mobile VARCHAR(255),
email VARCHAR(255),
webSite VARCHAR(255),
Address1 VARCHAR(255),
Address2 VARCHAR(255),
primary key (contactId)
) DEFAULT CHARSET utf8;
INSERT INTO contacts
SELECT distinctrow NULL, contactId, LandLine, mobile, email, webSite, Address1, Address2
FROM excel_table
ORDER BY contactId;
DROP TABLE IF EXISTS Locations;
CREATE TABLE Locations (
LocationId INT not null AUTO_INCREMENT ,
Locale VARCHAR(255),
County VARCHAR(255),
primary key (locationId)
) DEFAULT CHARSET utf8;
INSERT INTO locations
SELECT distinctrow NULL, locationId, locale, county
FROM excel_table
ORDER BY locationId;
DROP TABLE IF EXISTS performers__journals;
CREATE TABLE performers__journals (
Id INT NOT NULL auto_increment,
performerId int NOT NULL ,
journalId int not null,
primary key (performerId, journalId)
) DEFAULT CHARSET utf8;
INSERT INTO performers__journals
SELECT distinctrow NULL, performerId, journalId
FROM excel_table
ORDER BY journalId;
DROP TABLE IF EXISTS journals__Arts;
CREATE TABLE journals__Arts (
Id INT NOT NULL auto_increment,
journalId INT not null,
artsId INT not null,
primary key (journalId, artsId)
) DEFAULT CHARSET utf8;
INSERT INTO journals__Arts
SELECT distinctrow NULL, journalId, artsId
FROM excel_table
ORDER BY journalId;
DROP TABLE IF EXISTS journals__groups;
CREATE TABLE journals__groups (
Id INT NOT NULL auto_increment,
journalId INT not null,
groupId INT not null,
primary key (journalId, groupId)
) DEFAULT CHARSET utf8;
INSERT INTO journals__groups
SELECT distinctrow NULL, journalId, groupId
FROM excel_table
ORDER BY journalId;
DROP TABLE IF EXISTS performers__Arts;
CREATE TABLE journals__Arts (
Id INT NOT NULL auto_increment,
performerId INT not null,
artsId INT not null,
primary key (performerId, artsId)
) DEFAULT CHARSET utf8;
INSERT INTO performers__Arts
SELECT distinctrow NULL, performerId, artsId
FROM excel_table
ORDER BY performerId;
DROP TABLE IF EXISTS performers__groups;
CREATE TABLE journals__groups (
Id INT NOT NULL auto_increment,
performerId INT not null,
groupId INT not null,
primary key (performerId, groupId)
) DEFAULT CHARSET utf8;
INSERT INTO performers__groups
SELECT distinctrow NULL, performerId, groupId
FROM excel_table
ORDER BY performerId;
DROP TABLE IF EXISTS performers__locations;
CREATE TABLE journals__locations (
Id INT NOT NULL auto_increment,
performerId INT not null,
locationId INT not null,
primary key (performerId, locationId)
) DEFAULT CHARSET utf8;
INSERT INTO performers__locations
SELECT distinctrow NULL, performerId, locationId
FROM excel_table
ORDER BY performerId;
DROP TABLE IF EXISTS arts__groups;
CREATE TABLE arts__groups (
Id INT NOT NULL auto_increment,
artsId INT not null,
groupId INT not null,
primary key (artsId, groupId)
) DEFAULT CHARSET utf8;
INSERT INTO arts__groups
SELECT distinctrow NULL, artsId, groupId
FROM excel_table
ORDER BY artsId;
DROP TABLE IF EXISTS arts__locations;
CREATE TABLE arts__locations (
Id INT NOT NULL auto_increment,
artsId INT not null,
locationId INT not null,
primary key (artsId, locationId)
) DEFAULT CHARSET utf8;
INSERT INTO arts__locations
SELECT distinctrow NULL, artsId, locationId
FROM excel_table
ORDER BY artsId;
DROP TABLE IF EXISTS performers__contacts;
CREATE TABLE performers__contacts(
Id INT NOT NULL auto_increment,
performerId INT not null,
contactId INT not null,
primary key performerId, contactId)
) DEFAULT CHARSET utf8;
INSERT INTO performers__contacts
SELECT distinctrow NULL, performerId, contactId
FROM excel_table
ORDER BY performerId;
DROP TABLE IF EXISTS contacts__locations;
CREATE TABLE contacts__locations (
Id INT NOT NULL auto_increment,
performerId INT not null,
contactId INT not null,
primary key performerId, contactId)
) DEFAULT CHARSET utf8;
INSERT INTO contacts__locations
SELECT distinctrow NULL, contactId, locationId
FROM excel_table
ORDER BY contactId;
1136 column count does not match value