Solved

Access to MySQL

Posted on 2011-09-10
7
235 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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