"null values" when importing from excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm importing an excel file into access 2002 and I'm getting the error msg

"Error creating primary key on field 'Part Number' (Index or primary key
cannot contain a Null value.)."

Of course, I don't see a problem in the excel file and access is making the
field blank (null?). Does this error mean what it says or is there a
"secret" that someone can share? Any suggestions as to what to look at?
 
The error message suggests that the field you are using from Excel as a
primary key in your Access table contains a null value.

If you'd like to check into this more, consider importing to an import
(temporary) table without any primary key or indexing. Then run a query
against the import table to "parse" the import fields into their respective
permanent tables/fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The field in the Excel table is not null but Access is putting it in as null
for some reason.....
but I'll try what you suggested. Thanks!
 
I did what you suggested and it's still giving me an error - "TypeConversion
Failure" on the Part Number field. How can I tell (in Excel, I guess) what
Access is having a problem with?

Thanks so much for your help!!!
 
That's strange. Is there a chance that your Excel data is mostly numeric,
but one of the PartNumbers is alpha? (or vice versa)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
The PartNumber is formatted as "text" in Excel because there are alphas and
hyphens and such in the data. It looks like Access is putting it in as data
type "text", 255 chars. Is this a problem if I'm using this field as the
primary key? I have 47 of these files and only 5 of them are giving me a
problem. In one file, Access is reading the PartNumber in as an exponential
- I don't know how to get Access to not do that either.
 
Were this my issue, I'd first create a table in Access that had all fields
sized and typed as I needed. Then I'd import from Excel, probably into a
"temporary" table, since Excel data is rarely well-normalized. Finally, I'd
use queries to "load" the "permanent" table(s), normalizing the data by
using as many queries as I needed to parse the raw import data.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Alright, that's what I'll do.

Thanks!!!!!

Jeff Boyce said:
Were this my issue, I'd first create a table in Access that had all fields
sized and typed as I needed. Then I'd import from Excel, probably into a
"temporary" table, since Excel data is rarely well-normalized. Finally, I'd
use queries to "load" the "permanent" table(s), normalizing the data by
using as many queries as I needed to parse the raw import data.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...
 
Back
Top