Values Dropped When Importing Text

[Smartin]

Hi All,

Using A2003. I discovered a glitch in my database today that I traced
back to a text file import.

I already have a table in place to receive the text. One of the table
fields "CLM" is a 20 character text field, zero-length OK, not required.
The text file I use to populate the table contains a 10-character
element in the CLM position. The actual values look something like this:
WC02123456
WC03987654
0000979797
0004321000

What I found today is all the CLM values that look numeric
("0004321000") were dropped and CLM is Null. Moreover, /only/ those
kinds of values were dropped. The alphanumeric values are all there.

Has anyone seen this or have ideas how to prevent it? Note I cannot make
CLM a required field.

I can go back and fill in the missing values in another way, but the
extra step seems unnecessary, and this mysterious omission sort of
breaks my confidence.

 

[dbahooker]

yeah I got fed up with this crap about a decade ago; and I moved to
'Data Transformation Services'

MDB is for lamers and retards.
Move to SQL Server or have countless sleepless nights

-Aaron

 

[JamesDeckert]

Comma delimited or Fixed width?
In your text file are both the text and numbers contained in quotes or
neither or what?
In the import specification it is possible to specify the text qualifier. If
the text is contained in quotes and the number is not, Access may not think
it is a text field???

throwing out some thoughts,
James

 

[Smartin]

Comma delimited or Fixed width?
In your text file are both the text and numbers contained in quotes or
neither or what?
In the import specification it is possible to specify the text qualifier. If
the text is contained in quotes and the number is not, Access may not think
it is a text field???

throwing out some thoughts,
James

It's tab-delimited, but after looking more closely at my problem today I
realized I omitted an important step in my original problem description.
The root cause is operator error... The text is loaded in Excel first,
then the Excel data is imported by Access. There are a couple reasons I
was doing this, but I think I shall have to find a better way.

Anyway I think that explains it. Excel makes numeric cells where the txt
data looks numeric. The Access import definition for CLM is text, and it
balks when it encounters numeric data cells.

Sorry to have mislead you. Thanks for your thoughts.