Solved

Access to MySQL

Posted on 2011-09-10
7
231 Views
Last Modified: 2012-06-27
This is the error I get when I try to import:

Error

SQL query:

1,"2.1504 SCR","1,000 Airplanes On the Roof","Hwang, David Henry",,8/26/2006 0:00:00 2,"1.148","1776","Stone, Peter",,8/26/2006 0:00:00 3,"2.1005 SCR","1776","Stone, Peter",,8/26/2006 0:00:00 4,"2.800 SCR","1776","Stone, Peter and Sherman Edwards",,8/26/2006 0:00:00 5,"2.1139","1918","Foote, Horton",,8/26/2006 0:00:00 6,"1.216 ANT","20 Non-Royalty One-Act Classics","Mayorgs, Margaret Editor",,8/26/2006 0:00:00 7,"2.1277","4-H Club","Shepard, Sam",,8/26/2006 0:00:00 9,"2.1116 SCR","A... My Name is Alice","Silver, Joan Micklin and Julianne Boyd",,8/26/2006 0:00:00 10,"1.164","A Summer Ghost","Fredericks, Claude",,8/26/2006 0:00:00 11,"1.63","Abdication, The","Wolf, Ruth",,8/26/2006 0:00:00 12,"2.624 SCR","Abe Lincoln in Illinois","Sherwood, Robert E.",,8/26/2006 0:00:00 13,"1.27","Abend di Anni Nouveau","Lardner, R.",,8/26/2006 0:00:00 14,"1.48","Abraham and Isaac","Anon.",,8/26/2006 0:00:00 16,"2.963","Absent Friends","Ayckbourn, Alan",,8/26/2006 0:00:00 17,"1.138","Abse[...]

MySQL said: Documentation
#1064 - 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 '1,"2.1504 SCR","1,000 Airplanes On the Roof","Hwang, David Henry",,8/26/2006 0:0' at line 1

In my table, the first column is "BookID" it is INT

The other error I have gotten is the date format. I formatted the date in Access before I exported to: "yyyy\mm\dd" but when the export happens the zero is gone from the month.

I appreciate any suggestions.
0
Comment
Question by:BuddyKing60626
  • 3
  • 3
7 Comments
 
LVL 13

Expert Comment

by:khairil
ID: 36517712
Hi,

What is your command to import that csv into mysql, or are you using tools to do that?
0
 

Author Comment

by:BuddyKing60626
ID: 36517776
I am using PHP MyAdmin.  
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36517789
Can you show a screen shot of the options you picked in the PHPMyAdmin CSV (LOAD DATAFILE) interface?
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36517802
Try these:

Choose "CSV using LOAD DATA"

Set ',' for fields, '\n' for new lines and the other options blank. Don't have PHPMyAdmin in front of me, though, so screen shot will help.
0
 

Author Comment

by:BuddyKing60626
ID: 36517811
Import screen from PHP MyAdmin
This is the screen.
0
 

Author Comment

by:BuddyKing60626
ID: 36517815
I didn't get the usual error on that "import", I got this one:
Error

SQL query:

REPLACE INTO `lonergan` ( `BookID` , `Call` , `Title` , `Author` , `Subject` , `DateEntered` )
VALUES (
'1', '2.1504 SCR', '1,000 Airplanes On the Roof', 'Hwang, David Henry', '', '8/26/2006 0:00:00'
)

MySQL said: Documentation
#1292 - Incorrect date value: '8/26/2006 0:00:00' for column 'DateEntered' at row 1
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36517833
I would send the date as '2006-08-26 00:00:00' to avoid conversion issues since you have no control over the generated SQL to add STR_TO_DATE().
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 39
SQL Group on First occurrence 9 24
Binding recordsets to a form 6 22
Opening forms with an input box 5 17
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question