Data Import to Access that the format is not imported

[Erictsang]

Dear all

I have a problem need advice

I have a data base in excel file. around 65 fields and 2900 records.

i have checked all the fields in excel, formatted them in number, text, date etc


When i import to Access. some time it fail and make an error data based to keep all my record.



How can i resolve it?
Eric

[CJ_London]

formatting of values has no effect - it is the underlying value that is imported.

Excel is well known for allowing a mixture of datatypes in one column - you'll often see a warning that a number is stored as text with an offer to convert it to a number. Often identifiable because the default positioning is left for test and right for numbers. It is safest to import everything to a temporary table as text then do your conversion

The importer will look at the first few rows (I think 16 or 32) and decide from that what the datatype is. Nulls are treated as text. So if you have a column of dates or numbers where the first few rows are blank, the importer will assume the column is text - which may not then not import to a destination table where the values expected are numeric

[JoeM]

It is safest to import everything to a temporary table as text then do your conversion

Another similar alternative that I often use is to export the Excel file to a delimited text file (like CSV or Tab delimited).
Then import that, where you get to control all the data types of the incoming data fields.

[Erictsang]

this is exactly what happened

in the excel file there are not of null.
we formatted this as number, same as in Access's table but it turn out unable to add

Eric

[CJ_London]

we formatted this as number

As explained, formatting is not used when importing. Try JoeM's suggestion or just save the excel file as a .csv file