_ImportErrors table generated because Excel value too long

[JeremyI]

I have just about finished adding a module to my database to import information from an Excel spreadsheet into the appropriate table. It successfully creates some temporary tables, performs DoCmd.TransferSpreadsheet, formats and manipulates the records, does some checks against existing data and then writes to the table.

However, I started testing it with some realistic scenarios and it has broken big-time in one puzzling fashion.

The spreadsheet being imported is a list of assessment criteria for a given building, which is filled out by a roving interviewer. One column (D) is devoted to coded short responses (e.g., 'Y' 'N' etc), which is where Access comes in handy, and that's going perfectly now. Another column (F) has interviewer's comments and goes to a memo field, which seems fine.

But the third column that the interviewer is supposed to complete, for freeform responses (E), is only importing successfully under very strange circumstances. I tried entering a long string of nonsense text--initially this was to check how the corresponding form textbox would display it if the interviewer got carried away. I placed the string into two of the freeform cells and their corresponding comment cells. Suddenly errors started being generated on import. Rather than truncate the strings, Access was rejecting them altogether and leaving the values as null. There's no warning so I only noticed when a dozen import error tables had piled up from my tests...

This is the structure of the first temporary table. Importing to it is when the error table is generated:

F1            Long Integer
F2            Text (5)
F3            Text (60)
F4            Text (10)
F5            Text (255)
F6            Memo
AImportIndex  AutoNumber / Primary Key

The content of _ImportErrors is invariably:

Error                      Field    Row
Type Conversion Failure    F5       7
Type Conversion Failure    F5       9

The really weird thing is that this only happens if both of the comment fields are too long. If they are both too long, they both fail. If either of them is less than 255 characters, neither of them fails (the long one is truncated instead).

Changing the field type of [F5] to memo doesn't help, which truly worries me.

I could programmatically truncate the string before moving it into the final table, but since this problem occurs during TransferSpreadsheet, I don't have very fine control.

Any ideas? Is this a problem with my code or with the Excel workbook?

[ADezii]

Have you tried Automation Code to bring the Data into the Table? You should at least see if the problem resides in the TransferSpreadsheet() Method.

[JeremyI]

Hello ADezii,

Thank you. I'm not familiar with automation code, although from the search I just did, I think it may be something that I came across in another thread and have started to incorporate elsewhere in the same project. This sort of thing?:

    'then we add code to open the template file and save it with a name drawn from the form:
 
    'create excel instance
    Dim xlObj As Object
    Set xlObj = CreateObject("excel.application")
    'open the template file
    xlObj.workbooks.Open CurrentProject.path & "\Management Baseline Assessment Template.xls"
    'Start Excel and show
    'xlObj.Visible = True
    'save the template file with another name
    xlObj.activeworkbook.saveas CurrentProject.path & "\" & stTargetFile
    'close and re-open new file to prevent odd object locks
    xlObj.activeworkbook.Close
    xlObj.workbooks.Open CurrentProject.path & "\" & stTargetFile
    'open the temp file we exported from access
    xlObj.workbooks.Open CurrentProject.path & "\temp.xls"
    'select and copy all the data
    xlObj.ActiveSheet.Range("A2:L2").Select
    xlObj.selection.copy

etc...

(A bit of a mess, but this is an unfamiliar enough type of code that I'm still at the rote-copy-and-paste stage for it. Don't want to break anything.)

Of course, the whole thing began with a TransferSpreadsheet export! I'm happy to give autmoation code a try, but am having a bit of trouble finding some proper basics of how to write it. At least the needs of this project truly are pretty basic.

Can you please point out any helpful links you might know of?

[ADezii]

Would it be feasible to Upload the Spreadsheet either here, or to my Personal E-Mail Address should you fell as though that is warranted?

[JeremyI]

Yes, I don't see that there would be a problem with posting it. I've taken out identifying information but otherwise it is the same as the prototype I was testing on Friday.

Do you need to see the relevant tables and forms from the database as well, to give a diagnosis?

Appreciate your help!

[ADezii]

Do you need to see the relevant tables and forms from the database as well, to give a diagnosis?

Not really, let me see if I can Import the Spreadsheet cleanly.

[JeremyI]

OK, posted above. Talk to you tomorrow.

[ADezii]

I had a look at the Spreadsheet, and had no problem successfully importing it without any Errors. The Fields, however, did come over truncated. Will look again when I get the chance.

[JeremyI]

Thanks, ADezii. If you don't mind, could you try this one as well? It is the same thing, but with the longer text in cell E29 (and a correction in E31).

Truncation of the text in column E is the expected--and, indeed, desired--result in this case.

[NeoPa]

Jeremy,

Could you post the data of the record (Row) that causes a failure of the import (Row 7 or 9 from the original data reported in your Original Post would be good).
Otherwise congratulations on a clearly worded question. Not an easy one to explain and you did a great job.

[JeremyI]

That's nice to hear, NeoPa! The long text was as follows:

For this import (simulated 7/3 on 19/3), I'm going to write a really long response in here to make sure that it exceeds the available space. What can I say? It's a lovely day, although windy, and it finally seems that Spring is going to come someday. Looking forward to Dr Who over Easter--must check when the actual time slot is. If they've picked it, knowing that this is the BBC.

I copied this into both the Freeform Answer and Comment cells (imported into the F5 and F6 fields respectively), in both rows (appearing as 7 and 9 in the Access table). Truncated form should be:

For this import (simulated 7/3 on 19/3), I'm going to write a really long response in here to make sure that it exceeds the available space. What can I say? It's a lovely day, although windy, and it finally seems that Spring is going to come someday. Look

However...

To rule out the possibility of corruption in the original import Excel file, I have just tried deleting column E and several columns to the right, then re-formatting columns E and F. I'm afraid that may have been it all along. Fingers crossed, so far it seems to be working correctly even if multiple cells have text that needs to be truncated. I had received the template file from someone else and altered it several times myself, so who knows where an error could have crept in.

But if anything new is discovered that suggests this is a problem with the TransferSpreadsheet method, please do let me know!

Thanks all for looking into it and sorry about the bother.

(Incidentally, at last count, the new Dr Who series is still coming 'later this year'.)

[NeoPa]

@JeremyI
Woohoo !!!

[JeremyI]

:-)

Sadly, the error has now returned in the amended file, and in the latest test it has manifested when only 1 cell was too long. Must give this some more thought...

The import file is generated by overlaying some data from the database onto a template file and saving under a new file name (through automation code, I believe; see above). Could that affect things here at all? None of the problem cells are affected by the paste.

[NeoPa]

To be fair I never saw anything untoward with the data and can only assume, as you seem to have, that it was a corruption related issue. Your resultls are outside of my experience I'm afraid.

[JeremyI]

Haha! Cracked it at last. The answer came to me sometime overnight.

It wasn't really to do with the length of the strings per se, but rather the format Access was reading the Excel spreadsheet column as being in. Some of the expected freeform repsonses were going to be numbers (e.g. a charity number), and when a number was present, for some reason it threw out the whole Access field. I found that when I took out any numbers, the import proceeded fine.

But of course that wasn't acceptable, so I kept digging until I determined that the number format 'General' in Excel was what meant Access didn't know what to do. Despite the table field being specified as Text or Memo, when it hit numbers (possibly of a certain minimum value) and text strings longer than 255 characters, it no longer could process Excel's General number format, and type conversion errors resulted for all non-numbers. I changed that column to Text number format, and suddenly everything worked perfectly.

One drawback is that if the Excel user enters text over 255 characters in that field, it shows up as a string of hashes (####). But I'll turn this into an advantage by explaining in the manual that this is a signal that the length needs to be reduced. That just leaves us with Excel's charming green triangle indicator for 'Number Stored as Text', which also needs a mention in the manual, since now I know that if somebody helpfully 'corrected' it, the import would crash.

Hence the code I added to detect _ImportErrors, remove it and notify the database user stays in.

I don't know all the ins and outs of the precise conditions that trigger it, because they don't especially matter. But I thought I'd go ahead and post what I found out in case anybody else encounters the same sort of frustration.

Thanks again to both of you for having a look and the helpful diagnostics.

[NeoPa]

Nice response Jeremy.

I must admit that I'd rather discounted this problem from earlier info, but it seems that was premature. Excel, as a means of transferring data, does have a number of complications associated that make it a pretty limited choice. This is one of them. It tries to be too clever. If the first number of rows indicate that a column may be treated as numeric (or other specific data type) then it will treat that column that way and fall over any data that doesn't match it's self-determined pattern.

That all said, you seem to have extracted a win from a pretty hopeless situation so I'm really quite impressed. Posting an explanation for travellers that follow in your footsteps is a definite bonus.

[JeremyI]

Much appreciated, NeoPa. Yes, too-cleverness is something we'll always have to suffer with in MS Office, I expect.

For this project, since there are going to be various sorts of users contributing and using data (with wide differences in expertise levels and sometimes no involvement at all with the database itself), it seemed best to provide the common denominator of 'type the answers into the spreadsheet' when it was going out into the field. I didn't expect the import to be easy, but also didn't expect quite so many goofy problems...

Definitely glad that bit is over!