AboutScottGem Expertise I have been an IT professional for over 16 years. During that time I have accumulated a large store of knowledge and experience. This allows me to assist with a large variety of technology issues. My specialty is Database design primarily with MS Access. However, I can answer questions on many different IT related topics.
Experience I have worked in IT support for over 16 years. I have also helped people in cyberspace for a good portion of that time. I have been a volunteer staffer on Prodigy and AOL. I was the #2 ranked expert at AskMe.com (#1 in Technology) and am currently ranked at or near the top at Answerway.com and PointAsk.com.
Expert: ScottGem Date: 6/19/2007 Subject: Data entry to database
Question QUESTION: Hi Scott,
I'm looking for a solution to this problem: We receive data, mostly by email in either Word, Excel or Acrobat format. It consists of first name, last name, city, state/region, country, place (in a sporting competition), prizemoney. At present we are manually entering this data into our database and then publishing the sports result. We enter the last name and see if we can find a match (sometimes names received are mis-spelled, or the person matches but has moved towns so it needs to be edited here). If a match is found we select that one and enter the money info. If no match is found a new screen is launched where we type in the name, sex, nick-name & location info, then re-search and select it. It's mind-numbingly tedious. Do you know of any tools we can use to suck all the names off the received document, find matches/enter as new and then post to the sports result? Our database keeps track of all players and their lifetime winnings.
Thanks in advance.
Ed
ANSWER: The key here is patterns. As long as the input files follow a consistent pattern you can automate the entry. If it doesn't then manual is the best way.
For example, you can create an Excel template with columns for first name, last name, city, state/region, country, place, prizemoney. You can then link to that file, import the data and Append/Update your main table. Using Word, it would be harder since you would have to save it as a text file, then open it as text and read in each line. Acrobat would be worse since its harder to convert to text. However, there is a forms feature in Acrobat that would allow you to send out an form and just get the raw data back for import.
But if the format and the position of the data within the file will vary then manula is the better way.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
---------- FOLLOW-UP ----------
QUESTION: Hi Scott
Thanks a lot for taking the time to answer. I see what you mean - I was thinking more along the lines of some kind of semi-"intelligent" software that could "scan" the documents and lift off what are obviously names, then addresses, and so on and insert that info into the relevant fields of the our database. Have you come across anything like that?
Regards
Ed
Answer There are things like Cardscan that scan a business card and store the data in a Contacts database. But such software is customized for the specific app. I don't know of anything generic enough for your purposes.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
