Index or Primary Key Cannot Contain a Null Value

[mrmmickle1]

I have a .csv file that I am trying to import via:

External Data > Text File

My Delimiter is a | . My Text Qualifier is a "

I keep getting the error Index Or Primary Key cannot contain a null value.

However, I have manually gone through and removed all Indexes. There is also no primary key defined:

1. Is Access trying to identify a primary key for me?


2. Since I have not defined a primary key how can there be a null value in it?

I have tried to:

1. Add an additional AutoNumber Field (Indexed=True, No Duplicates) but this did not seem to fix the issue.
2. To Not import the fields that I suspect are being categorized as a primary key.

Does anyone have any ideas how to resolve this issue?

[ranman256]

somewhere you have a field, set to INDEXED = YES NO DUPLICATES

or you are putting a null in an indexed field.

[orange]

I agree with ranman -- some where there is a field with a null and the field is part of index or PK.

[mrmmickle1]

I'm really unsure why this is happening:

1. I delimited the .csv import in MS Excel.
2. External Data > Excel File
3. Imported a New Table fine with the following errors:

Primary Insurance Field was set to Number when it should have been AlphaNumeric
BirthDate Field had an incorrect date : 06/13/0185

So after finding these errors I did this:

1. Fixed the BirthDate in the .csv file
2. Delimited the file in Excel
3. This time when importing to a New Table I changed the Primary Insurance field to Short Text
4. The Table was imported with zero errors

So after this I then:

1. Changed the birthdate in the .csv file and saved it.
2. External Data > Text File
3. Set No Indexes
4. Set All fields the same as the .xlsx import
5. This time when I tried to use delimiter | and Text Qualifier "
6. The fields would not separate correctly
7. It looked like Access put an extra " in front of my first field in the preview and also added additional quotes?? (ie. "AB123-112|""Name""|""Name2""
8. I looked into the file and this is not how the data looks. It is presented like this: (AB123-112|"Name"|"Name2")
Why did Access add extra quotes????

My data appears delimited like this in the .csv file:

Field1|Field2|Field3
AB123-112|"Some Test"|"Some more Text"

Should there be quotes around the first field?? They are not present in the csv file.....

So I thought this might have been the issue. I then manually went in and added quotes to about 30 rows worth of data. Deleted all other information. Then tried to import just the 30 rows.... Unsuccessful. Fields will not separate properly.....

[orange]

Please show us jpg or copy and post some of the csv.

[rpeare]

Looks like your .csv file is not correctly identifying text strings if it is not putting " " marks around your 'field1' (AB123-112 should be "AB123-112")

