Importing data from Excel

[nik9]

Hello All,


I'm sure this is as simple as it gets. But, I need critical help, I am so close to being able to see my consolidated result efforts. I have an excel flie which incorporates 5 columns the data consist of alpha/numeric in each cell/record with periods such as 1900.123.4008 However, when I am importing the flat file into Access it will not read some of my data. Like at line 24 it sill import the data that is clearly on the flat file. So, I am not sure what I am not doing right? I hope my description is detail enough. Please let me know what I can elaborate on further to help give a better idea of my question.

Thanks, Nik

[JoeM]

When importing data from Excel, you cannot tell Access the data type of each field it imports, Access tries to figure it out on its own. I believe it only looks at the first 10 records. So if the first 10 records for a particular column/field all look numeric, then it will choose a Numeric data type. However, if values below row 10 are alphanumeric (have letters or other text characters in them), those will not import because they are not of a numeric type.

When you import a Text (flat) file, you get to choose your own Data Types, so it isn't a problem in importing those kind of files.

There are a few ways around the issue when importing directly from Excel. Any one of these three should work.
1. Make sure if you want an alphanumeric entry, that you have alphanumeric entries somewhere in your first 10 rows;
2. Export the Excel file to a Text file, and import that Text file where you can control the field Data Types;
3. Import the file to a Temporary Table in Access, then use an Append Query to write the data from the Temporary Table to your Final Table. Realize that this query may have to do some data type conversions.

If I am barking up the wrong tree, and this is not the problem that you are having, please post an example that is not working, what it looks like on the file, and what you are seeing in Access.


I am afraid it is not quite clear. Can you post an example that is not working, what it looks like on the file, and what you are seeing in Access?

[alansidman]

JoeM is on target with his response. Further to his response, when I have to import an excel file and I want to ensure that the fields are the right type, I insert a dummy row of data as the first record with the type of data I want Access to recognize, ie. Numbers, text, dates, etc.

When I complete the import, I then delete that first record.

Alan