Access 2007: Import Text Wizard errors

[M Skabialka]

My users have been importing updates into a database for years from a
delimited text file. Now they are using Access 2007 and get this error:
Error: Import Text Wizard: Invalid argument. OK
Import Text Wizard: An error occurred trying to import file
'c:\temp\import.txt'. The file was not imported.
The import errors table shows that there is a 'Null value in an auto-number
field' in the ID field.

When I try the same procedure on an Access 2003 machine there are no
problems so there isn't anything wrong with the data.
In Access 2007 it appears that the records were appended but users are
gun-shy about performing a procedure that shows errors.

Is there a patch to fix whatever this problem is?

Mich

 

[ntc]

it is frustrating indeed when something works in 03 but not 07... I have
found that sometimes 03 was a little more forgiving and it is not an 07 bug
but just more rigorous enforcement of rules...not sure that is the case here
but just a comment...

clearly you can not be attempting to import anything into an autonumber
field; that field should be left out of the import so that 07 can generate
its own autonumber value as the records append.....

not sure if those nulls you see in the error table are pointing us in the
right direction or not.....

as a sanity check/trouble shoot; you might link to the excel like it is a
table (not import)...and then whip up a MakeTable query on it....and inspect
the resulting new table's data field properties; possibly you might see some
misalignment of field properties between this new table and your regular
table.... possible that 03 interprets an ambiguous excel column one way
while 07 interprets it another.....

 

[M Skabialka]

It is NOT an Excel file - it is a delimited text file with the delimiter
being "|". I placed the autonumber field as the last field in the Access
table and the imported data does not go into that field.
As a test I opened the text file in Excel, using the same criteria as Access
and the data looks fine. It did convert one field to number because some
entries started with 0 but in Access I am importing into a properly
formatted table so this is not an issue. The primary key field is never
null but all others can be.

I need an answer to this problem because users cannot visually inspect about
1000 records every time they import the file - it kind of defeats the whole
automation concept, so they are putting off importing it for fear that
somehow the data has no integrity.

The users do not have Access 2003 on any of their machines so I can't go
that route.
Mich

 

[M Skabialka]

I reduced the text file to one record for testing - all data is exactly what
it should be, but I still get these errors.

I created a specification file and used that to try and import the text
data, but still get the same message - Invalid Argument - Help shows this
message:



Invalid argument. (Error 3001)

You tried to perform an operation that involves a routine in a DLL, and one
of the arguments to the routine is invalid. Check your entry to make sure
you have specified the correct arguments, and then try the operation again.

This error also occurs when you attempt to use mutually exclusive constants
in an argument to a method, such as specifying both dbConsistent and
dbInconsistent in the options argument to the OpenRecordset method.



How can I fix this problem?

Mich

 

[ntc]

you can reopen this over in the specific Import/Export section of this
community and get a fresh look by the MVPs

all I can suggest is what I would do / have done.... eliminate all the
variables and get the basics working and then rebuild from there...it just
seems to me that lots of 07 imports lots of stuff so that I don't think it is
a bug per se but there is something about that originating file that it
doesn't like - relative to the destination table.

you are going in the right direction in trialing with just record

trial also with less fields

and trial with link/MakeTable rather than import...

make sure there are no hidden attributes, formatting or excess space/lines
in the txt doc

trial with a different deliminator such as comma if possible...