Importing only unique records from excel tables
Importing only unique records from excel tables
(OP)
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...
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...
RE: Importing only unique records from excel tables
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Importing only unique records from excel tables
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...
RE: Importing only unique records from excel tables
I almost always import into a fresh table and then process the records.
Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016
RE: Importing only unique records from excel tables
RE: Importing only unique records from excel tables
Seems you have a dangerous situation...
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
CODE
Skip,
for a NUance!
"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!
RE: Importing only unique records from excel tables
RE: Importing only unique records from excel tables
RE: Importing only unique records from excel tables
Here's a related FAQ I wrote several years ago on this very subject..
FAQ68-6659: When is a NUMBER not a NUMBER?
Skip,
Just traded in my OLD subtlety...
for a NUance!
"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!
RE: Importing only unique records from excel tables
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
RE: Importing only unique records from excel tables
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,
Just traded in my OLD subtlety...
for a NUance!
"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!