Importing from Excel - losing 3rd level

[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

 

[CautionMP]

Two questions, one if you Link (Linked table)to spreadsheet file do you still get the same error(s)? If you save the spreadsheet file as delimted text and then use TransferText does the informaton import correctly?

 

[markSaunders]

if i use a csv then 5.10 still appears as 5.1

using linked tables replaces the third level numbering (x.x.x) with #Num!

What I ended up doing was....

Format the XLS
[ul][li]put a space before the hierachical indicator (ClassID)[/li]
[li]where two are the same (x.1 and x.1) put a 0 after the second one[/li]
[li]save the file[/li]
[/ul]
Format Access
[ul][li]import XLS to Tbl_Import[/li]
[li]run Access queries to pull data to relevant table (category, topic, subtopic)[/li]
[li]save the file[/li]
[/ul]
Import to SQL
[ul][li]run a DTS package to import to SQL[/li]
[/ul]
- ideally I will change the Access queries to be SQL stored procs and eliminate the Access component - when I suss how to do it!!!

Cheers
Mark Mark Saunders

 

[KippL]

Mark,
Here's my guess......(Any field name in an access table with "ID" in it will almost always get set up an indexed field (most will not allow duplication by default) so if you have duplicate information in this field in your xl data this will generate the first error....your second error (truncation of zeros) (the data)is seen as a number field in your access destination table.....the cheap & dirty solution is to predefine your destination table in access....(where you're importing your data into).....fool-proof method to get data in....set all field types to TEXT (make sure to allow NULLS if you have blanks) and turn off indexing.......this should get your data in as it is in your spreadsheet.
Good Luck ,
Kipp

 

[markSaunders]

KippL

I dont understand why when the CL column is formatted as text and the Access field is formatted as text Access insists on treating the field as a number.

I wonder if this is because Access receives the XL cell as a number (perhaps because the first character is a number?) then attempts to convert it to a text field. - this seems to make sense (i.e. the explanation not the reason for its functionality!!) in respect of the fact that when a space is added the the XL column before the number then access imports no problem.

the macros take data from the import table and, expecting text, convert to numbers for other ID fields and links etc.

strange goings on behind the scenes i think but resolved at last (in a 'it works' sense)

would be interested in knowing exactly what Access is up to if anyone out there knows the technicalities?

cheers Mark Saunders