weird occurance when appending data

[patrichek]

Hi,
I have a contactid field that is a p/k and set to autonumber. When i import data from a csv file the autonumber field from the imported data shows a number that is 8 digits long compared to the previous contactid fields that are 4 long. example: imported record from csv contactID = 39980546 normal record input from user contactID = 2140. on the csv there is a contactID field but it is left blank.
Since i'm only in the testing phase of importing, should I be concerned with this, and how may it affect my database?

thanks!

 

[lynchg]

Are you trying to import data into the contactid along with the rest of your data?

I believe you can choose the origin and destination fields when importing data, if the contactid field is included in the import, you need to take it out.

 

[patrichek]

hi,
i've tried it both with and without the contactid field in the csv and returned the same results?

 

[JerryKlmns]

patrichek,

There is no way to input the value you want in an autonumber field (and you should avoid using it as a PK). If you need these values create a new field, copy the existing records PK to that field and import the file.
FYI, when adding records to a table with code the sql statemenet of "Insert Into ... " does not include the autonumber field.

I guess, you should re-organize your table stucture.

 

[patrichek]

Hi Jerry,
i don't want to import anything in the contactid field in my table, i want it to auto generate during the import and it does but not in sequence (8 digit number). that was my original question.... is this normal?

thanks for responding

 

[lynchg]

Have you had a series of unsuccessful imports or have there been many records inserted and then deleted from your destination table?

The autonumber field will use up numbers in the sequence even if there is an unsuccessful insert. Say you have 4000 recs in your table and the highest autonumber value is 4000. You try to insert 25,281 records into the table but it fails. You try the same insert again and it succeeds, your auto number field will look like 3999, 4000, 29282, 29283...

The same principle holds after you have deleted records. Take that same table with just the 4000 records, you delete 2000 (autonumber id's 2001 - 4000), and then insert one new record, it's autonumber field value will be 4001, 2001 thru 4000 are gone forever. You can reset the value back to 1 (or a number you choose), but you have to empty out the data and then re-insert it again.

 

[patrichek]

Would it be possible that it is importing all the rows in the csv, even the blank ones?

 

[patrichek]

Hi Guys,
I got it figured out! what i should have done is once i removed the contactId (autonumber) from the csv i should have deleted the table i was using and used my original from a backup to test it without the autonumber being imported....

thanks for helping me straighten it out!

 

[lynchg]

It could be, it depends on how you set up your import.

Jerry had a point in that you don't want to rely on the values in an autonumber. You can use it as a pk field or part of you pk but you shouldn't be too concerned with the values themselves. Although if you are already at 39 million + in the autonumber sequence, you may eventually use them all up.

Look at what you are doing in your import, maybe try linking to your csv, and create a query that mimics your import criteria and see how many records you get.

 

[patrichek]

Lynchg,

its fine now, the problem was i after i first tried the import and noticed the large autonumber i never deleted that table so when i tried importing without the autonumber in my csv that 8 digit number in my table kept growing. I've since deleted the table, imported the table from backup and the numbers after importing the csv without autonumber field are now in the correct sequence.

thank you!