Import from Excel problem

[cheyenne2002]

Hi,

I am trying to import a file from Excel to Access. I need to add this data into an existing table set up by one of the Access templates.

I checked to be sure that the heading names match the field names in the existing table, but I get an error message that basically says there is an error and the file was not imported.

I then imported the Excel file into a new table and compared the attributes of the fields. They all seem to be okay.

Any suggestions as to what else I might be able to do. My problem is that I will be trying in the future to import and add records to the existing table in Access.

I need some help and guidance.

Thanks
Sharon

 

[rjoubert]

I then imported the Excel file into a new table and compared the attributes of the fields. They all seem to be okay." - Did you check the field types to see that they match up to the table you are trying to import into?

 

[SkipVought]

Hi,

Do you happen to have a column in your Excel sheet that has BOTH numeric and text data?

Skip,

_[red][/red]

 

[cheyenne2002]

Thanks for the quick reply:

rjoubert, yes I ddid check to see if the field types were the same. First try I found one with number and the other with currency. I made both number and tried again, but it did not solve the problem.

Skip, The address field, which is text, has both numbers and alpha characters.

Sharon

 

[cheyenne2002]

I have some other info on this:

I went in and createe a new table, when I got to the part in the wizard where you can determine if you want to skip a field, it would not let me choose other fields than the first one. I clicked on the header for the second field but the data shown on the top was still for the first field.

Any ideas what that is about.

Sharon

 

[SkipVought]

A row with...
[tt]
123 Main
[/tt]
in a column is a STRING

A row with...
[tt]
123
[/tt]
in a column is a NUMBER.

STRINGS & NUMBERS do not mix well in the same column.

Skip,

_[red][/red]

 

[cheyenne2002]

Skip, I understand. How does Access handle street addresses in a database? There must be a way to import this data...hmmmmm....got to put my thinking cap on.

SMD

 

[Ed2020]

Access will ignore the field data types of the destination table and make its decision based on the first x number of rows in the Excel file.

If the first rows a numeric for a given column and then the data column has some text in further down you will experience problems importing the file.

The only answer I found (I had the exact same problem as you, with address data) is to programmatically convert the file to a CSV file (using Automation of Excel) and then import the text file.

Let me know if you want the code and I'll post it tomorrow.

Ed Metcalfe.

Please do not feed the trolls.....