Get your CodeRanch badge!*
The moose likes JDBC and the fly likes Error while transferring data from Excel to MS Access Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error while transferring data from Excel to MS Access" Watch "Error while transferring data from Excel to MS Access" New topic
Author

Error while transferring data from Excel to MS Access

Paras Ahuja
Ranch Hand

Joined: May 22, 2012
Posts: 62
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

Joined: Apr 30, 2003
Posts: 820

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

Joined: May 22, 2012
Posts: 62


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

Joined: Aug 22, 2010
Posts: 3433
    
  47

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

Joined: Apr 30, 2003
Posts: 820

can you post the data definition for the "Table1" in Access and the row of data that is failing?
Paras Ahuja
Ranch Hand

Joined: May 22, 2012
Posts: 62
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....


[Download snap1.bmp] Download

Paras Ahuja
Ranch Hand

Joined: May 22, 2012
Posts: 62
Here's my Table1 pic (after trying to transfer data)


[Download snap2.bmp] Download

Rob Spoor
Sheriff

Joined: Oct 27, 2005
Posts: 19541
    
  16

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).


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Paras Ahuja
Ranch Hand

Joined: May 22, 2012
Posts: 62
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

Joined: Apr 30, 2003
Posts: 820

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error while transferring data from Excel to MS Access
 
Similar Threads
MSACCESS Connection
JDBC Newbie
Excel driver for Solaris?
Performance of Swing application
Read Excel from JDBC.