Still not working- Importing Excel into access

[MCantu]

I tried posting this before, but I have not seen it posted yet, so I will post again.


I am trying to import an excel sheet into access.
I have searched the threads, and found similar problems, but none of the answers are helping.

The problem:
I use a macro to transfer the spreadsheet (Transferspeadsheet)

I have a column in excel that contains mostly numeric entries, but sometimes it will have alphanumeric entries.

Access will see the numeric entries and assume this is a numeric field- and then null my alphanumeric entries, and I will get importerror tables after the fact.

Many times the first 10 rows are completely numeric, but the 20th row is alpha numeric. If the alphanumeric row is closer to the top- no errors.

I have 60 spreadsheets to load every morning. Switching to access for all users would be a godsend, but also a fantasy in my environment. I have to find a work around for this. The users will always be using excel to enter this data.



I have tried:

1) Changing excel columns format to text.

Result: Access doesn't care. Sees the field as numeric- import errors on alphanumeric entries

2.) Linking to table

Result: if the first rows are numeric, when I scroll down to the alphanumeric entry it states "error" when I view the linked table in Access. (Access will not allow me to change the field properties of the linked table)

3.) Making a table in access with the "correct" feilds and exporting to excel. Having the users use that version.

Same result- import errors.



There is one thing I have not tried, and I am ready to try anything:
Somehow exporting excel to text and then importing the text.

HOWEVER: I read this as a solution someone has tried in this forum- but he had problems with date entries. (They didn't convert very well, I am assuming).

I have date entries in my spreadsheets, so I don't want to go there. Besides, I don't know how to automate the export of 60 spreadsheets to text.


I asked someone else, he suggested an "import map".

Did help search, Internet word search- nada.

Is there some sort of import template that can be used to force access to understand that I want a particular field as a text field?

 

[MCantu]

I was seriously considering placing the dummy row 2 in excel with "AAA" and then hiding it.

Unfortunately, my users tend to be skittish and confuse easily. They would wonder why row 2 was missing, and then everyone would have to come ask..... If they didn't already make up some odd reason out of the blue, and then it would lead to confusion.

*sigh*

I did consider that before. It's on my "last resort" list.

I did try the import into an existing table. When I tried it at work, I still got the import errors.

I tried it at home with a mock up of what I have at work..... It worked.

So... I have to see what is going on.

Gremlins, I suspect.

Thank you.

 

[MCantu]

Import errors still- any other suggestions?

My mock up at home works.

Back here at the office, the real thing still gets import errors with a temp table.

I can't see what is different.

(Although I only attempted it with 1 column at home, and I have 3 columns that are giving me import errors)

I have a table with the correct fields that I empty out and re-fill with the contents of the spreadsheet.

I still have import errors.

I may be forced to use the false row of alpha characters in the excel sheet to get this done...

I feel as if there is a more elegant way to do this......
You would think there would have to be.....


Any other suggestions?

 

[vangogh228]

Train your users

I've had this exact problem. Either use the dummy record procedure or sort the spreadsheet so that an alpha entry appears first. Now, to complicate matters, a long numeric value (12 digits, for example) will be changed to Exponential format and end up looking like "1.2E10" or some such nonsense.

Good luck.

 

[reclusivemonkey]

Could you get your users to add the ' character before the number/alpha code in your spreadsheets? This tells Excel to treat the value as a text field. If you link the spreadsheet to Access now it should keep the correct format. I sympathise with your problem, I have a lot of problems with expenditure codes like 6APR2004 in Excel!