Importing only unique records from excel tables 1

[JayRo71]

I imported an excel workbook file with 6 columns and 1000 unique rows (=1000 unique records). Using my newly imported table, I created an index using 3 fields and set the index value to unique. I know it to be true that the combination of all three fields is unique in each record and this is confirmed because access allows me to save the table with the new index. In theory, if I try to import and append the exact same table that I just imported to itself, there should be no records imported because none of the records would be unique. There appears to be problem with fields that have blank values or have an inconsistent alphanumeric pattern in the data. For example if most records have an account number that is just numeric and there are a few records that are alphanumeric, Access imports the alphanumeric records despite the fact that they are not unique.

I did some reading online and this has something to do with the way Access guesses at the data type. I have made sure that the account number field is formatted aa General in Excel and formatted as short text in Access, but it does not appear to make a difference. Even when I do not include the account number field in the index, the same records tend to import, despite not being unique.

Any suggestions?

Thank you...

 

[dhookom]

Formatting is typically unimportant and should be ignored except in appearance on a form or report. Data types are important. Excel values can easily contain unseen spaces or other characters before or after what you actually notice. I would check these in Excel first or import into a new table and compare.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JayRo71]

Thank you dhookom. The thing that is troubling is that I am importing the same excel file twice. One time as an external import, create new table and one time as an external import append to existing table. I would think that the second import would not import any records because they are the exact same record set that I just imported and the index is set to unique. The second import does ignore most of the records, but it imports any records that have a blank field or any records with unusual data.

Let me see if I can spell it out a little better here.

1) I have an excel table with 10 records and there are the following 3 columns in the table. txtFirstName, txtLastName and txtAccountNumber. All the cells are formatted as General in excel.
2) Next I go into Access and I create a table by importing the external data excel file.
3) I open up my new table in design view and I set the data types of all 3 fields to short text and I create a unique 2 field index that has both the txtLastName and txtAccountNumber.
4) I import the same excel table and append it to the table I just created. None of the 10 records should import because of the unique index and I should still be left with 10 records in the table.

Instead, maybe 2 of the 10 records import and an error table is created. When I look at the error table, there are 2 records in it. The two records that are creating the error are doing so because 1 has a blank field and the other has a field who's account number value is inconsistent with the other 8 records. The 8 account numbers may have values like 1112, 3416, 3215, 8899... etc. The two that create the error and actually import might have values like West8888 & East5364. I am not sure why it matters because the account number field has a short text data type.

Thanks...

 

[dhookom]

Of you feel you are importing duplicate records based on primary key fields, I would create a totals query subgroups by the primary key fields and count the duplicates.

I almost always import into a fresh table and then process the records.



Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[JayRo71]

I think you might be on the right track, I'm just not sure if I understand what you are suggesting. The index that should be prohibiting duplicates is not the primary key. I have it setup so that Access is creating the Primary Key. I will try importing the data into a fresh table and running an append query instead of appending the table directly during the import. I think I have tried that already, but I will try again. Thank you for your effort... much appreciated. It is just so weird that I can not prevent duplicate records from appending to the table using a index set to unique. One would think that if you are appending a table to itself and the index is set to unique records only, they would not import. Maybe the trick is to find duplicates after the import is done and delete them and maybe that is what you were getting at with the totals query subgroups. Thanks again...

 

[SkipVought]

Hi,

Seems you have a dangerous situation...

For example if most records have an account number that is just numeric and there are a few records that are alphanumeric, Access imports the alphanumeric records despite the fact that they are not unique.

When you mix NUMBERS in a column with STRINGS, you will not get good results!

Go back to Excel.
1) Change the NumberFormat Of this column from General to Text. But that's not enough. Changing the cell Number Format does NOT change the underlying value. All your numbers are still numbers. If you had changed the Number Format BEFORE you entered your data, you would be okay. So you need another step.
2) Edit each cell containing a number and hit ENTER. This will convert 1234, for instance to "1234" TOTALLY DIFFERENT VALUE.

You could also for that in a short macro like this

Sum MakeText()
'This sub will make each cell NumberFormat TEXT, in the CURRENT SELECTION 

   Dim r As Range

   For Each r In Selection
      With r
         .NumberFormat = "@"
         .Value = .Value
      End With
   Next
End Sub

Be sure to SELECT the column of DATA you want converted from NUMBER to TEXT. SELECT DATA ONLY!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[JayRo71]

Skip Vought, thank you for your answer and I'm sure your solution will work as I can tell you have come across this exact problem before. I'm going to try it out right away and report back. Sorry it took me so long to respond and I am going to set this up to send me notifications when someone responds. I truly value the time you donated in trying to help me out...

 

[JayRo71]

SkipVought, you absolutely nailed it and thanks for the VBA refresher. I have not dabbled in that since year 2000 and even then, I would only consider myself a self taught novice in Access, VBA and SQL. I understood all the concepts but could never put together a completely functional executable.... What little I knew is coming back to me... Thanks!!!

 

[SkipVought]

Glad you got it sorted out.

Here's a related FAQ I wrote several years ago on this very subject..
faq68-6659

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[Andrzejek]

I guess it is OK to use Access’ build-in functionality, like "Get External Data - Excel Spreadsheet" (wizard?) if it does what you need.
But it is not a big problem to write a little bit of code yourself to accomplish the same when you encounter the issues you had. And this way you are in charge of how it functions. You can either ‘push’ data from Excel to Access, or you can ‘pull’ it from Access’ side. You can use appropriate variables to keep data as numbers, or text, or date, and enforce all and any rules you wish. And when something goes wrong, you can easily spot the problem in your code.


---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

I guess it is OK to use Access’ build-in functionality, like "Get External Data - Excel Spreadsheet" (wizard?) if it does what you need.

Yes, however Get External Data needs ALL text data in columns that have rows that contain all numeric values. So the same dilemma remains.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!