Excel Importing.... Fact or Fiction?

  • Thread starter Thread starter Paul Mak
  • Start date Start date
P

Paul Mak

I have an Excel file needs to export to an Access table. The Excel file has
one worksheet and it contains 103 rows of data in 12 columns. One of the
column "Zip Code" the data type was set to "General" to allow both numeric
and text data type of the zip code like "56124" or "56124-0014". I also
noticed in that worksheet, only the last 6 rows of the zip code is in the
text data type format. In the Access table I have a field named "Zip Code"
and it set to "Text" data type.

It failed to import the zip code for the last 6 rows in the Excel file for
the reason "Type Conversion Failure". So I sort the "Zip Code" column from
"Z to A" so that the text data type appears at the first row in the Excel
file and it succeed to import to the Access table without an error. By
further experimenting it in the Excel file I re-sort the "Zip Code" column
back to the original sorting order and changed the data type to text on row
25 on the Zip Code column and it imported to Access table without an error.
I went back to the Excel file changed the row 25 back to numeric data type
and change the row 26 to text data type and the importing to the Access
table failed at the same 6 records. So I conclude that in the Excel
importing to an Access table, the Access uses the first 25 rows of the Excel
records to determine the data type for the entire column in the Excel file.
Fact to Fiction?
 
hi,
i'm not sure about the exact number of rows but fact.
access does not look at the entire data just the first few
rows.
 
Paul said:
failed at the same 6 records. So I conclude that in the Excel
importing to an Access table, the Access uses the first 25 rows of
the Excel records to determine the data type for the entire column in
the Excel file. Fact to Fiction?
Click to expand...

Fact.
 
Yes.

You can avoid this problem by creating a "calculated" Column in Excel using
the TEXT() worksheet function to convert the mixed Column to all Text. When
you import, skip the mixed Column and import the "calculated" Column.
 
Back
Top