Invalid Argument when importing text file

[baldbalrog]

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]

IS your app over 2g?

Is your app split?

How are you importing?

 

[baldbalrog]

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!!

 

[vbaInet]

Running out of hair rapidly!!

Not good seeing that your username is baldbalrog

Are you importing through code or using the Import Wizard?

 

[gemma-the-husky]

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]

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]

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]

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]

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]

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:

SET IDENTITY_INSERT MyTable OFF