Importing alphanumeric cells from Excel - haven't yet seen a threa

[Access Joe]

I have an Excel worksheet that contains a column with mostly ID numbers in
it, but occassionally has an alphanumeric value as well. When I import it
into Access, the alphanumeric records do not carry over and instead, go to an
'Import Errors' table.

Now, I read a lot of threads on this, but no solution I saw worked. I tried
importing the data into an existing table with a predifined 'Text'
field...also tried formatting the Excel worksheet as Text...also tried
various copy/paste special commands in Excel...and even tried using text
formulas in Excel prior to importing. None of these recommended solutions
worked.

What I can tell you is that this Excel worksheet was imported from an
external proprietary application, and what's interesting is that if I try
doing a simple calculation (i.e. =sum(A1:B1)) in one of its cells, nothing
happens (the formula itself remains visible and does not calculate at all).
I have checked to make sure Formula mode is turned off, and also refreshed
the worksheet. Still the same result.

Any suggestions, as this is really frustrating the heck out of me...

Thanks! Joe

 

[John Nurick]

Hi Joe,

1) did you find this in your search? It gives chapter and verse:
http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

2) IIRC if you format an Excel cell as text and then enter a formula, Excel
treats it as text and not evaluated as a formula. But formatting cells as
text seldom makes any difference to the importing.

3) You don't mention whether you tried prefixing the numeric values with
apostrophes to force Excel and Access to treat them as text. This has
succeeded every time I've used it.

4) If all else fails, save the Excel data to a CSV file and import that into
Access.

 

[Rod Plastow]

Hi Joe,

I've just walked into this one as well. It seems that Access examines the
first 25 rows of the Excel spreadsheet and guesses the data type of each
column. However the data type option of the Import Spreadsheet Wizard is
disabled so you are stuck with Access's best guess that is not always
correct. Why the option is disabled is beyond my comprehension; this was not
so in previous versions of Access where as a user you had complete control
over the data type of the column.

So what to do? If this is a one-off and a simple import of one Excell
spreadsheet into one Access table then I suggest you edit the spreadsheet and
insert a dummy data row at the top. Enter alpha characters in the columns
that you wish to force to Text data type and numeric and date values as
relevant in the other columns. Now import the spreadsheet, clean up any
remaining conversion errors and then delete your dummy row from the Access
table - messy but it works!

If however this is a task you, or one of your users repeats frequently then
you may have to search for a more elegant solution.

Regards,

Rod

PS Let's lobby MS to re-enable the data type option.

 

[John Nurick]

Hi Rod,

I can't speak for Access 95 or earlier, but in my experience from Access
97 onwards the Spreadsheet Import Wizard (unlike the Text Import
Wizared) has never allowed one to select data types.

On Tue, 16 May 2006 00:30:02 -0700, Rod Plastow <Rod

 

[Rod Plastow]

Hello John,

Really? I do remember doing it though but I have been using Access off and
on since about 1992. What version would that be?

Anyway it's somewhat beside the point which is why cannot one specify the
target data type? Surely it's a required/sensible feature of any data import
function in any situation.

Regards,

Rod

 

[John Nurick]

Really? I do remember doing it though but I have been using Access off and
on since about 1992. What version would that be?

Access 1 or 1.1, I think.

Anyway it's somewhat beside the point which is why cannot one specify the
target data type? Surely it's a required/sensible feature of any data import
function in any situation.

On information so far it will be included in Access 2007. See e.g.
http://msdn.microsoft.com/msdntv/transcripts/20060413AccessCCTranscript.aspx