Invalid Argument when importing text file (1 Viewer)

baldbalrog

New member
Local time
Today, 09:38
Joined
Mar 10, 2011
Messages
4
I regularly import a text file in similar layouts into Acccess 2010. For my latest job, I keep getting Invalid Argument when importing into an existing table. The file is comma separated. If importing it to a new table, it adds two new columns - despite ther being no extra commas in these positions in the file, and reports both as type conversion errors.

Anyone any clues?

Yes - I tried compacting and repairing the database, though the 2Gb rule isn't a problemn in 2010 is it?

Help!!
 

DCrake

Remembered
Local time
Today, 09:38
Joined
Jun 8, 2005
Messages
8,632
IS your app over 2g?

Is your app split?

How are you importing?
 

baldbalrog

New member
Local time
Today, 09:38
Joined
Mar 10, 2011
Messages
4
Hi

Most of the tables are in SQL (though not the new table I tested with and found the extra columns).

The access database file size is 193,376Kb

I use the import text wizard. We do this every day of the week on different jobs.

I even went into Advanced - which can't see the extra columns.

Running out of hair rapidly!!
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Sep 12, 2006
Messages
15,614
do a "special"

read it in a line at a time, use split, based on commas, and see if one line does have the wrong number of columns. There MUST be something strange.

it will take a while, possibly, but I expect it will be worth while.
 

boblarson

Smeghead
Local time
Today, 02:38
Joined
Jan 12, 2001
Messages
32,059
And Yes, 2Gb is the max in 2010 as well. If you need more you can move to SQL Server 2008 Express which gives you 10Gb.
 

baldbalrog

New member
Local time
Today, 09:38
Joined
Mar 10, 2011
Messages
4
Folks, thanks for the suggestions.

It turned out it WAS importing the data anyway. I found several copies of it in the table after transferring data via a jet version of the table. We then remembered we get this with SQL back ends, and last year, just ignored the invalid argument error notification.

So I deleted the multiple copies and imported again, expecting to have to ignore the error, but I got a different (SQL) error - "Explicit value must be specified for identity column in table ..... either when IDENTITY_INSERT is set toON or when a replication user is inserting into a NOT FOR REPLICATION identity column"

There is one Identity column in the table which looks to be set up properly, and I'm the only user in the database, which is not being replicated.

Any clues anyone?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Sep 12, 2006
Messages
15,614
not familiar with importing to SQL, but this sounds like an autonumber field problem. Maybe the offending column has some specific values and some blanks, and the import can do either one or the other (ie all blank, or all pre-set) but not a combination at the same time.
 

baldbalrog

New member
Local time
Today, 09:38
Joined
Mar 10, 2011
Messages
4
Gemma

Kinda my thoughts.

After truncating the table, I'm now back to getting the "Invaid Argument"/File not imported" messages, but it DOES import the data.

That's Microsoft for you!
 

dfenton

AWF VIP
Local time
Today, 05:38
Joined
May 22, 2007
Messages
469
If you're inserting data into a table with an identity field while IDENTITY_INSERT is turned on. That's what this error message is telling you:

"Explicit value must be specified for identity column in table ..... either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column"

It doesn't have anything to do with Replication. When IDENTITY_INSERT is turned on for a table, it means you can append existing values to the indenty field. You don't want to do that, so you need to turn it OFF. This can be done from Access with a passthrough query:

Code:
SET IDENTITY_INSERT MyTable OFF
 

Users who are viewing this thread

Top Bottom