Community
Showing results for 
Search instead for 
Do you mean 
Reply

Updating fields from excel csv file

New Member
Posts: 1
Country: USA

Updating fields from excel csv file

So our company uses a sql database and gives us weekly reports outputted in a comma or tab delimeted file.

I need to update a balance due field, so need to figure out how to just update that filed not enter duplicate names every week.

My layout is pretty simple with 4 fields being contact (first,last), customer id, balance due.

If I import all the fields will it creat duplicates or can I ask it to match a name and jsut update the 4th field?

kevin 

act!2006 xp 

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: Updating fields from excel csv file

You should have a look at itImport to do field level merges. ACT! only has the option to replace the field.

 

On that page, only the Advanced version is avalable for ACT! 2006. If you upgrade to ACT! 2008, you have the choice of the cheaper Basic version to do it the way you're doing, or the Pro version, which can read directly from the SQL data and not need the weekly reports.

 

With both of those, you can also get the Service Module option, which will run it automatically (daily, weekly, etc) and do all the work without user interaction

Nickel Contributor
Posts: 220
Country: USA

Re: Updating fields from excel csv file

Ive alsway had rtouble using both forst and last name fields when doing imports. Using Contact has always worked for me.  in your example, my excel spreadsheet would have 3 columns, contact, customer id, blance due.  This is exported into a comma sep file.

 

  • in ACT, use the import wizard.
  • select the file you wish to import from and click next.
  • select "contact records" in the next window and click next
  • now select comma for type.  I have headers in the first column of my excel files so i select NOT to import the first record. click next
  • do NOT use a predefined map file. click next
  • Now make sure your mappings are correct.  contact should have automatically matched with contact as well as cust. ID and balance. if they didnt, match them up here now. click next
  • In this next window, select "contact" to specify your merge options
  • now, on the left where it says contact records, select merge and on the right where it aso says contact records, select "do not add". and click OK
  • On a couple test rounds, you'll want to have "confirm each match" selected just so you can make corrections as necessary. Click next
  • now click finish and the merge will start

 

i had a couple issues at first tweaking the settings to get the merge to work correctly bit it didnt take long to get it wrking smoothly.  my biggest isse was using first and last name seperatelty in excel.  Im sure someone more experienced here can help more but these are the steps iwent thrugh to get it to work.  The important thing is to gert the merge function set up right, thats how you can avoid duplicate contacts.

 

Anyone else with a clearer explaination? or better way to use the import wizard with first and last name fields?

 

Matt

 

 

 

Matt Pulsts
Your Intown Handyman
Atlanta, GA
New Member
Posts: 4
Country: USA

Re: Updating fields from excel csv file

You need to go to Tools and then Preferences, Duplicate checking. You can to tell it what to scan for duplicates on. Then go to import, choose your file and when you get to the stage for duplicate changing in the wize you want to change it to if Source records do not match then merge the new data. You can also choose replace, etc. Hope that helps.