1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

excel: Read from a text file and put in a cell

Discussion in 'Business Applications' started by ajrobson, Aug 25, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Excel is my weakest area of office programs so I was wondering if anyone can tell me if this is possible and possibly offer any advice on how to implement it.

    Problem
    I have a spreadsheet with a list of names and dates and what that person is doing on each day. I also have a text file of whenever each person is away, which I export from a calendar. What I want to do is have excel read the text file and write to the correct cell based on what the text file says.

    Example text file
    Tom - Holiday 13/07/2010 14/07/2010
    Harry out 14/08/2010 16/08/2010

    Attached is an example spreadsheet.

    So based on this text file I would want to fill in cell C3 with "Holiday" (the second date is the first return date) and cell D5 and E5 with "out"

    Is this possible to do?
     

    Attached Files:

  2. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    I would need to see what your exported text file is laid out like in order to help you with this.
    I am sure that what you want is possible, but we need more info to help with the specifics.
     
  3. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Thanks for the reply. Attached is the text file. I basically wrote a script to export an outlook calendar to a csv file, then it opens it with excel and strips all the relevant details and saves them to a text file.
     

    Attached Files:

  4. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    It would appear you have 2007 (or later) and that version of Outlook has the ability to export the data to a csv file built in. See here (about 1/2 way down the page):
    http://social.msdn.microsoft.com/fo.../thread/0be4aceb-aa6d-4db7-9c0d-0a0a357bff9e/
    Your text file is tab delimited rather than a csv file. Not a problem for Excel, however.
    I take it from your first post that you want the names in column A, the reason(s - will a person ever be out for more than one reason or should an extension be treated as a new event?) in column corresponding to the date that the event begins and then just "Out" until it is past the corresponding date?
    Will you have a different sheet for each month?
    I am no coder, but I am sure someone can help you.
     
  5. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Yeah 2007 can export to a csv - I wrote a script to automate the export and then just get the info I would need.

    This sheet is extended to go for 1 year I just created a quick example, Column A will contain all the names and for each item I get from the calendar I would want to fill in all the cells between the start and end date with whatever it says on the text file e.g. Harry Out would put the word "Out" on all the cells from the start date to the end date.
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Did you intentionally leave out the - between name and the text?

    Code:
    Subject	Start Date	End Date
    Tom [B][COLOR="Red"]-[/COLOR][/B] Holiday	13/07/2010	14/07/2010
    Harry out	14/08/2010	16/08/2010
    
     
  7. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    No thats just how it was on the clanedar. So for example Tom created an all day event called "Tom - Holiday" whereas Harry named his "Harry out"
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
  9. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Any ideas how to solve it?
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Yes I do, but I just picked up the post.
    I'm at work now and won't be able to work on it seriously 'till after dinner and I don't have 2007 here even though the basics will work in 2003.
    I'll get back to you asap with some ideas and how I think it could be doen.
     
  11. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    thank you that would be a great help!
     
  12. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Hi have you managed to think of some ideas yet?

    I know a way to do to it its just trying to implement it.

    My idea was to use autoit to read each word of the text file, as each line only has three parts to it (name, start and end) I would then store each in a variable. Then it would find the name on the spreadsheet and find where the start date matches a cell on the sheet it would then have two cell refs where it could fill in the cells - the third variable would be used to stop filling in cells.

    The thing is I have no clue where to start implementing this!
     
  13. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Sorry ihas taken so long.
    I'll get at it later this afternoon.
    If you have another text file with some alterantives, I think Ihave the simple solution for you.
     
  14. ajrobson

    ajrobson Thread Starter

    Joined:
    Aug 2, 2006
    Messages:
    283
    Thank you. I have just run my code again. Attached is the CSV and the text file my script generates after deleting all the details I'm not interested in from the CSV file.
     

    Attached Files:

  15. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,559
    First Name:
    Hans
    Okay, got it,

    I'll do some coding and get back to you this weekend
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/945641

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice