I need to import alpha and numeric data from excel

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

Guest

I am importing a large excel spreadsheet to access. One of the columns is
"invoice number". This field is defined as text, but has both alphanumberic
entries and numeric entries - example "12345" and "45678X". I can import the
alphas or numerics, but not both. I get conversion errors on whichever is not
first in the spreadsheet. How do I import both successfully?
 
hi,
in access you could define the table first then import
from excel to an existing table, the one you define before
the import.
a bit manual but i have used it before.
 
I am importing a large excel spreadsheet to access. One of the columns is
"invoice number". This field is defined as text, but has both alphanumberic
entries and numeric entries - example "12345" and "45678X". I can import the
alphas or numerics, but not both. I get conversion errors on whichever is not
first in the spreadsheet. How do I import both successfully?
Click to expand...

Another approach is to ensure that all the entries in this column are
text. You can do this by prefixing them with an apostrophe (this isn't
displayed in Excel or imported into Access, so does no harm).

This Excel VBA function may help. It adds an apostrophe to every cell in
the selection (watch out for wrapping in the For Each line):

Sub AddApostrophesAllToSelection()
Dim C As Excel.Range
With ActiveWorkbook.ActiveSheet
For Each C In Intersect(Selection,
..UsedRange).SpecialCells(xlCellTypeConstants).Cells
C.Formula = "'" & C.Formula
Next
End With
End Sub
 
This is why I always, Let me reiterate ALWAYS export the XLS to TXT first.
Excel never lets me choose the import format and it always (95% of the time)
chooses the wrong data type for Zip Codes (which are text that look like
nubers. It also amanges to force integer or double when the values at the
begining of a numeric field are within those ranges, causing larger nubers to
drop off. Save yourself the angst and just export to .txt then import. It
takes two seconds. and is easy enough to automate in excel or from access.

rev.
 
Back
Top