$50
Write the SELECT INTO OUTFILE and LOAD FROM INFILE statements
SELECT INTO OUTFILE is explained here.
LOAD DATA INFILE is explained here.
Just write the OUTFILE to a csv format, then write the INFILE to pull it back. Do not include the ids. I'm moving all the data from one table to another, but not the ids.
The tables:
mysql> explain final_import_user_class;
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| user_id | varchar(100) | YES | | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
| module_id | int(11) | YES | MUL | NULL | |
| content_id | int(11) | YES | MUL | NULL | |
| status | varchar(1) | YES | | NULL | |
| quiz_score | int(11) | YES | | NULL | |
| quiz_date_completed | date | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| course_id | int(11) | NO | | NULL | |
+---------------------+--------------+------+-----+---------+----------------+
mysql> explain final_import_user_course;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+----------------+
| user_id | varchar(100) | YES | | NULL | |
| course_id | int(11) | YES | MUL | NULL | |
| enrollment_date | date | YES | | NULL | |
| status | varchar(1) | YES | | NULL | |
| test_status | varchar(1) | YES | | NULL | |
| completion_date | date | YES | | NULL | |
| exam_score | int(11) | YES | | NULL | |
| prof_order | int(11) | YES | | NULL | |
| expiration_date | date | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+-----------------+--------------+------+-----+---------+----------------+
mysql> explain final_import_user_answer;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| user_answer_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | varchar(100) | YES | | NULL | |
| question_id | int(11) | YES | MUL | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
| course_id | int(11) | YES | MUL | NULL | |
| answer | varchar(1) | YES | | NULL | |
| correct | varchar(1) | YES | | NULL | |
| quiz_order | int(11) | YES | | NULL | |
| exam_order | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
mysql> explain final_import_course_evaluation;
+---------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+----------------+
| professor_interaction_com | varchar(80) | YES | | NULL | |
| course_evaluation | varchar(20) | YES | | NULL | |
| course_evaluation_com | varchar(80) | YES | | NULL | |
| spend_more_time_on | text | NO | | NULL | |
| improve_site_by | text | NO | | NULL | |
| like_course_about | text | NO | | NULL | |
| anything_else | text | NO | | NULL | |
| user_id | varchar(100) | YES | | NULL | |
| course_name | varchar(100) | YES | | NULL | |
| date_created | date | YES | | NULL | |
| course_organization | varchar(20) | YES | | NULL | |
| course_organization_com | varchar(80) | YES | | NULL | |
| materials_relevance | varchar(20) | YES | | NULL | |
| materials_relevance_com | varchar(80) | YES | | NULL | |
| length_of_modules | varchar(20) | YES | | NULL | |
| length_of_modules_com | varchar(80) | YES | | NULL | |
| text_clarity | varchar(20) | YES | | NULL | |
| text_clarity_com | varchar(80) | YES | | NULL | |
| quality_of_pictures | varchar(20) | YES | | NULL | |
| quality_of_pictures_com | varchar(80) | YES | | NULL | |
| quality_of_videos | varchar(20) | YES | | NULL | |
| quality_of_videos_com | varchar(80) | YES | | NULL | |
| value_of_ref_material | varchar(20) | YES | | NULL | |
| value_of_ref_material_com | varchar(80) | YES | | NULL | |
| value_of_tastings | varchar(20) | YES | | NULL | |
| value_of_tastings_com | varchar(80) | YES | | NULL | |
| quizzes_and_exam | varchar(20) | YES | | NULL | |
| quizzes_and_exam_com | varchar(80) | YES | | NULL | |
| ease_of_purchase | varchar(20) | YES | | NULL | |
| ease_of_purchase_com | varchar(80) | YES | | NULL | |
| website_usability | varchar(20) | YES | | NULL | |
| website_usability_com | varchar(80) | YES | | NULL | |
| professor_interaction | varchar(20) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+---------------------------+--------------+------+-----+---------+----------------+
UPDATE:
For importing the data, I see this page explains how to write a list of table columns. So I try this:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE user_answer (user_id,question_id,class_id,course_id,answer,correct,quiz_order,exam_order)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';but I get:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'' at line 3UPDATE:
Does anyone have any suggestions? I can not figure how to write the LOAD DATA INFILE so as to exclude the id field.
UPDATE:
So, my first row is:
"14502501085373900",528,100,100,"D","n",1,0And I try to load this with:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv' INTO TABLE user_answer (user_id,question_id,class_id,course_id,answer,correct,quiz_order,exam_order);And I get:
Warning | 1261 | Row 1 doesn't contain data for all columns Can anyone think why that would be? I've got 8 columns of data, and I specify 8 fields in the LOAD DATA INFILE command.
UPDATE:
Oh, right. If I do not specify:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';then it defaults to looking for tabs.
But I can not figure the syntax for FIELDS TERMINATED when combined with a list of columns.
UPDATE:
I just raised the prize to $40. Anyone have any thoughts about how to write the INFILE command? I need to get this done in the next 3 hours.
UPDATE:
Interesting. If simply do this:
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FROM final_import_user_answer
WHERE user_answer_id > 857461
;and then this:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE user_answer (user_id,question_id,class_id,course_id,answer,correct,quiz_order,exam_order)
;
I get:
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 3670 Deleted: 0 Skipped: 3670 Warnings: 1Skipped 3670 is disappointing. I run "show warnings" so I can see the warning:
Warning | 1264 | Out of range value for column 'user_answer_id' at row 1 Its as if it does not see the list of columns that I've specified. And yet, at this point, my load INFILE with a list of table fields is nearly identical to what MySql shows in the official documentation.
UPDATE:
mysql -V
mysql Ver 14.14 Distrib 5.1.37, for debian-linux-gnu (i486) using EditLine wrapper
UPDATE:
Now I am trying to use the SET syntax, but I am not getting it right.
I tried this:
mysql> LOAD DATA INFILE '/tmp/final_import_user_answer.csv'
-> INTO TABLE user_answer
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> (@user_id, @question_id, @class_id, @course_id, @answer, @correct, @quiz_order, @exam_order)
-> SET user_id = @user_id
-> SET question_id = @question_id
-> SET class_id = @class_id
-> SET course_id = @course_id
-> SET answer = @answer
-> SET correct = @correct
-> SET quiz_order = @quiz_order
-> SET exam_order = @exam_order
-> ;but I got:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET question_id = @question_id
SET class_id = @class_id
SET course_id = @course_' at line 7
Lawrence Krubner | 07/21/10 at 12:45pm
| Edit
(3) Possible Answers Submitted...
-
Last edited:
07/22/10
12:31pmWilliam Clark says:You want export and import done for each of these 4 tables?
Which id field do you want to exclude? Is it id?
- 07/21/10 1:06pm
William Clark says:Is this what you want?
SELECT user_id, class_id, module_id, content_id,status,quiz_score,quiz_date_completed,course_id
INTO OUTFILE '/tmp/final_import_user_class.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_class;
LOAD DATA LOCAL INFILE '/tmp/final_import_user_class.csv'
INTO TABLE final_import_user_class_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
SELECT user_id, course_id, enrollment_date,status,test_status,completion_date,exam_score,prof_order,expiration_date
INTO OUTFILE '/tmp/final_import_user_course.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_course;
LOAD DATA LOCAL INFILE '/tmp/final_import_user_course.csv'
INTO TABLE final_import_user_course_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
SELECT user_answer_id, user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_answer;
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
SELECT professor_interaction_com,course_evaluation,course_evaluation_com,spend_more_time_on,improve_site_by,like_course_about,anthing_elsee,user_id, course_name,date_created,course_organization,course_organization_com,materials_relevance,materials_relevance_com,length_of_modules,length_of_modules_com,text_clarity,text_clarity_com,quality_of_pictures,quality_of_pictures_com,quality_of_videos,quality_of_videos_comvalue_of_ref_material,value_of_ref_material_com,value_of_testings,value_of_testings_com,quizzes_and_exam,quizzes_and_exam_com,ease_of_purchase,ease_of_purchase_com,website_usability,website_usability_com,professor_interaction
INTO OUTFILE '/tmp/final_import_course_evaluation.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_course_evaluation;
LOAD DATA LOCAL INFILE '/tmp/final_import_course_evaluation.csv'
INTO TABLE final_import_course_evaluation_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
- 07/21/10 2:17pm
Lawrence Krubner says:William, almost perfect. Can you think of any reason why 330 records would be skipped?
mysql> LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
-> INTO TABLE user_answer
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
Query OK, 3340 rows affected (2.57 sec)
Records: 3670 Deleted: 0 Skipped: 330 Warnings: 0
- 07/21/10 2:27pm
Lawrence Krubner says:Is there an easy way to figure out which 330 records got skipped?
- 07/21/10 2:30pm
Lawrence Krubner says:Oh, sorry, I should have been more clear. I can not use user_answer_id. I'll adjust that and redo it.
- 07/21/10 2:36pm
Lawrence Krubner says:Huh. Odd. How do I get the import to ignore the id field? I'm getting a lot of skips.
- 07/21/10 2:38pm
Lawrence Krubner says:I tried:
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_answer
WHERE user_answer_id > 857461
;
which gives me 3670 records. But then this:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
-> INTO TABLE user_answer
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n';
gives me:
Query OK, 1 row affected, 11250 warnings (0.04 sec)
Records: 3670 Deleted: 0 Skipped: 3669 Warnings: 6455
- 07/21/10 2:42pm
Lawrence Krubner says:Uploading screenshot of "show warnings". Basically, the columns do not line up correctly. The first row is user_answer_id. I need the LOAD DATA INFILE to skip over that.
- 07/21/10 2:47pm
Lawrence Krubner says:I tried this:
SELECT '',user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_answer
WHERE user_answer_id > 857461
;
Which produces output like this:
"","14502501085373900",617,117,100,"B","n",9,0
But on import I get errors like this:
| Warning | 1366 | Incorrect integer value: '' for column 'user_answer_id' at row 58 |
- 07/21/10 2:52pm
Lawrence Krubner says:On one of the other tables, some of the other SELECT INTO OUTFILE worked without a hitch. For instance:
SELECT user_id, course_id, enrollment_date,status,test_status,completion_date,exam_score,prof_order,expiration_date
INTO OUTFILE '/tmp/final_import_user_course.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_course;
But I think this only worked because of the coincidence that the "id" field is ordered so as to come last:
user_id
course_id
enrollment_date
status
test_status
completion_date
exam_score
prof_order
expiration_date
id
But when the id comes first, as user_answer_id does with final_import_user_answer, how can I get the LOAD DATA INFILE to skip past it?
- 07/21/10 5:44pm
William Clark says:Try using these along with my original select into queries:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_class.csv'
INTO TABLE final_import_user_class_new
(user_id, class_id, module_id, content_id,status,quiz_score,quiz_date_completed,course_id)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE '/tmp/final_import_user_course.csv'
INTO TABLE final_import_user_course_new
)user_id, course_id, enrollment_date,status,test_status,completion_date,exam_score,prof_order,expiration_date)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
(user_answer_id, user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
LOAD DATA LOCAL INFILE '/tmp/final_import_course_evaluation.csv'
INTO TABLE final_import_course_evaluation_new
(professor_interaction_com,course_evaluation,course_evaluation_com,spend_more_time_on,improve_site_by,like_course_about,anthing_elsee,user_id, course_name,date_created,course_organization,course_organization_com,materials_relevance,materials_relevance_com,length_of_modules,length_of_modules_com,text_clarity,text_clarity_com,quality_of_pictures,quality_of_pictures_com,quality_of_videos,quality_of_videos_comvalue_of_ref_material,value_of_ref_material_com,value_of_testings,value_of_testings_com,quizzes_and_exam,quizzes_and_exam_com,ease_of_purchase,ease_of_purchase_com,website_usability,website_usability_com,professor_interaction)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
- 07/21/10 5:56pm
William Clark says:Whoops! Try it this way:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_class.csv'
INTO TABLE final_import_user_class_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_id, class_id, module_id, content_id,status,quiz_score,quiz_date_completed,course_id);
LOAD DATA LOCAL INFILE '/tmp/final_import_user_course.csv'
INTO TABLE final_import_user_course_new
(user_id, course_id, enrollment_date,status,test_status,completion_date,exam_score,prof_order,expiration_date)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_id, course_id, enrollment_date,status,test_status,completion_date,exam_score,prof_order,expiration_date);
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_answer_id, user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order);
LOAD DATA LOCAL INFILE '/tmp/final_import_course_evaluation.csv'
INTO TABLE final_import_course_evaluation_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (professor_interaction_com,course_evaluation,course_evaluation_com,spend_more_time_on,improve_site_by,like_course_about,anthing_elsee,user_id, course_name,date_created,course_organization,course_organization_com,materials_relevance,materials_relevance_com,length_of_modules,length_of_modules_com,text_clarity,text_clarity_com,quality_of_pictures,quality_of_pictures_com,quality_of_videos,quality_of_videos_comvalue_of_ref_material,value_of_ref_material_com,value_of_testings,value_of_testings_com,quizzes_and_exam,quizzes_and_exam_com,ease_of_purchase,ease_of_purchase_com,website_usability,website_usability_com,professor_interaction); - 07/21/10 6:07pm
William Clark says:Another whoops. I did not exclude the autoincrement field for this one:
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_answer;
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order); - 07/21/10 6:16pm
William Clark says:Also, if you have characters that need to be escaped, then add ESCAPED BY like this:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_course.csv'
INTO TABLE final_import_user_course_new
(user_id, course_id, enrollment_date,status,test_status,completion_date,exam_score,prof_order,expiration_date)
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\' (user_id, course_id, enrollment_date,status,test_status,completion_date,exam_score,prof_order,expiration_date);
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\'
FROM final_import_user_answer;
- 07/21/10 6:19pm
Lawrence Krubner says:No, I already tried that. I wrote about that up above. If I try this:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE user_answer
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_answer_id, user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order);
Then I get:
Query OK, 0 rows affected, 11250 warnings (0.01 sec)
Records: 3670 Deleted: 0 Skipped: 3670 Warnings: 6455
If I type "show warnings" then I get stuff like:
+---------+------+-------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'user_answer_id' at row 1 |
| Warning | 1366 | Incorrect integer value: 'D' for column 'course_id' at row 1 |
| Warning | 1261 | Row 1 doesn't contain data for all columns |
| Warning | 1264 | Out of range value for column 'user_answer_id' at row 2 |
| Warning | 1366 | Incorrect integer value: 'B' for column 'course_id' at row 2 |
| Warning | 1261 | Row 2 doesn't contain data for all columns |
| Warning | 1264 | Out of range value for column 'user_answer_id' at row 3 |
| Warning | 1366 | Incorrect integer value: 'C' for column 'course_id' at row 3 |
| Warning | 1261 | Row 3 doesn't contain data for all columns |
| Warning | 1264 | Out of range value for column 'user_answer_id' at row 4 |
| Warning | 1366 | Incorrect integer value: 'C' for column 'course_id' at row 4 |
| Warning | 1261 | Row 4 doesn't contain data for all columns |
| Warning | 1264 | Out of range value for column 'user_answer_id' at row 5 |
| Warning | 1366 | Incorrect integer value: 'C' for column 'course_id' at row 5 |
- 07/21/10 6:20pm
Lawrence Krubner says:OK, I'll try that.
- 07/21/10 6:27pm
Lawrence Krubner says:k, I do this:
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_answer
WHERE user_answer_id > 857461;
and then this:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE user_answer
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order);
I get:
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 3670 Deleted: 0 Skipped: 3670 Warnings: 1
If I type "show warnings" I get:
Warning | 1264 | Out of range value for column 'user_answer_id' at row 1
This is the problem I complained of up above. Its as if it is not seeing the list of table names.
I'll attach a screenshot just in case seeing my command line inspires some thoughts.
- 07/21/10 6:29pm
William Clark says:OK Lets just concentrate on the final_import_user_answer table
Are you sure that the new table has user_answer_id as an auto-increment field?
It shouldn't be trying to insert data into that field.
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\'
FROM final_import_user_answer;
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order);
I'd be interested in any errors this may show. - 07/21/10 6:29pm
Lawrence Krubner says:There are no characters that I need to escape, at least, none that I know of. I suppose a comma in the material itself would wreck the import? But I do not believe there are any commas in this material.
- 07/21/10 6:36pm
William Clark says:the error Out of range value for column 'user_answer_id' at row 1 suggested that it is trying to load data to that field. Make sure that the new table has that field created as NOT NULL AUTO_INCREMENT PRIMARY KEY.
- 07/21/10 6:42pm
William Clark says:Another thing to try. Move the csv file to the machine with the new table.
Then take out the LOCAL keyword
ie:
LOAD DATA INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order);
- 07/21/10 6:52pm
Lawrence Krubner says:This is the table I'm inserting into:
mysql> explain user_answer;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| user_answer_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | varchar(100) | YES | | NULL | |
| question_id | int(11) | YES | MUL | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
| course_id | int(11) | YES | MUL | NULL | |
| answer | varchar(1) | YES | | NULL | |
| correct | varchar(1) | YES | | NULL | |
| quiz_order | int(11) | YES | | NULL | |
| exam_order | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
- 07/21/10 6:57pm
Lawrence Krubner says:Maddening. According to what I've read, this problem should not exist.
- 07/21/10 6:59pm
William Clark says:Did you try taking out the LOCAL keyword?
Check your rights to that database. Do you have FILE access?
Does the current user have write access to the directory that contains the mySQL table? - 07/21/10 7:08pm
William Clark says:Hi Lawrence,
Try this:
LOAD DATA LOCAL INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order) set user_answer_id=null; - 07/21/10 7:13pm
William Clark says:I left in the local keyword by mistake. To be thorough, here is the current suggested method:
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\'
FROM final_import_user_answer;
LOAD DATA INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE final_import_user_answer_new
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' ESCAPED BY '\\' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order) set user_answer_id=null; - 07/21/10 7:59pm
William Clark says:Lawrence: Did you see the new bit? ie: set user_answer_id=null at the end of the LOAD DATA command?
- 07/21/10 8:10pm
Lawrence Krubner says:William, sorry to say, I just left work. I'm unable to test your suggestions till tomorrow.
Regarding your suggestions about the File permissions and such, I do not think those are the problems. The code for the other tables seems to work. I could be wrong about this, but I think the problem I'm having is somewhat unique to the table final_import_user_answer. - 07/21/10 8:11pm
Lawrence Krubner says:I mean, if the problem is one of permissions, that none of the LOAD DATA INFILE statements would work. But the ones for final_import_user_class does seem to work.
- 07/21/10 8:13pm
Lawrence Krubner says:Actually, I'm now testing using 2 tables in the same database (same server), exporting from one and importing into the other. The same problem persists. I assume permissions can not be an issue in this situation.
- 07/21/10 8:17pm
William Clark says:I look forward to hearing how the addition of "set user_answer_id=null" works. Have a good night!
- 07/22/10 10:22am
Lawrence Krubner says:I get:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ESCAPED BY '\\'
- 07/22/10 10:26am
Lawrence Krubner says: - 07/22/10 10:29am
William Clark says:Try the ESCAPED BY with only one char. ie: ESCAPED BY '\'
- 07/22/10 10:31am
Lawrence Krubner says:If I do this:
SELECT user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order
INTO OUTFILE '/tmp/final_import_user_answer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM final_import_user_answer
WHERE user_answer_id > 857461
;
LOAD DATA INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE user_answer
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order) set user_answer_id=null;
then i get:
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
Somehow we need to indicate that it should not be mapping the input to the user_answer_id field.
- 07/22/10 10:40am
Lawrence Krubner says:I can import the OUTFILE to final_import_user_answer, from where it just came, but I can not import it into the user_answer table, the one that is in the same database, on the same server. This makes me think there is some slight difference between these 2 tables. Yet, I can not see it:
mysql> explain final_import_user_answer;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| user_answer_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | varchar(100) | YES | | NULL | |
| question_id | int(11) | YES | MUL | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
| course_id | int(11) | YES | MUL | NULL | |
| answer | varchar(1) | YES | | NULL | |
| correct | varchar(1) | YES | | NULL | |
| quiz_order | int(11) | YES | | NULL | |
| exam_order | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
mysql> explain user_answer;
+----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+----------------+
| user_answer_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | varchar(100) | YES | | NULL | |
| question_id | int(11) | YES | MUL | NULL | |
| class_id | int(11) | YES | MUL | NULL | |
| course_id | int(11) | YES | MUL | NULL | |
| answer | varchar(1) | YES | | NULL | |
| correct | varchar(1) | YES | | NULL | |
| quiz_order | int(11) | YES | | NULL | |
| exam_order | int(11) | YES | | NULL | |
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)
- 07/22/10 10:43am
William Clark says:I think that is closer, it actually imported record this time, didn't it?
- 07/22/10 10:48am
William Clark says:The duplicate key entry happens when you reach the upper limit of the auto increment field, in your case, being a signed int the upper boundary is 2147483647.
Once the last record is with the id 2147483647, when you insert a new record the id should be 2147483648. But mysql reduce it inside the valid range, and tries to insert the id 2147483647, therefore it produces a duplicate key entry.
DROP the new database and re-create it. They re-run the LOAD DATA statement. - 07/22/10 10:54am
Lawrence Krubner says:William, I am sorry I didn't explain this. The 2147483647 is actually the user_id, which we store in a varchar(128). The problem here is the same one we've been dealing with all along: the user_id is getting pushed into the primary key field, that is, the user_answer_id field.
The actual highest primary key is 864901, which you can see here:
mysql> select user_answer_id from final_import_user_answer order by user_answer_id desc limit 1;
+----------------+
| user_answer_id |
+----------------+
| 864901 |
+----------------+
1 row in set (0.01 sec)
Of course, we do not want this at all. - 07/22/10 10:58am
Lawrence Krubner says:One user_id made it into the table:
mysql> select user_answer_id from user_answer where user_answer_id > 950000 ;
+----------------+
| user_answer_id |
+----------------+
| 2147483647 |
+----------------+
1 row in set (0.01 sec)
I deleted this record since it was obviously a mistake. - 07/22/10 11:01am
William Clark says:No Lawrence, I disagree.
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY' is talking of the Primary key field, which is user_answer_id.
Try dropping the database and re-creating it, then do the LOAD DATA. - 07/22/10 11:20am
Lawrence Krubner says:Now I am trying to use the SET syntax, but I am not getting it right.
I tried this:
LOAD DATA INFILE '/tmp/final_import_user_answer.csv'
-> INTO TABLE user_answer
-> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '\n'
-> (@user_id, @question_id, @class_id, @course_id, @answer, @correct, @quiz_order, @exam_order)
-> SET user_id = @user_id
-> SET question_id = @question_id
-> SET class_id = @class_id
-> SET course_id = @course_id
-> SET answer = @answer
-> SET correct = @correct
-> SET quiz_order = @quiz_order
-> SET exam_order = @exam_order
-> ;
but I got:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET question_id = @question_id
SET class_id = @class_id
SET course_id = @course_' at line 7
- 07/22/10 11:22am
William Clark says:I don't think you need the set syntax.
As I said:
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY' is talking of the Primary key field, which is user_answer_id.
Try dropping the database and re-creating it, then do the LOAD DATA. - 07/22/10 11:25am
Lawrence Krubner says:William, this is the first line of data to be imported:
"14502501085373900",528,100,100,"D","n",1,0
14502501085373900 gets put into user_answer_id and, as you said, converted to 2147483647.
This is the second line of data that needs to be imported:
"14502501085373900",527,100,100,"B","n",2,0
Again, MySql tries to put the 14502501085373900 into user_answer_id, and again it gets converted to 2147483647, and thus we get the "duplicate key" error that I quoted up above.
However, 14502501085373900 is the user_id. It does not belong in the user_answer_id field.
- 07/22/10 11:29am
William Clark says:hmmm, and this is after dropping the db and re-creating it?
Then doing the LOAD DATA with "set user_answer_id=null" at the end?
Can you show me the errors? - 07/22/10 11:44am
Lawrence Krubner says:Oh, sorry. I am stupid. I get your point about user_answer_id now. The auto-increment for this table is exhausted now, regardless of whether our other statements work correctly or not. So I need to re-create the table. Right.
- 07/22/10 11:55am
Lawrence Krubner says:Okay, to rebuild this table, with an auto-increment field that is not exhausted.
First I saved a copy of the table:
mysqldump --user=xxx --password=xxx cms --tables user_answer > /home/dev/answer_export_2010-07-22.sql
Then I dropped the table:
drop table user_answer;
Then I re-imported the table:
source /home/dev/answer_export_2010-07-22.sql
And now I've got about what I expect:
mysql> select count(*) from user_answer;
+----------+
| count(*) |
+----------+
| 819392 |
+----------+
1 row in set (0.00 sec)
mysql> select user_answer_id from user_answer order by user_answer_id desc limit 1 ;
+----------------+
| user_answer_id |
+----------------+
| 861231 |
+----------------+
1 row in set (0.00 sec)
- 07/22/10 12:01pm
Lawrence Krubner says:k, now I do this:
LOAD DATA INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE user_answer
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_id, question_id,class_id,course_id,answer,correct,quiz_order,exam_order) set user_answer_id=null;
and I get:
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
- 07/22/10 12:15pm
William Clark says:Doh!! I thought that would work.
I see that you are doing the LOAD DATA on top of 819K of existing rows. That shouldn't be a problem. What does show errors output?
Try again, re-create the table, but do the LOAD DATA first, then import the saved 819K of rows.
I would be interested in the show errors output and any initial error. - 07/22/10 12:17pm
Lawrence Krubner says:So, I drop the table again and re-create it, again, via the mysqldump I just made. Then I try:
LOAD DATA INFILE '/tmp/final_import_user_answer.csv'
INTO TABLE user_answer
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' (user_id, question_id, class_id, course_id, answer, correct, quiz_order, exam_order);
and I get:
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'
Bizarre that it keeps putting the userid in the user_answer_id field. Frustrating, too, that I now need to drop the table and re-create it for every experiment.
- 07/22/10 12:19pm
Lawrence Krubner says:That is an interesting idea. I'll try it.
- 07/22/10 12:23pm
Lawrence Krubner says:Okay, so I:
mysql> drop table user_answer;
then I recreate the table:
mysql> CREATE TABLE `user_answer` (
-> `user_answer_id` int(11) NOT NULL AUTO_INCREMENT,
-> `user_id` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
-> `question_id` int(11) DEFAULT NULL,
-> `class_id` int(11) DEFAULT NULL,
-> `course_id` int(11) DEFAULT NULL,
-> `answer` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
-> `correct` varchar(1) COLLATE utf8_unicode_ci DEFAULT NULL,
-> `quiz_order` int(11) DEFAULT NULL,
-> `exam_order` int(11) DEFAULT NULL,
-> PRIMARY KEY (`user_answer_id`),
-> KEY `user_answer_FI_1` (`question_id`),
-> KEY `user_answer_FI_2` (`class_id`),
-> KEY `user_answer_FI_3` (`course_id`)
-> ) ENGINE=MyISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
then I run the LOAD INFILE:
mysql> LOAD DATA INFILE '/tmp/final_import_user_answer.csv' INTO TABLE user_answer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (user_id, question_id, class_id, course_id, answer, correct, quiz_order, exam_order);
I get:
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'mysql>
The errors and warnings are about what you would expect:
mysql> show errors;+-------+------+------------------------------------------------+| Level | Code | Message |
+-------+------+------------------------------------------------+
| Error | 1062 | Duplicate entry '2147483647' for key 'PRIMARY' |
+-------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'user_answer_id' at row 1 |
| Warning | 1264 | Out of range value for column 'user_answer_id' at row 2 |
| Error | 1062 | Duplicate entry '2147483647' for key 'PRIMARY' |
+---------+------+---------------------------------------------------------+
3 rows in set (0.00 sec)
- 07/22/10 12:25pm
Lawrence Krubner says:Oh, dumb. I just noticed the auto-increment field is already set to the max. I guess that is the problem with mysqldump.
- 07/22/10 12:30pm
Lawrence Krubner says:Nice! So now this seemed to work no problem:
LOAD DATA INFILE '/tmp/final_import_user_answer.csv' INTO TABLE user_answer FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' (user_id, question_id, class_id, course_id, answer, correct, quiz_order, exam_order);
So, the problem was that the auto-increment field was exhausted, after some point when the user_id got accidentally inserted into it. And even when I did "drop table" that didn't help, because I was re-creating the table from a mysqldump that was setting the auto-increment field to 2147483648 as the starting point.
Interesting.
- 07/21/10 1:06pm
-
Last edited:
07/21/10
2:13pmJarret Minkler says:Will probably be a lot slower than just using a select in the create table or insert statement?
INSERT INTO final_xxxxxx SELECT (...fields...) FROM origional;- 07/21/10 2:16pm
Lawrence Krubner says:2 different servers, one dev and one production. It would be hassle to network them together just to run 1 query. Easier to move around files.
- 07/21/10 2:30pm
Jarret Minkler says:gotcha :)
- 07/21/10 6:32pm
Lawrence Krubner says:Of course, I thought that hours ago, in all innocence. I didn't realize what a problem it would be to use OUTFILE and INFILE.
- 07/21/10 2:16pm
-
Last edited:
07/23/10
1:33amk v says:Hi Long back I've created an insert generator stored procedure...
http://kedar.nitty-witty.com/blog/mysql-stored-procedure-to-generate-extract-insert-statement/
I though it'd be helpful to you in your case with minor changes. Please find below code:
DELIMITER $$
DROP PROCEDURE IF EXISTS `InsGen` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsGen`(in_db varchar(20),in_table varchar(20),in_file varchar(100),in_ignore varchar(100))
BEGIN
declare Whrs varchar(500);
declare Sels varchar(500);
declare AllCols varchar(500);
declare Inserts varchar(2000);
declare tablename varchar(20);
declare tmp varchar(20);
set tablename=in_table;
select group_concat(concat('concat(\'"\',','ifnull(',column_name,','''')',',\'"\')')) INTO @Sels from information_schema.columns where table_schema=in_db and table_name=tablename and column_name<>in_ignore;
select group_concat('`',column_name,'`') INTO @Whrs from information_schema.columns where table_schema=in_db and table_name=tablename and column_name not in (in_ignore);
select group_concat('`',column_name,'`') INTO @AllCols from information_schema.columns where table_schema=in_db and table_name=tablename;
set @Inserts=concat("select concat('insert into ", in_db,".",tablename,"(",@Whrs, ") values(',concat_ws(',',",@Sels,"),');') from ", in_db,".",tablename," group by ",@AllCols, " INTO OUTFILE '", in_file ,"'");
#select @Inserts;
PREPARE Inserts FROM @Inserts;
EXECUTE Inserts;
END $$
DELIMITER ;
Create above stored proc and execute as:
call InsGEN('DB-NAME','TABLE-NAME','/tmp/output-filename.sql','id');
Considering ID as auto-increment field just redirecting above code will work for you.
Also Refer:
mysql-load-data-infile-syntax-generator-tool
http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server/
Update:
Did you try this?Previous versions of this answer: 07/23/10 at 1:33am
This question has expired.
Current status of this question: Completed
