MySQL Forums
Forum List  »  CSV Storage Engine

Re: Uploading CSV
Posted by: Peter Richards
Date: June 22, 2018 10:10PM

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?

Options: ReplyQuote


Subject
Views
Written By
Posted
661
June 21, 2018 10:16PM
359
June 22, 2018 07:56AM
Re: Uploading CSV
438
June 22, 2018 10:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.