There are a couple of simple things you can do
1. Set your delimiter to | but do not have a text qualifier
2. After the import run an update query to replace all " marks with a blank (replace(fieldname, """", "") I think is the right syntax)

or

Use vba to cycle through the .csv and remove the "" marks before you attempt to import using filesystemobject.

If altering your original data is a big deal, save the converted file to a new file name with an identifiable name so for instance original.csv becomes original_converted.csv.

Once the "" marks are removed from the file you should be fine to import as long as there are no conflicts of data types (I normally import as all text with an 'unknown' text file to start then start narrowing down the table until I encounter errors).

oh one more thing

you could use filesystemobject to insert "" marks around the first field as well then your import should be fine also

[NTC]

open your csv file in excel - then save as an excel file - then work with an excel file; in terms of field formatting and such it is just much easier to prep for import an excel file rather than csv

[mrmmickle1]

rpeare,

Original .csv data looks like this:

Chart Number|Last Name|First Name|Middle Initial|Street Address|City|State|Zip Code|Zip Plus 4|Home Phone|Work Phone|Social Security|Birth Date|Marital Status|Sex|Performing Physician
ZX15-613443|"LastName"|"Firstname"|""|"123 Main St"|"Knoxville"|"TN"|"53665"|""|"888-253-3253"|""|"423-53-2382"|"3/16/1974"|""|"Male"|"Doctor Name"
ZX15-613444|"LastName"|"Firstname"|""|"234 Main St"|"Greensboro"|"NC"|"53665"|""|"888-253-3253"|""|"423-53-2382"|"5/23/1974"|""|"Female"|"Doctor Name"
ZX15-613445|"LastName"|"Firstname"|""|"789 Main St"|"Boston"|"MA"|"53656"|""|"888-233-3233"|""|"433-53-2382"|"6/15/1982"|""|"Male"|"Doctor Name"

I tried to replace the quotes as you suggested. I did a find and replace. Now the data looks like this:

Chart Number|Last Name|First Name|Middle Initial|Street Address|City|State|Zip Code|Zip Plus 4|Home Phone|Work Phone|Social Security|Birth Date|Marital Status|Sex|Performing Physician
ZX15-613443|LastName|Firstname||123 Main St|Knoxville|TN|53665||888-253-3253||423-53-2382|3/16/1974||Male|Doctor Name
ZX15-613444|LastName|Firstname||234 Main St|Greensboro|NC|53665||888-253-3253||423-53-2382|5/23/1974||Female|Doctor Name
ZX15-613445|LastName|Firstname||789 Main St|Boston|MA|53656||888-233-3233||433-53-2382|6/15/1982||Male|Doctor Name

When I try to import the file without using a text qualifier the file looks like this in the preview:

Chart Number|Last Name|First Name|Middle Initial|Street Address|City|State|Zip Code|Zip Plus 4|Home Phone|Work Phone|Social Security|Birth Date|Marital Status|Sex|Performing Physician
"ZX15-613443|LastName|Firstname||123 Main St|Knoxville|TN|53665||888-253-3253||423-53-2382|3/16/1974||Male|Doctor Name
"ZX15-613444|LastName|Firstname||234 Main St|Greensboro|NC|53665||888-253-3253||423-53-2382|5/23/1974||Female|Doctor Name
"ZX15-613445|LastName|Firstname||789 Main St|Boston|MA|53656||888-233-3233||433-53-2382|6/15/1982||Male|Doctor Name

Once imported the Quotes are in the Chart Number Field.... (Strange since they are not present in the csv file)

NTC,

I have to agree. This was much easier with a regular excel file.

I can write VBA code to do what I need no problem. I was just trying to use MS Access' native functionality to perform a task. I thought it would be easier this way....

[mrmmickle1]

If anyone is interested I tried to work out this import issue yesterday for a few hours and was unable to figure it out. I thought it was bad data.... So today I tried to resolve the problem again using a different export with different fields and thought I would be able to resolve the issue with some help. Unfortunately, it looks like I will have to go with my original solution for importing information.

My Original Solution To The Issue

[rpeare]

dim fs
dim fInFile
dim fOutFile
dim sline as string

set fs = createobject("scripting.filesystemobject")
set fInFile = fs.opentextfile("c:\test\test.csv")
set fOutFile = fs.createtextfile("c:\test\test_output.csv")

do while finfile.atendofstream <> true
    sline = finfile.readline
    foutfile.writeline replace(sline, """", "")
loop

set fOutFile = nothing
set fInFile = nothing
set fs = nothing

didn't test it, there may be some qualifiers on the createtextfile method I didn't account for, but modifying a table record by record is way, way, way slower than fixing your original data source.

You could also do as someone else mentioned, open the csv, save it as an excel file, then import the excel file minus the "" formatting.

test code as written and it worked to remove the " marks from each line, from there it's just an easy bulk insert to a table (and quicker)

[mrmmickle1]

rpeare,

Thanks so much for the help. I appreciate you taking the time to respond.
I tried your method above and it seems to distort the .csv file. All " are replaced with "" but it seems that instead of leaving all of the data in Column A some of the data is moved to other columns. Which further distorts the data for the import. I was able to use this VBA to do something similar:

Sub Test()


Application.DisplayAlerts = False :Application.ScreenUpdating = False
Workbooks.Open Filename:="C:\Users\mmickle\Desktop\test.csv"
Cells.Replace What:="""", Replacement:=""
ActiveWorkbook.SaveAs FileName:= "C:\Users\mmickle\Desktop\test2.csv"
Workbooks.Close
Application.DisplayAlerts = True :Application.ScreenUpdating = True


End Sub

However, the data is still distorted for the import....

I have now been able to get all of the data into the Table with extra quotations ("). Is there an easy way to execute an SQL statement that will Update All fields at once? I have 55 fields..... So one field at a time would be rather tedious.

I have been fooling around with an SQL statement but can't seem to get it to do what I want (The Table is named Test):

SELECT REPLACE ([Test].*, """", "") FROM Test;

