paste errors import

[slimjen1]

Hi all, I am using Access 2003. I am recieving paste errors when importing a spreadsheet from excel 2003. Some of the columns in the spreadsheet are alphanumeric and they have green triangles and they do not import. I tried convert to number and format the column as text or general and still won't work. Also some numbers are changing to exponents. Can anyone help please?
Thanks

 

[Alansidman]

Try saving the Excel file as a .csv file and then importing it into Access. When you do this, you can click on the Advanced Button during the set up of the import to specify the fields as to the type of format you want. It will also then import the numbers as text and avoid the scientific notation.

 

[slimjen1]

Thanks Alan. I'll try this

 

[slimjen1]

Hello; I am still getting errors when I used this. I think the problem is the users are using two different versions of excel; 2003 and 2007 and trying to paste in the 2003 spreadsheet. Is is anyway I can use import specs and save it so that no matter what the formating issues; it would take care of it. I remember doing this in another database but I think is was txt files not xls. I don't get the import spec wizard when I try importing from xls. Is there any other way?
Thanks

 

[Alansidman]

Jen; you only get the import spec when you import anything but an excel file. Access analyzes the Excel file and automatically assigns formats. That is why I suggested the .csv option. You have more control over a text file import.

 

[slimjen1]

Thanks Alan. I will go this way. I can't remember how to set up my vba with import specification. I know theres a step Im suppose to add and cant find anything on the forums. This is what I have:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
   "tbl_SHIPMENT_temp", "C:INVOICEREPORT\IMPORT1.xls", True

Thanks

 

[gemma-the-husky]

you do not get an import spec when bringing in a spreasheet

the problem is two fold

1. access/excel guessdatatypes based on the first few rows of data, and often treat columns as numbers, when they should be text. one way to avoid this, is to insert a row 2, and put a word like "TEXT" in any coulmn that must be text

2. some cells may contain bad data.

the import errors table will show you the suspect rows, so that you can see what the problems are.


-----
a csv is always better to import than a spreadsheet, imo. you get more control, and general you know the file has not been "compromised". if you have a "raw data" csv, then use that instead of the spreadsheet. avoid the spreadsheet, as actually opening a csv in excel, and saving it again may change it

however if you have a xls file to start with, there is little point in going to the trouble of saving it as a csv.