Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error while transferring data from Excel to MS Access

 
Paras Ahuja
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi everyone,
I am trying to copy records from excel to ms-access using JDBC-ODBC Driver. However, this error keeps on coming:
java.sql.SQLException: [Microsoft][ODBC Excel Driver] You cannot record your changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum). Correct the error and try again.

Let me tell you when i run the same program when there are few records in the table,it runs perfectly without generating error. But my actual excel sheet has around 7000 records and when i try to transfer that whole data,it gives this irritating error which i can't even understand. I am using both MS Access and Excel 2010.
Please help.....

Thanks in advance...
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paras Ahuja wrote:Hi everyone,
I am trying to copy records from excel to ms-access using JDBC-ODBC Driver. However, this error keeps on coming:
java.sql.SQLException: [Microsoft][ODBC Excel Driver] You cannot record your changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum). Correct the error and try again.

Let me tell you when i run the same program when there are few records in the table,it runs perfectly without generating error. But my actual excel sheet has around 7000 records and when i try to transfer that whole data,it gives this irritating error which i can't even understand. I am using both MS Access and Excel 2010.
Please help.....

Thanks in advance...


I'm going to guess that one of the records in the 7000 rows violates a constraint you have in your Database (unique, not null, max chars, etc.) the run with just a few records does not contain a record with this violation. You will have to identify the record it is choking on and compare that to the data that works.

You could also post your code.

I would expect a JDBC driver to report the problem SQL statement, but the JDBC-ODBC bridge is not like other drivers. Note that the documentation says:

The JDBC-ODBC Bridge driver is recommended only for experimental use or when no other alternative is available.
 
Paras Ahuja
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


No Tim, I opened my MS Access table, saw which cell was not getting copied from excel ( and generating the error). And when i tried to move only that record (containing that particular cell),it gets copied perfectly.
Also, could you suggest me what to use instead of JDBC-ODBC?

Thanks...
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Tim is most probably right. The error message you posted explicitly says that one of your records violated database constraints.

You could add logging to your method and print values of every record you're trying to insert to the console first. That way you'll know which record is responsible for the error. Another way would be to closely inspect the definition of the target table in MS Access database for constraints and check (using filters in excel, for example) for records which are not compliant with the constraints. The wording of the message is so vague that it could even mean the not-null or foreign key constraint is violated.
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
can you post the data definition for the "Table1" in Access and the row of data that is failing?
 
Paras Ahuja
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Tim,
I have attached snapshots of part of my excel sheet and Table1 (after trying to transfer data).

As you can see through my code,
1. I first insert into Table1 all the values of 1st column of excel sheet.
2. Then i update Table1 by adding all the remaining columns of excel sheet.

My 1st column in excel is ReferenceNo (which you can see in the excel sheet i've attached). The values of these columns are inserted into Table1 properly.
Then when OMS_OrderNo (my 2nd column) is added to Table, the vaue with red background (in my excel sheet) is not coped and generates the error.
Please see to it.....
Thanks....
Filename: snap1.bmp
Description:
File size: 713 Kbytes
[Download snap1.bmp] Download Attachment
 
Paras Ahuja
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Here's my Table1 pic (after trying to transfer data)
Filename: snap2.bmp
Description:
File size: 713 Kbytes
[Download snap2.bmp] Download Attachment
 
Rob Spoor
Sheriff
Pie
Posts: 20555
57
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think that OMS_Order is a numeric field. However, your Excel files contains non-numeric values like "NA", "1-141972991" and "". You will either need to change the column to contain text instead of numbers, or convert these invalid values to something else (perhaps NULL).
 
Paras Ahuja
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rob,
But when i copied these records containing values like "NA", "1-141972991" and "" in OMS_OrderNo into another sheet and then try to transfer,it transfers perfectly.
Does it has to do smthng with the size of data i'm transferring.
 
Tim McGuire
Ranch Hand
Posts: 820
IntelliJ IDE Tomcat Server VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paras Ahuja wrote:Hi Rob,
But when i copied these records containing values like "NA", "1-141972991" and "" in OMS_OrderNo into another sheet and then try to transfer,it transfers perfectly.
Does it has to do smthng with the size of data i'm transferring.


You can't trust what you cut and paste into a field to be the same as a JDBC SQL insert command because Access and especially Excel have "helpful" features of text and number formatting and will try to anticipate your wishes. For me, this is often unhelpful. When you transfer as you say to another Excel sheet and then complete the transfer via JDBC successfully, I imagine that the first excel sheet is formatted differently than the one you paste into. (Your first one is formatted as a number field and the other one formatted as text). I can tell you that an excel numeric field will happily take "NA" . When I copy that row into another sheet, Excel will, helpfully or not, make that new one text or "General".

If you copy and paste a bunch of rows into a new sheet and the cell with "NA" at the top, then the column with "NA" is made text. If "NA" is present in, but not at the top of, a column of numbers, the column will be made numeric. This is just one example and it may apply. You are going to have to experiment.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic