Import Excel File into Access - Error Message

Question

Trying to import excel file into access through Wizard appending to an exiting table. Getting message one field does not exist. The field is in the table but the wizard shows the field before it is very stretched out. You have to use the scroll bar to view all the table fields.

This is a new issue. Have been using the same database for years and have never had this issue.

Thank you

Answer 1

Can you show us a screen shot of the Excel file around these few columns. From what i can see, I would suspect and entry in the Credit Union column may have been filled with a lot f spaces, stretching out the column.

What you might try is linking to the spreadsheet (instead of importing) Then run the following query:

SELECT [Credit Union], Len([Credit Union]) AS ColWidth

FROM tble

ORDER by ColWidth Desc;

This can show you if any value in the col is filled with spaces.

Also, as a point of practice, I rarely directly import data. Instead I will link to the import file Then run an APPEND query to add the data to the table. In this way you can make adjustments as needed, For example you can add the Credit Union column with the expression:

ImpCU: Trim([Credit Union])

So even if thyere is extra spaces, they are taken care of.

Answer 2

Dear Atlantic86,

Thanks for providing the screenshots for our confirmation.

If it is convenient, could you provide the affected file here for us to do a test in our environment to see the result?

If the file link and the file can be shared here directly, please add your confirmation as this forum is public.

If no, welcome to let us know at your convenience and I'll take the next action to collect the affected file.

Meanwhile, if it is convenient, I'd like you to upload a full screenshot of the Account page in the Access application you are using for our reference. You can access the page via clicking on File>Account.

Note: Remove any private information before uploading the screenshots.

Thanks for your effort and time.

Sincerely,

Cliff | Microsoft Community Moderator

Answer 3

Thank you for your reply. There has been no change to the data type from previous use. We are very careful about the Excel doc to ensure that it is consistent format and headings.

Regarding the stretched field, its during the wizard process. Hard to explain but heres some pics. Normally we see all fields at this point in the wizard process, however now the credit union field is very stretched (expanded) and I get an error message saying the Member Number does not exist in table i am appending to. It does exist however.

Clear as mud??

Answer 4

Two thoughts come to mind.

1. When a process works for some time, then fails, the first thing to verify is that the data in the source is consistent with your expectations. If you have always received date values in a specific field, for example, does any of the data in that field in the failing Excel file still contain only valid dates? Or when you are expecting numbers, are there any text values, and so on?
2. Did someone change the format and layout of the source Excel file without letting you know? The error seems to suggest that is a possibility.

One backfill question. What does it mean to say "...the field before it is very stretched out)"? What do you mean by "very stretched out"?

Thanks.