importing from excel - losing data

[markSaunders]

Hi, I have a spreadsheet containing data in the format described below...

x category 1
x.x topic x.1
x.x topic x.2
x.x.x subtopic x.x1
x.x.x subtopic x.x.2
x category 2
..etc..

I have used a TransferSpreadsheet macro but it refuses to import the subtopic level (x.x.x of data).

I have two text fields in the import table (as in the spreadsheet) named ClassID and ClassDesc.

If I set the primary key as ClassID then I recieve an error on import to the effect that there was a primary key violation or referential integrity problem.

If I set the table to contain no primary key then all the data is imported with the exception of the ClassID (x.x.x) for the subtopic level - i.e. the ClassDesc is still imported but with no value for the ClassID for subtopics.

There is no problem if I manually copy the contents of the spreadsheet to the clipboard and paste directly into the table.

This is the first process for uploading the data to SQL2000 (as I find it much easier to write these macros than try and do the same thing in SQL [for now...])

Any help would be greatfully appreciated.

Regards
Mark Mark Saunders

 

[KippL]

Mark,
Here is my guess.........when access is creating a table to store your data in it "sees" the characters "ID" in a field name.....assumes it's a KEY field and doesn't allow duplication......you need to pre-design an empty table in access and set all field data type to TEXT.......make sure to allow NULLS (if your spreadhseet contains blank cells) your second error (truncating zeros)is caused by access seeing the field as a number and thus a zero is redundant....setting this destination field to a TEXT type will cure that problem.
Access import wizard is sweet for somethings....this isn't one of them....build your own table.......you'll be much happier.....then import your data.
Kipp

 

[markSaunders]

Cheers KippL

the columns in excel are formatted as Text as are the fields in Access. The field in access is set as a primary key, although i have changed and tried this as a non-primary field with allow duplicates to no avail. is it that access is creating some temporary table and treating them (for some bizarre reason) numbers until it copies it to my import table?

i shall try removing the ID characters from field names and see if that makes a difference - as a solution i created a vb app that reformatted the first (classID) column to contain a space as the first character - import to access - run access macros and upload to sql.

cheers
m Mark Saunders