Importing and Counter problems.

[woodyinoz]

I am currently importing a text file created by a CAD package directly into a table. However, at present there are no unique fields which I can use as my primary key. I have tried entering an autoincrement field but this causes the text import to fail and a single blank row is added to the table with a new counter number only.

Has anyone got any ideas how I could get around this?

Thanks,

Woody.

 

[Langley]

First of all never use an autoincrement field as the primary key. The solution is to make your own increment field. Import the data into a table and add a blank NUMBER field to the front of it (let's call it 'myKey'). Then put the table in a scan loop and increment the number. I recommend starting with 1001. If it's something you are going to do frequently then you need to keep the numbers unique. Do this by creating a table with one NUMBER field (let's call it 'Counter') to use as your seed number for incrementing - to begin enter 1 record in that number field with a value of 1001 (unless you want to start at a different number). Each time you do the import and run the scan loop, you will automatically be in sync with your numbering scheme.

var
   cntr   number
   tcSeed tCursor
   tcCad  tCursor
endvar

tcSeed.open(":MyAlias:myNumTable.db")
cntr = tcSeed."Counter"

tcCad.open(":MyAlias"CadData.db")
tcCad.edit()

scan tcCad:

   tcCad."myKey" = cntr
   cntr = cntr + 1

endscan

tcCad.endEdit()
tcCad.close()

tcSeed.edit()
tcSeed."Counter" = cntr ; start with the right number next time

tcSeed.endEdit()
tcSeed.close()

Now reconstruct the cad data table and key that first field.

Hope this helps,

Mac

 

[footpad]

Woody,

Mac's right; autoincrement fields sound nice, but they're a pain to work with, in part because Paradox (BDE, actually) doesn't assign the new value until the record is posted. It can work, but it quickly causes problems, expecially when you start sharing your tables with multiple users.

If all you're after is some arbitrary value designed to be unique, you *can* use a DateTime field with a Default of NOW. Mac's approach is the preferred one, though, since it gives you control over the incrementing process.

I might add more error checking but he's got the basic algorithm down.

If you'd rather not build this, you might be interested in a third-party product that does it for you. Check out the description of AutoKey at

http://www.rdaworldwide.com/RDAprod.htm

for full details. (No, I'm not affiliated with RDA. Just familiar with their products.)

Hope this helps...

-- Lance

 

[woodyinoz]

Cheers for that. Once again you've helped me out!

All of the help is much appreciated guys!!

Woody.