Excel to Access - import problem

  VNAM75 21:29 17 Aug 2006

I have a list of codes in an excel table, which are either 5 digits or a letter followed by 4 digits, eg. 25879 or S6587. When I import the table into access the codes that have letter prefixes appear as blank cells.

I have tried the following:

1) Formatting the excel codes as text rather than general or number then importing from access
2) Changing the field property in the access table to text after importing the excel table
3) Creating a blank access table and formatting the field property to text then import the excel table. This doesn't work ie. access doesn't allow it.
4) Trying to specify/set the field property as text during the import stage - there's an option to do this but access doesn't allow me to select it.

I dont want to link the table to access, not that would solve the problem.

  beynac 23:35 17 Aug 2006

I just tried it using your first option in Office 2003. I formatted the cells in Excel as 'Text'. I then created a new database and imported the Excel worksheet (File->Get External Data->Import). The only change I made was to untick 'First row contains column headings'. It worked! What version of Office do you have?

  beynac 23:37 17 Aug 2006

Have a look at Tools->Options->Tables/Queries.Make sure that the 'Default field type' is 'Text'.

  VNAM75 00:05 18 Aug 2006

Thanks beynac, just tried what you did and its fine - even when I select 'First row contains column headings'

I think (hope) I know what the problem is. Excel sometimes doesn't like data from certain sources. The codes are actually copied from our company's finance database. I had a similar problem with some sage data before.

I solved it by: if column A has the codes copied from the database, in column B2 put in formula =A2, then copy down to the last cell. Then highlight column B and do copy, paste special, values. Delete column A. It worked with the sage data, I hope it does with this.

Will try tommorow and provide update.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

The art of 'British' pulp fiction

Best password managers for Mac

TV & streaming : comment regarder le Tournoi des Six Nations 2018 ?