Importing Excel file into Access -- format errors

  • Thread starter Thread starter MacNut2004
  • Start date Start date
M

MacNut2004

Hello,

I have to import an excel file and I have a field called "ID" that NEEDS to
be in number format, which is how it is in Access. In the excel import file,
i make sure it's number format, but once it's in access, why is the format of
it changing to text? There are ONLY NUMBERS in the import file in that field.


I have an automatic import function that imports the file, and then joins
the "ID" field in the data in the import table to the "ID" field in the main
table in the database and it won't join because it is text in import, and
"number" in the main table.

Without having to manually go into the design of that import table and
change the format to number, is there an automatic way to do this once
imported? It's an ongoing issue and really annoying.

Thanks,
MN
 
Check to see if the spreadsheet doesn't have any text in the first row for
that field. If it has a header of "ID" it could confuse Access. If it does,
see if changeing ID to a number allows the import to work as you hope.

You could also link to the spreadsheet instead of importing it directly.
Then you could append records from it to a table where the ID field is set to
number.

You could also try importing into a table that's already in Access. I'm
assuming that you are creating a new table during the import.
 
Access gives text type data a higher priority than numeric type data.
If you have a column in excel of only numbers, we would expect access would
see it as numbers and import as numbers.
If only one of the first 8 rows of that column had a text entry, or
something that access thinks is a text entry, this would cause access to
treat the whole column as text.
So, is there any way that access could mistakenly think that there is a text
entry in the first 8 rows of that column?
Could access be misinterpreting the column header - ID - and be tricked into
thinking it is a text column?
Try the import using the wizard and specifying that the first row does not
contain column headings.

Jeanette Cunningham
 
Hello,

Thanks for the response. They are actually all numbers for sure, I just
checked it. And the header is NOT "ID" , i just used that for this example.
Now...not ALL of the rows have this particular field populated....the other
rows may have null in them. Could this make it a text field because of the
nulls?

Thanks!
MN
 
MN,
using A2003 in A2000 file format, I just tried to import from excel.
I set up a number column formatted as number with 2 decimal places.
I left the 3rd and 5th row blank.
Access imported it to a new table as numeric data as double.

Would appreciate if you would post the first rows of that column from the
spreadsheet so I can try to reproduce the problem here.

Jeanette Cunningham
 
I just discovered that there are like 20 lines of blank IDs and then in row
20, that field finally has a numeric value. I think cut that first line and
inserted it as the first line in the import and when i imported it, there
were no problems.

Is this a typical problem with importing into access where the first records
are null?

Thanks,
MN
 
MN,
I was able to reproduce the same problem as you when I made the first rows
20 blank in the number column.
I haven't heard that it is a typical problem, but it could be. It is another
piece of info to add to our knowledge about importing problems.

Jeanette Cunningham
 
Back
Top