Not to familiar with SQL can anyone please point out what I'm doing incorrectly?

[rpeare]

I'd love to see an original file or a portion of one unless there are some funky hex characters in there my code should have worked to remove the " marks

there are ways to cycle through fields on a table for each record but you are, again, modifying 55 records individually times how many records you have which is extremely inefficient.

an example of the code to loop through fields in a table can be found here:

http://bytes.com/topic/access/answer...n-access-table

from here you'd have to

1. rst.edit
2. cycle through each field and remove the " marks
3. rst.update (to commit the changes)

can you show me the code you used of mine that you say is distorting the data and can you tell me what it looks like before and after.

[ssanfu]

I ran a couple of quick tests (A2010)

I copied the 2 examples from post # 8 into two different text files.
The first test was with the quote delimited text in a file I named TextImport.csv.
I let Access create new tables.


Using the Text Import Wizard, there is an order you must use to import the file.
1 Select the file; click OK
2 Select delimited; click Next
3 Select OTHER, backspace, enter the pipe (|)
4 Check the "First Row Contains Field Names"
5 Select the Text Qualifier " (Double quote) ; click Next
6 Click on each of the field to ensure that they are text. Note: The Zip field is indexed. You can set it to NO
7 Set the BirthDate Data Type to Date/Time; Click Next
8 I let Access add a PK; click Next
9 Click Finish.

Imported with No errors

The second test was without the quote delimited text in a file I named TextImport2.csv.

1 Select the file, click OK
2 Select delimited click Next
3 Select OTHER, backspace, enter the pipe (|)
4 Check the "First Row Contains Field Names"
5 Select the Text Qualifier NONE ; click Next
6 Click on each of the field to ensure that they are text. Note: The Zip field is indexed. You can set it to NO
7 Set the BirthDate Data Type to Date/Time; Click Next
8 I let Access add a PK; click Next
9 Click Finish.

Also Imported with No errors..


Apparently the "Other" Delimiter Option for "Type of delimiter" defaults to a space. Clicking the backspace key deletes the default space so the pipe (|) can be entered as the delimiter.
This is another reason spaces should NEVER be used in object names.

[mitch_pearce79]

Adding to Steve's first test, when using the Text Import Wizard you can hit the advanced button and save an Import Specification.
You can refer to this import specification later if/when you develop some code for routine imports, eg. DoCmd.TransferText acImportDelim, "Import Specification Name", "tblName", "C:\path.csv"

[mrmmickle1]

ssanfu,

Thank you for your input. After reading your post I found your steps exact to the procedure I was using. Just, to test it again I copied the data from post #8.

The 1st information set. (the way the original csv file is set up) results in this:

https://www.dropbox.com/s/khwli4lgyi...ample.PNG?dl=0

The 2nd information set (the one stripped of " ) results in this. NOW IT SEEMS TO WORK.....

https://www.dropbox.com/s/dcjz0sh3hl...ample.PNG?dl=0

Odd since it was so intent on adding extra quotations to the first field when I tried to do this yesterday....
On a side Note I used the actual file that I stripped the quotations from and the same thing happened (additional quotes appear):

https://www.dropbox.com/s/4bfkxk9k74...uotes.png?dl=0

This makes me think there may be some hidden characters in the export that are throwing off the import. When you copy this data and paste it into a file the characters could now be removed.....

Is there some kind of compatibility issue with the way that Excel Delimits data as opposed to the way that Access delimits data?
As I noted before:

1. When I delimit the file in Excel first.
2. Then save as an .xlsx
3. Then import the .xlsx there are no errors....

However, when I try to cut out the intermediary step..... No Dice. As NTC noted it seems much easier to do this from an .xlsx file....

Not sure what is going on but, it is certainly a major pain.

mitch_pearce79

Thanks for the tip. If I can ever get the process streamlined and working properly this will be helpful. As of right now I see no reason to save the Import Specification, since it doesn't work correctly.