Community
Showing results for 
Search instead for 
Do you mean 
Reply

Avoiding duplicates when importing from Excel

New Member
Posts: 4
Country: United States

Avoiding duplicates when importing from Excel

If "Do not change" is selected when importing a Text delimited file from Excel, no data at all is imported.

If "Merge" is selected, duplicates are created.

Duplicate checking is ON with e-mail as first field to match records and Address 1 as second field..

Any suggestions for solving this?

Ludovic9

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Avoiding duplicates when importing from Excel

Questions:

Are there matches for all records?

Are you trying to update fields for records already in the database?

 

The ACT! import can only replace ALL fields, it can't update fields.

 

To just add new records, or replace entire records, see this ACT! Knowledge Base article -

http://kb.sagesoftwareonline.com/cgi-bin/sagesoftwareonline.cfg/php/enduser/std_adp.php?p_faqid=1520...

 

To import data and update speicific fields, you need an add-on such as itImport

New Member
Posts: 4
Country: United States

Re: Avoiding duplicates when importing from Excel

Hi Mike,

I've been successfully importing from Excel using the "Do not change" option in the Specify Merge Options screen for years using versions of ACT prior to 2010, to block importation of records already existing in the database. I use "e-mail" followed by "Address 1" followed by "City"" as the fields on which ACT should search for existing records.

There are not matches for all records. There may be some, which is why I am trying to prevent them being imported into ACT by selecting "Do not Change" in the Specify Merge Options screen.

However, as stated, none at all are imported when I do so, regardless of whether they are new or existing.

The only way I can get the program to import the new data from Excel is to select "Merge". However, I do not want to change anything in the existing record -just bypass it.

This problem has only arisen with ACT 2010

ludovic9

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Avoiding duplicates when importing from Excel

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Avoiding duplicates when importing from Excel

New Member
Posts: 4
Country: United States

Re: Avoiding duplicates when importing from Excel

Hi Mike,

Yes, I'm using .csv.

I seem to have deleted the demo file. I'll reinstall with the disk and give it a try.

Thanks for the suggestion.

ludovic9

New Member
Posts: 4
Country: United States

Re: Avoiding duplicates when importing from Excel

Mike:

Tried using DEMO database. Same result !

Try it for yourself by creating an Excel file of, say, six records, two of which exist in your database and four are new.

If you attempt to import choosing "Do not change"  (if records exist), none will be imported.

If you change to "Merge" (if records exist) all 6 will import, and may duplicate the existing ones.

if you can solve this, I'd be highly obligated to you.

Ludovic9

New Member
Posts: 10
Country: United States

Re: Avoiding duplicates when importing from Excel

To avoid duplicates when importing CSV records first import the CSV records into an EMPTY copy of the master database. Example: your database name is CUSTOMERS and you want to import a CSV file into it, first, create an EMPTY copy of your CUSTOMERS database and call it SHOW, then import the CSV file into SHOW, you now have 2 databases - CUSTOMERS and SHOW (CSV) now import the CSV records in SHOW database into CUSTOMERS database. Under 'Contact Merge Options' I selected ' if source records match destination records 'Do Not Change' and If source records no not match destination records 'Add'. I imported without any duplicates. Hope this helps.

 

Pat

CEC

New Member
Posts: 12
Country: United States

Re: Avoiding duplicates when importing from Excel

Thanks for the input. I tried it - it didn't work. With "do not change" selected for records that match, no records at all are imported - exactly as importing directly from Excel.  If this is operator error, I can't see what I'm doing wrong.

If it HAD worked, I would have regarded it as a tedious step to have to take - and one that ACT software people should fix  - as they should now in any case.

LB

New Member
Posts: 10
Country: United States

Re: Avoiding duplicates when importing from Excel

ACT 2009 v11 is the version we are using. Check the Excel CSV file, we get trade show lists in Excel format but not all data is correct so we 'clean it up' before saving as a CSV file as follows.

 

We make a copy of the list and work from the copy.

FIRST COLUMN CANNOT BE BLANK - NO BLANK COLUMNS - remove all blank columns

ONLY 1 WORKSHEET - REMOVE ANY ADDITIONAL WORKSHEETS (TABS) AT BOTTOM OF EXCEL LIST

COLUMN HEADINGS: Rename each column to match the ACT DB

 First Name/Last Name/Title/Company/Address 1/Address 2/City/State/Zip Code/Country/Phone/Email/Website

Add column for 'Phone Ext' - move phone ext data to this column

Add column for 'DBA' (if you need this info)

 

Errors we noted on trade show list: phone ext entered in phone column along with phone number, DBA entered in Address 2 and Address 2 entered in City etc. email missing @ or .com and website missing www or .com

 

Format Phone Column: click to highlight this column, open ‘Format’ click on Cells and select from menu ‘Special’ and select Phone Number

Format Zip Code: click to highlight this column, open ‘Format’ click on Cells and select from menu ‘Special’ select Zip Code or Zip Code+4 if list has zip code+4 entries. (this is a fix for missing zeros in the zip column)

 

Clean up list: sort by 'Zip' - do all records have a zip code, are any records missing digits, do same for Phone, Email and Website

Once the list is cleaned up save it as a CSV file.

 

Try a 'Test' list with only 2 or 3 names and do not import the first record as that is the column headings

 

Open ACT DB and import -

1)    Specify Source select 'Text Delimited'

2)    Specify Record Type click on 'Contact Records'

3)       Specify Import OptionsSelect field separator Click on ‘comma

Import the first record? DO NOT SELECT THIS, LEAVE BLANK

4)      Predefined Map Files, Click on ‘Do not use a predefined map file’ click ‘Next

5)   Contact Map - map all fields 

6)      Specify Merge Options.Specify your contact merge options here: Click on ‘Contact

Do not checkConfirm each match’

7)      If source records match destination records: Contact records: select ‘Do not change. 8)       If source records do not match destination records, select ‘Add

Click ‘OK’ to continue and import the CSV (Excel) records.

 

Since the database is already 'Blank' there are no records to 'match' or 'not match' so all records are imported.

 

ACT 2009 v11 will import all CSV duplicate records no matter what 'source' is selected, I tried all options and always ended up with 1500 duplicates!!  Once you have all the CSV records imported into an ACT DB and then import those records into the master ACT DB you should have no 'duplicates' this works for us.

 

Patc2436

CEC