Import to text - only text value NOT importing

[Gerry]

I have a spreadsheet (XL 2007) with a column that is mostly (all but 1) numeric values (10 digits). I'm importing this to a text field, to accommodate the single text value (12 characters long) in the column. I've left the field length at 255 for now.



Everything EXCEPT that text entry is being imported. For that record, I get a NULL value in that field.

What am I missing?

(ALSO: I've been working with a saved import, to ensure consistency - is there a way to get in and look at those settings?)

[RuralGuy]

I've been working with a saved import, to ensure consistency - is there a way to get in and look at those settings?

It is the Advanced button about halfway down this link.

[RuralGuy]

Here's another picture: http://www.btabdevelopment.com/main/...9/Default.aspx

[Gerry]

Here's another picture: http://www.btabdevelopment.com/main/...9/Default.aspx

The file I'm importing is an Excel file, not a text file. My problem is with a single column, which I wish to import as text.

[Gerry]

It is the Advanced button about halfway down this link.

No link in this message. Can you re-post?

[RuralGuy]

Sorry about the missing link. I was just trying to show you how to look at and edit your import specifications.

[Gerry]

Sorry about the missing link. I was just trying to show you how to look at and edit your import specifications.

Ah! I see what you mean. Unfortunately, that seems to only appear when using the text import. That's exactly what I was looking for - unfortunately, it doesn't work with my import. /grumble

[Gerry]

An update on this one:

If I reformat the column in Excel (this takes several steps to ensure format doesn't change to scientific notation on some numbers) to Text, then all imports properly.

It appears that Access is running a conversion filter based upon the first entry, which causes the text entry to fail (since the first entry is numeric).

Anyone have thoughts on how to prevent this? I've been using a saved import, but would willingly build my import in VBA if there was a solution to this issue.

[RuralGuy]

I was just going to suggest that. It seems to me that's what I had to do to get it to import correctly. Access is just being waaaayyyy too helpful here.

[Gerry]

LOL - that's exactly the words I used to describe this to my wife.

We're going to be doing some programming in Excel before the import, so we may just roll a text conversion into that programming, and put that whole column (including the numeric values) in text format. That would solve the import without going through fixed-width or delimited exports.

[RuralGuy]

I think that is as close as we can get to Solved for this thread. Do you want to follow the link in my sig and mark this thread?