 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / March 2006"null values" when importing from excel
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 > I'm importing an excel file into access 2002 and I'm getting the error msg > [quoted text clipped - 5 lines] > 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 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! > The error message suggests that the field you are using from Excel as a > primary key in your Access table contains a null value. [quoted text clipped - 18 lines] > > 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? 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!!! > The field in the Excel table is not null but Access is putting it in as null > for some reason..... [quoted text clipped - 22 lines] > > > 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? 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 >I did what you suggested and it's still giving me an error - >"TypeConversion [quoted text clipped - 37 lines] >> > > "secret" that someone can share? Any suggestions as to what to look >> > > at? 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. > That's strange. Is there a chance that your Excel data is mostly numeric, > but one of the PartNumbers is alpha? (or vice versa) [quoted text clipped - 45 lines] > >> > > "secret" that someone can share? Any suggestions as to what to look > >> > > at? 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 > The PartNumber is formatted as "text" in Excel because there are alphas > and [quoted text clipped - 62 lines] >> >> > > look >> >> > > at? Alright, that's what I'll do. Thanks!!!!! > 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 [quoted text clipped - 73 lines] > >> >> > > look > >> >> > > at? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.