Access: what could be causing error importing data into a calculation field?

Factual Questions
1

I have an Access table, originally designed to get populated by manual input from a form. It has some fields that auto-populate without needing the person filling out the form to fill in that information: the fiscal year, the quarter, and some numerical totals that pull from numbers that they entered.

When I first switched over to populating the table by importing from Excel workbooks (appending a copy of the records to the Access table), I didn’t realize those auto-populate fields wouldn’t just “fill themselves in” — since they’d been doing so when the data was entered from the form. But indeed they did not. I updated the existing ones with a SQL update query and then added those columns to the system that generates the Excel files, and most of them — the quarter, the fiscal year, the sum of three different kinds of stock inventory, the calendar year, etc etc — imported and populated those columns in Access. But there are two exceptions and I can’t figure out why!

They are rejected on import:

“Not all of your data was successfully imported. Error descriptions with associated row numbers of bad records can be found in the Microsoft table ‘Sheet1$_ImportErrors’.”

That error table (“Sheet1$_ImportErrors”) lists those two fields (over and over, for each row in the Excel source data), with the Error listed as “Unparsable Record”.

(Which sounds like the record in its entirety is being rejected, but such is not the case; the records land otherwise intact, just missing these two fields)

I figured there had to be some characteristic of these two calculation fields that set them apart from the other calculation fields that are importing without errors. But not that I can find! Both the rejected ones and many of the accepted ones are set to result type “Long integer”, are set up to be the sum of three numerical fields in the same Access table, don’t have any Validation Rules set up.

I’m not an Access head. I’m a database developer but my background is FileMaker, and Access steers very differently.

Anyone got a clue what might be causing Access to spit the data from these fields like this?

2

I would sooner expect the problem is bad data on the Excel end. Such as the numeric fields in that worksheet column don’t actually have numeric numbers in them, but rather are a string of digits as text characters. perhaps with embedded leadng or trailing spaces or formatting characters like “,”. But which all look like ordinary numbers until you dig into the actual value of the cell(s).

An unrelated thought
If the Access fields are doing the calculating rather than the Excel fields, is there a circular dependency there?

Or maybe even it all works as long as the dependent cell is to the right of all the cells it depends on, but not vice versa? I would not expect that to be an issue in modern software as opposed to Access/ Excel v1.0, but stranger things have survived to the modern era in the name of backwards compatibility.

3

Reasonable conjecture, but no. Inside the other system they are populated by a formula and the result of the formula is explicitly set to numerical. Also we tried creating the columns manually and populating them all with 0 (type a 0 top line then fill down), doesn’t like that either.

Beats heck out of me, but that would be the case for the fields that Access is cheerfully importing too, so that seems unlikely to be the culprit.

4

Are these the only fields calculated by a formula? Are the cell contents the formula, or the results of the formula copied with “values only” flagged?

5

Can you export it from Excel as a CSV and import that instead?

What happens if you try to manually copy each individual field from the problematic record(s) into access… do any of them individually give you an error?

6

No, the vast majority of the fields are calculated. Not by Excel but by the system that exports the values as an Excel workbook.

Don’t know, never rummaged around in Access to see if it has a similar mechanism for importing from CSV. I’ll check.

I’ll check that too.

7

I think you need to modify your table and the import to use only the data fields - leave out the calculated fields.

Then, create a query that includes all the table fields as well as the calculated ones. That query is then used for your reports or whatever just like the original table was used.

8

K364 makes sense to me, but it’s not a system I have complete authority in. I’ll suggest it.

Reply, the fields will not let me paste into them, nor will it let me manually key in a numerical value.

I managed to figure out how to import from CSV file, and saved the Excel file in CSV format and imported it. Identical error.

9

So it only happens on these certain calculated fields but no other apparently similar calculated fields in the same table? Does it happen on the trouble fields on every record regardless of data value, or only certain records?

10

Every single record. And it’s importing extremely similar calculated fields without complaint.

Weirdly, there’s also a field that it complains about and puts into the Error table it generates, but that field is nevertheless importing just fine. Go figure.

11

That begins to suggest some sort of damage in the Access schema. I am far from an Access maven.

  • Is there a mechanism to export the Access table schema in a human-readable format? If so does that produce a valid result?
  • If you read the schema to compare the definitions of the unsuccessful fields and the similar successful fields does anything stand out?
  • If you try to create a second generation sister table from that exported schema, does it exhibit the same importing faults as the original? etc.

I’m grasping for queertrons here, since this sure does not sound like behavior-as-designed.

I’d even be considering deleting the fail-to-import column(s) then re-creating them afresh using the field builder UI.

12

I’d be happy to take a look if you want to DM me the files in question (with any secrets stripped out). Understand if you can’t, though.

13

This sounds like a useful experiment and I’m inclined to try it tomorrow. I’ll report back.

Thanks for the offer, Reply. Let me check with my supervisor. Seems like I might be allowed to ship you an empty clone and a sample Excel file for import testing.

14

I figured it out. Seems kind of obvious now.

  1. Some time around 2019, the database was changed — several questions were removed altogether from the form, and others had their verbal prompts modified.

  2. For unknown reasons, whoever was doing database design created entirely new fields for all the stock and inventory questions and that included the various totals fields. What I was missing was that the only calculation field that’s part of the modern form is the one that appeared to be receiving data, the rest of the calculation fields all belong to the obsolete world of the old form – hence they weren’t giving me errors because they didn’t exist in the Excel document.

  3. So that one calculation field was actually showing up with data in it after import, but Access was still complaining about that field as “unparseable records” anyway.

  4. So the penny finally dropped: it isn’t importing into that column, the calc field is working correctly and calculating the value inside of Access! I removed the column of the calculated field from the excel sheet, tested an import, and yes it is populating with the correct answer and I no longer get the error message.

15

Yaay!

But that is a dogtastically craptacular error message. So misleading versus the reality of the situation.