#  Other Applications & Softwares  > Access Tables & Databases >  >  Excel Files Imported into Access problem: Primary Key Contains NULL Value...I can't fix

## Doc Snowfox

Hi there, experts. 
I am still climbing the Access ladder of knowledge, and am baffled now for the second day with the common error, on the Access "Import Spreadsheet Wizard: 'Index or primary key cannot contain a Null value.' "

I have read all I can from Forum searches (here and the web) to try to decipher where my errors in my two Excel files are. I will add them to the string here. I want to use my own Primary Key to have the relationship in the Access queries to have the "New Emp Number" in the Clone User file link to the "New Address2" data in the Clone Access Asset Retrieval 07 24 13 file. 
I have done something wrong, or have multiple things wrong. I have tried to Format these two data field columns as Numbers, Long Integer or Double, but it doesn't matter. I still get the same reporting problem: Access "Import Spreadsheet Wizard: 'Index or primary key cannot contain a Null value.' "


Can someone steer me in the right direction? It's probably fundemental issues and mixed formatting. But I give up. I cannot figure it out. I am reading everything I can to move on, but I am baffled by too many potential issues here. 

HELP???

----------


## Doc Snowfox

I cannot find what's wrong in the Primary Key choice, and it won't bring over the data per my choice of Primary Key, unless I let Access assign one for me. And that defeats the point of these two tables. 
Do I need to format my column choices for Primary and Foreign Keys to a number (in Excel before importing the tables) or as Integers, Long Integers or Double in the Import process? I cannot tell what the issue is...Help please?
I am a beginner at Access. Trying to learn, as fast as possible. Thank you in advance.

----------


## MarvinP

Hi Doc,

On the Import Spreadsheet Wizard Dialogue, do you select your number columns and tell Access they are numbers and not short text?

----------


## Doc Snowfox

Well yes. The question I have though, is do the number columns need to ALL be the same as as Integers, Long Integers or Double? Other than details of 16, 32 bit or whatever, I don't know the difference. Though, the larger the characters needed to hold the number, apparently I need to move up from Integer, to Long Integer or to Double. Does that sound right?
Have I answered your question? 
If I pre-format the numbers in Excel as "Number" for the columns, I still need to select a Number when performing the Import Spreadsheet Wizard Dialogue, correct? Do they all need to be the same type, or as I am thinking I read somewhere, they are "compatible" ?
Thank you. I am now under a deadline to get this fixed.

----------


## MarvinP

Yep, they need to be the same type and the Integer vs Long or Double in the Access db are carving out space to put those imported numbers.  If you define a field as Integer in Access and try to import a huge number (that doesn't fit in the integer space) you get the import error message.  If you try to import a Text Field into that integer spot you get those errors also.  

It looks like your import didn't work and you are getting errors for Row 2 in two fields.  Make your fields in Access Double Type and see if that helps.  If not then look for text characters in your numbers.  I've seen the letter "oh" instead of zero that cause these errors.  I've also seen a hidden character 160 in CSV files that create these import errors.  I don't believe you have these in your Excel file but....


Read http://msdn.microsoft.com/en-us/libr...ffice.10).aspx to see if

----------


## Doc Snowfox

MarvinP,
Yeah, I thought the Primary Key column data and Foreign Key data needed to be compatible as numbers. I will change them all to Double, as you've recommended.

And yes, previously in the Primary/Foreign Key fields I have identified three fields as having some alpha characters, and so they are changed to I think the number 1, 2 and a 5 or 6-digit number, to correct that. However, there are still some or one, somewhere, that is still wrong. At least that's my guess as to why I am still getting the Null error. And other than the same manual review, I don't know how to find these error fields...is there an automated way to discover them?

Also, I am running out of time now. I need to complete this db work before end of day, as I've been tweaking it all week and still haven't found the fix. Yeah, always a deadline.
I really appreciate your input. I will try this all again on the real data file, as these files that I've included are just modified dummy clone files from the much larger originals. One file is over 700 rows and the other is >900 row fields. I need to get the reporting part going before EOD today.
I'll read your hyperlink info next.

MarvinP, thank you! I'll check back shortly for any other recommendations that you might have offered.

----------


## Doc Snowfox

By the way, MarvinP, you said wrote "It looks like your import didn't work and you are getting errors for Row 2 in two fields." 
How did you find that or know any quantity? 
Curious how you know that, as well as wanting/needing to learn.

Also, is using a real Zero, "0" okay in the Primary Key fields, as it is a number in a VMX collumn I believe, in one of my files??? Or does the Zero also kick the Null error message?
Thanks.

Thirdly, do you know if I can somehow do what I am trying to do just in Excel, using the VLOOKUP function? I do not know how to use that Search in different Worksheets. If asking here isn't right, I'll go over to the Excel forum side (probably more appropriate, over there).

----------


## MarvinP

Row 2 in two fields??  I looked at a table Database7.zip you had in your access file that was all the import errors. It claimed it had two problems importing row 2 and listed two different fields.  Access is telling you which rows of your Excel file were problems and even which fields weren't imported correctly.  Access is cool if you know where to look.

----------


## Doc Snowfox

Ah yes, I have read that. Sorry, didn't understand.

I have used that to re-read the fields, and I could only "see" which were the locations, but when I repaired, meaning confirmed they were indeed number fields and ran the Import Spreadsheet Wizard Dialogue, and made sure the columns were compatible, it didn't fix anything. So I've been guessing it's something I can't manually see in reviewing...like a formatting problem, or something behind the scenes as gurus sometimes put it. 

I must be missing something. Maybe a fundemental [rule] I am not aware I need to follow?

----------


## MarvinP

OK Doc, I have the answer....

When you are doing the Import Wizard Dialog, it asks which sheet (or Range) you want to import.  I show names in this dialog.  One is a Report and the other is Sheet1.  I'd bet that importing the report tries to import TEXT.  You need to select the Sheet1 instead of the report.  

Seems like an easy answer but I think that is it!!!

----------


## Doc Snowfox

MarvinP,
I did as you recommended, and before doing the Import Wizard Dialog, checked all the formatting in Excel so that numbers were numbers, and text (unless Dates) were text...if had Dates, made them Dates to stay.
So then copied file, and started fresh. I also deleleted any blank rows as a trial import still showed an error message: 'Index or primary key cannot contain a Null value.' So, even though I cannot "see" any values in any other fields whether rows or columns, I deleted them all if they appeared to be blank, in case were not visible values. Did the Import Wizard as you recommended. Went ok. Went into Design View, Properties, made relationships, made a Primary Key as I had intended all along, ran it.. Ba-Zinga! All worked.
So, having taken your advice and making these other changes, I am not sure where the errors were sourcing from, but they're gone. Thanks a ton, MarvinP!!!

----------

