The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
Pulling data from Word to Excel
FrostyAlphaWolf
Registered User regular
Registered User regular
One of my new duties at work has been to handle checking-in/out keys. It's not a very organized system at the moment, so I'm trying to get some information sorted into an Excel file for easy access. What I have right now is a .txt file with the report of which keys are overdue. I'll put a sample of a report below.
The main information I want from the report is the key number (KEYTLC1082), date it was due (or "NEVER" in some cases), and the person who has it checked out (John Smith).
I started entering the data manually until I realized there were about 65,000 pages in this report (and probably more since the system can't handle putting out more than 65,535 pages at once). What I'm wondering is if there is any way I can use a macro or program to search and pull these 3 fields from the txt file and put them into Excel (or something I can easily import into a spreadsheet). Does anyone know if Excel and Word can even work with each other like this?
Dr. John Smith
Anthropology
CAMPUS
"1ST OVERDUE NOTICE"
The following Library materials are overdue. Please return them
as soon as possible.
Thank you.
1 call number:KEYTLC1082 ID:KEYTLC1082
Teaching Station Key (1000-1099)
due:12/31/2009
The main information I want from the report is the key number (KEYTLC1082), date it was due (or "NEVER" in some cases), and the person who has it checked out (John Smith).
I started entering the data manually until I realized there were about 65,000 pages in this report (and probably more since the system can't handle putting out more than 65,535 pages at once). What I'm wondering is if there is any way I can use a macro or program to search and pull these 3 fields from the txt file and put them into Excel (or something I can easily import into a spreadsheet). Does anyone know if Excel and Word can even work with each other like this?
[SIGPIC][/SIGPIC]
FrostyAlphaWolf on
0
Posts
if your document has set keywords (like ID:KEY or "call number") you could make a model to grab the data out of it with some like monarch pro http://www.datawatch.com/_products/monarch_pro.php there may even be free versions of similar tools available.
Blizzard: Pailryder#1101
GoG: https://www.gog.com/u/pailryder
I'll definitely check out something like Monarch; it seems like a scripting program is going to be what I need.
Live - MrObersmith
PSN - Obersmith
How many physical keys are there? 65,000 lines seems too excessive to be useful. Does this just list the notices that were sent out? like, if the same key has changed hands multiple times, and each time it was late, there are multiple notices in the file? If you want a history like that, Access is probably a better solution than excel. but if you just want a list of keys, and their current owner and status, excel would work.
Find out who you call when something goes wrong with that software. When you get in touch with that guy, tell him you need a report pulled from the database. There is probably a backend being maintained by invisible nerds that can pull this data insanely easily.
The software itself can only generate a txt file for reports, but I feel like there is a database keeping all this data organized behind the program. I'll check with the head of IT for the library and see if he knows how that works.
Luckily, we only have around 3,000 keys total to keep track of. The 65,000+ pages are mostly filled with extra spacing and page breaks (each record is formatted almost exactly like the example I put in the OP).
My goal is just to keep track of the key number, who it's currently checked out to, and the date it is/was due back. Instructors like to lose the keys or lend them out to grad students without thinking about it, so as long as we can hold one person responsible for the key we don't really worry about what they do with it.