Import Excel into Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing an excel spreadsheet into Access as a table. One field in Excel (Room Number) is a Numeric Field in the original file, but has text stored in it (ex., 305A). I changed the format of the column to be "text". When I import it into Access, it still insists on importing as a "double" & then I get an error table that says "type conversion failure". I have tried to import it into a new table, I have tried to import it into an existing table that already has the Room Number field created as a text field, I have tried copying the original excel spreadsheet into an Excel template that has the Room Number field set to be a text field. No matter which way I try importing it, it insists on being a "double" number & drops my data in that field for 2 of my records that show room numbers with an alpha character at the end. How can I get this to import into Access as a Text field?
 
Can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a number?

Or, put in the first row the "nonnumeric" value so that ACCESS sees it and
knows that not all values are numbers.

If nothing else works, you can use Automation (via VBA code) to open the
EXCEL file and read the spreadsheet cell by cell and write the values into a
recordset that is based on the target table.

--
Ken Snell
<MS ACCESS MVP>

Bogga said:
I am importing an excel spreadsheet into Access as a table. One field in
Click to expand...
Excel (Room Number) is a Numeric Field in the original file, but has text
stored in it (ex., 305A). I changed the format of the column to be "text".
When I import it into Access, it still insists on importing as a "double" &
then I get an error table that says "type conversion failure". I have tried
to import it into a new table, I have tried to import it into an existing
table that already has the Room Number field created as a text field, I have
tried copying the original excel spreadsheet into an Excel template that has
the Room Number field set to be a text field. No matter which way I try
importing it, it insists on being a "double" number & drops my data in that
field for 2 of my records that show room numbers with an alpha character at
the end. How can I get this to import into Access as a Text field?
 
Can you go into EXCEL and insert a ' character in front of each of the
values in the column? That will tell ACCESS to treat the value as a number?
Click to expand...

Ken meant "treat the value as text even if it looks like a number".

You can add the apostrophes under program control using code like this:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub
 
John Nurick said:
On Sat, 22 May 2004 18:09:01 -0400, "Ken Snell"
Ken meant "treat the value as text even if it looks like a number".
Click to expand...

You are correct.... thanks!
 
Back
Top