TransferSpreadsheet Problem - Keys

[Guest]

I am trying to import files that come off of a very "dirty" database system
called Davox into Access via the Excel format. The only format that Davox
seems to handle at all for output formats is Excel format (CSV is completely
messed up as they didn't do their double-quoting right, but that's a whole
other story).

So, I want to be able to import these files into Access, clean them up,
change data types from text to numeric so that I have well-formed data types,
etc etc. (That's why I'm importing them instead of linking so that I can
clean up and change data types where this system does nasty things like for
example "000-35" as the number -35, or they have text that is a blank string
("") but not a null, etc....)

When you import manually in Access it gives you the option to NOT create a
Primary Key, and it will skip the whole section with the "Creating and
Changing Field Indexes" which takes forever it seems on a 22MB Excel file
over the network share that I'm forced to do this all on. When I import via
TransferSpreadsheet though, I see no such option at all for skipping the
creation of a Primary Key. Is there a way to get TransferSpreadsheet to skip
the creation of a Primary Key?

 

[Mo]

Don't think so, but what's the problem if it does set a primary key.
It's just an autonumber field, and can be deleted after the import from
table design.

But I've another idea - can you save the Excel to a csv and then import
the csv using an import specification? In vba is would look something
like:

DoCmd.TransferText acImportFixed, "IMPORT SPEC HERE", "FILE", ,,

When you import a csv, after selecting the file, click the Advance
button in the lower left. This takes you to an area where you can
create an Import Specification, after filling in the fields, lenghts,
attributes, and click Save As. The name you save to is the same as
what you'd put in the Import Spec Here in the above code snippet.

I've found this is a fairly fast import.

Hope it helps some.

Mo

 

[Guest]

Heh, the problem was I wasn't actually deleting the table (only doing a
"delete * from blah" on it). When I do that, no primary key probs, and it
imports faster.