load an alpha/numeric field into access from a text file

[Guest]

I am loading data from a text file into an access table. One field is
alpha/numeric and when it loads an error table is created - the error is
'Type Conversion Failure'.

I have tryed setting the field to number and text but still receive the
error. Is there a setting that will let whatever is in the field (number or
letters) load as text?

Thanks for any help you may provide.
Red

 

[tina]

when you import the text file, are you using a saved Import Specification?
if the field in the text file is mixed alpha/numeric, the field in your
table will have to be Text data type (or Memo). make sure that the text file
field is designated as Text in the Import Specification.

is the Type Conversion Failure occurring on every record in the table? or
just certain records? and does the data exist in the record(s) generating an
error? or does the data fail to save in that field in the record(s)?

hth

 

[Guest]

tina,
thanks for reply. I am using text data type (also, Memo) and the error
table is still building. The number fields load ( ie: 132, 054) but the
alpha/numeric do not (ie: b3r, 1am).The data does not appear in the table
after import completes - only a blank cell.
Thanks again for your help.
Red

 

[tina]

i'm guessing that the first record has a value starting with a number in the
alpha/numeric field, and it's being wrongly interpreted as numeric. that's a
problem if 1) you're importing to a "new" table, rather than an existing
one, and/or 2) you're not using a saved Import Specification where the
alpha/numeric field has been specified as text.

did you create an Import Specification that sets the alpha/numeric field as
text? and then save it for future use?

 

[Guest]

The first value is a number. I have replaced it and the next 7 with a text
entry (ie:SSS). Made no difference. Still created the error table with 'type
conversion error and those table fields were blank. -- If I'm understanding
your reference to Import Specification, I have set the data type in Design
View to text. (Is this what you are referring to?) -- Would the fact this
data came from an sql server make any difference? All fields in the database
are text fields.
Thanks for your input and my bad if I sound vague. This really has me
stumped.

 

[tina]

well, frankly, it sounds pretty strange to me too. i don't know if the
origin of the text file being SQL Server makes a difference - i wouldn't
have thought so. re the Import Specification: i'm not talking about the
table's design view. when you click File | Import | Get External Data from
the database window, the Import Wizard dialog box opens. you can click on
the Advanced button in the lower left corner of the dialog. in the Advanced
options window, you can designate the data type of each field being
imported. then save your settings as an Import Specification for use in
future imports of the data, so you don't have to re-invent the wheel each
time.

try that, and if it doesn't work, then my only other suggestion is to open
the text file in Excel and look at the column of data that you're having
trouble with - including moving from cell to cell in that column so you not
only see the "display" values but also the "real" values. maybe there's
something odd about the data and maybe you can fix it by highlighting the
column in Excel and explicitly formatting it to Text. then you could resave
the text file, or save it as an Excel file (or both), and try the Access
import again.

hth