Importing from Excel Error

[Preyzar]

Hello,

I've been banging my head on the wall for a while with trying to get the Import Wizard working and searching various forums to see if anyone has experienced this problem without luck. I have double-checked to be sure that each column in the Excel spreadsheet is formatted correctly for the corresponding destination field in Access and that they are in the correct position with the same number of columns. When I copy and paste the data directly into the table it works fine. I've also triple-checked the column headers to be sure there is no formatting and that there are no string differences.

The user of the db I've written will not have permissions into the tables, so of course I want to make sure that he will be able to import without pasting into the tables, but for some reason I keep getting the No Can Do message from the Wizard. The user is being faced with Access for the first time and is fairly resistant to it, so I HAVE to make it as easy as possible for him, but I'm not up to the VBa coding required to create a browse option for importing from a form.

Any suggestions on what I could be missing so I can get the Import Wizard working?

- dates are formatted as medium dates in both Excel and Access
- number fields are formatted as such in both
- text fields are formatted as such in both
- there are no calculated fields

The primary key is an auto-generated number, so I have left the Excel column blank (but with a header) but formatted the cells as numbers.

 

[RainLover]

I think but not 100% sure that your PK Column is causing the problem. I am guessing that you are attempting to write a Null value into the table.

So simply delete that column. It is not needed as Access will look after that field.

 

[gemma-the-husky]

The user of the db I've written will not have permissions into the tables

what do you mean? if he doesnt have permission, he cant add records

 

[Preyzar]

I think but not 100% sure that your PK Column is causing the problem. I am guessing that you are attempting to write a Null value into the table.

So simply delete that column. It is not needed as Access will look after that field.

I did try that - I still get the message.

The Access Help menu just says that I need to ensure that the formats, number of columns, and column headers must match - which they do as far as I can tell.

 

[Preyzar]

what do you mean? if he doesnt have permission, he cant add records

I misspoke... The window that shows the tables, queries, etc. will be hidden so he won't be able to see them. If he really wanted to (and knew how) he could unhide the pane and then have access to them, but that's unlikely, lol.

 

[RainLover]

Can you post a database with just that table and the spreadsheet.

Otherwise I would just be guessing.

 

[Preyzar]

Can you post a database with just that table and the spreadsheet.

Otherwise I would just be guessing.

Any insight you could offer would be greatly appreciated!

 

[gemma-the-husky]

are you using docmd.transfer spreadsheet, or doing it by hand

do it by hand - if all the rows cannot be added (eg data validation errors) you should get some advisory message

sully question, but are you sure you arent creating a new table, rather than importing to an existing one

 

[David Eagar]

Just a thought - delete all trailing columns and rows from your spreadsheet. i have been caught before by trying to load 'extra' columns where Access thinks there is some data

 

[RainLover]

Import into a new table. Call it test.

Then compare the Data types etc between your existing table and the Test table.

You will see a lot of differences.

Fix your table and try again.

If this is not successful.

Get rid of input masks and formating.

If it still does not work try importing one Column at a time in order to locate the offending Column.

 

[Preyzar]

are you using docmd.transfer spreadsheet, or doing it by hand

When you say by hand, I'm assuming you mean copying and pasting directly into the tables?

do it by hand - if all the rows cannot be added (eg data validation errors) you should get some advisory message

If you do mean by the copy/paste method, I'm not getting any errors which is what's leading to my frustrations. It would be easy to identify then which field in Access or column in Excel is the culprit, lol.

sully question, but are you sure you arent creating a new table, rather than importing to an existing one

LOL, it's pretty hard to do that through the wizard. I did that once long ago, but when the Wizard prompted me for a new table name I figured it out.

 

[Preyzar]

Just a thought - delete all trailing columns and rows from your spreadsheet. i have been caught before by trying to load 'extra' columns where Access thinks there is some data

I've been caught with that in the past too, so that was one of the first things I looked at. Ctrl-End in Excel took me to the appropriate last cell in the data I'm trying to upload. Bolded header rows have stalled me before too!

 

[Preyzar]

Import into a new table. Call it test.

Then compare the Data types etc between your existing table and the Test table.

You will see a lot of differences.

Fix your table and try again.

I've done the first part of this and printed off the table documentation to match the properties of the live table up to the test table. I'll give the rest of it a whirl later today and let you know how it goes.

Thanks all for all your suggestions!

 

[Preyzar]

Well, a couple of more hours into it and I haven't been able to get any further in solving the problem. A Google search (with a different string) brought me to a suggestion of using the wizard to import to a new table, then using an append query to update the table you want the data to end up in. Apparently this takes out any finicky problems the Import Wizard throws its hands up at.

I'm familiar with macros in Excel, but not sure if they behave the same way in Access. Could I record a macro (and attach it to a command button) that would run through all the steps (after the new table is created) in the above suggestion, or would I need to do the automation in VBa?

 

[David Eagar]

Looking at EXCEL sheet I saw

15-Jan-0915-Jan-09FCCS Deposit / Corr187.1665513.0015-Jan-0915-Jan-09FCCS Deposit / Corr104.16 65513

65513.00 is treated as a number, where next row is 65513, treated as text

also got error message field Bank notes does not exist in table (should be banknotes)

Deleted first column of the EXCEL sheet & imported OK

 

[gemma-the-husky]

no i dont mean copying and pasting

i mean selecting file/import and following the prompts

this will give you warnings about any import errors

--------
after this is working satisfactorily, you can then automate this with simple code

 

[RainLover]

Check the Column names you are using.

They must be the same as the Table.

Also Date is a reserved word. Use myDate or something meaningful.

I got it to work.

 

[Preyzar]

Thanks peeps, after a little more playing I got it to work.