Re: Uploading CSV
Thank you for your response.
CREATE TABLE IF NOT EXISTS `Members` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`username` varchar(50) COLLATE latin1_general_ci NOT NULL,
`firstname` varchar(50) COLLATE latin1_general_ci NOT NULL,
`surname` varchar(75) COLLATE latin1_general_ci NOT NULL,
`password` varchar(50) COLLATE latin1_general_ci NOT NULL,
`email` varchar(75) COLLATE latin1_general_ci NOT NULL,
`active` tinyint(1) NOT NULL,
`code` varchar(75) COLLATE latin1_general_ci DEFAULT NULL,
`role` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
`avatar` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;
But now I have a different problem.
I export the file as a CSV file
I save it on a spreadsheet with | separators so that it may be sorted on a column
With no changes made at all I then go to import the file and am greeted with the error message"
#1044 - Access denied for user 'dbo741795594'@'%' to database 'CSV_DB'
This is the CSV file:
"1" "User10" "Peter" "Green" "35f504164d5a963d6a820e71614a4009" "email_address" "1" "NA" "Member" NULL
"5" "User30" "Fred" "Bloggs" "1a1dc91c907325c69271ddf0c944bc72" "email_address" "0" "NA" "Administrator" NULL
"4" "User20" "Joe" "Brown" "1a1dc91c907325c69271ddf0c944bc72" "email_address" "1" NULL "Administrator" NULL
I now realise that if I export the table as a SQL file I can extract the data, sort it and then return it to the SQL file, avoiding the need to export as CSV.
However, when I then try to import the revised file I have the following error:
SQL query:
--
-- Dumping data for table `Members`
--
INSERT INTO `Members` (`id`, `username`, `firstname`, `surname`, `password`, `email`, `active`, `code`, `role`, `avatar`) VALUES
(1, 'User10', 'Peter', 'Green', '35f504164d5a963d6a820e71614a4009', 'email_address', 1, 'NA', 'Member', NULL),
(5, 'User30', 'Fred', 'Bloggs', '1a1dc91c907325c69271ddf0c944bc72', 'email_address', 0, 'NA', 'Administrator', NULL),
(4, 'User20', 'Joe', 'Brown', '1a1dc91c907325c69271ddf0c944bc72', 'email_address', 1, 'NA', 'Administrator', NULL);
MySQL said: Documentation
#1062 - Duplicate entry '1' for key 'PRIMARY'
Presumably I need to do something to make the file overwrite the entry with ID 1, but what?
Secondary question.
If I sort the files into a different order, do I have to keep the same ID number for the entries?