Importing from Excel

[Guest]

Hi

I am trying to import data from and excel file. There are six columns in
the spreadsheet.
A - DATE
B - CLIENT NUMBER
C - TREATMENT NUMBER
D - TIME (FORMATED AS A NUMBER)
E - SERVICE PROVIDER NUMBER

i am trying to import them into my contact table in access with the
following table headings

Autonumber
Date
Client No
Treatment
Time
Service Provider

when i do the import and click on finish i get the following message "An
errror occured while importing 'File' File was not imported.

Can someone help as to what the problem may be.

Thanks

 

[Guest]

Forgot to mention that the notes field sometimes has more than 255 characters
and the notes in the contact table is an OLE-object where you can type as
much as you want.

Hope this helps a bit more

 

[Ken Snell \(MVP\)]

How are you trying to do the import? Tell us the steps you're using.

 

[Guest]

i am going to File - External Data - Import

and then following the wizard steps

 

[Ken Snell \(MVP\)]

That method will not properly import text strings that are longer than 255
characters. You will need to use a macro or VBA code to run the
TransferSpreadsheet method, as it will import longer strings.

The error that you're getting, if the field list that you posted for the
table is the order of the fields, results because there is no Autonumber
column in the EXCEL file. The import is trying to put the DATE column's data
into the Autonumber field in your table; this should cause an error most, if
not all, of the time.

If you want to keep the autonumber field, open the table in design view,
highlight the Autonumber field's row, and drag it to the end of the field
list (make it the last field). Then the import will match up the columns
with the fields correctly.

Note that it's not a good idea to use Date as the name of a field. See these
Knowledge Base articles for more information:

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

 

[Guest]

I will only need the autonumber after i have imported the information so can
i remove it and then reinsert it afterwards??

Thanks for the Date advice i will chamge the field name to something else.

Also can you advise me how to import the information taking into account
that the notes field will be longer than 255 words and knowing that i have
formated the notes field to a OLE object to accomodate the extra texts.

Thanks, as im only a novice as databases and rely alot on the wizards.

Andrew

 

[Ken Snell \(MVP\)]

If you want to delete the autonumber field and then add it back after you
import, that should work. But, if you do what I suggested, you don't need to
delete it at all.

Check out TransferSpreadsheet in Help file to get info on how it works. If
you're a "novice", then using a macro to run it will probably be your better
option. You can create a macro to run the TransferSpreadsheet action; Help
shows how to do this.

Post back after you've checked out Help and tried to set up the macro; we'll
answer questions for you!

 

[Guest]

Have created macro and get the following error messgae

Field 'F1' doesnt exist in destination table 'contact'

My spreadsheet has the following colunms the info starts in row 1. there is
no column headings at all

A - Date
B - Client
C - Catergory
D - Time
E - Notes
F - Staff Member

the following are the field names in the my table contact

Pdate
Client No
Catergory
Time
Notes
Staff Member
Contact ID

the columns match up with the fields, so i dont know whats going wrong.

Thanks

 

[Ken Snell \(MVP\)]

Post the arguments that you're using for the TransferSpreadsheet action in
the macro.

 

[Guest]

Its ok Now
thanks for all your help.
I ended up putting the field headings in the first row of the spreadsheet
and changing the option to yes for first row contains heading and then it
worked fine.

Cheers