Solved

Access to MySQL

Posted on 2011-09-10
7
237 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 60

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

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 60

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
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 …

734 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