Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Unanswered: How can I read a text file and write values into an excel sheet.

    Hi,
    How can I read a text file and write values into an excel sheet.

    I have a text file which I create when I run “JavaScript Code” to extract pdf bookmarks. The location of the text file is “C:\Users\makhtar\Desktop\WOOF_Stacey_CAT_master_B ookmarks.txt”

    I have an excel template “Bookmark Excel Template” at “C:\Users\makhtar\Desktop\Bookmark Excel Template.xls”

    How can I read every line of the text file and write values in the excel column.

    Each text file row consists of 3 values. Date: Description: Author
    Please note the values are separated by “:”

    The writing should start from A8 in Excel which should be 1 and then the 3 values in the sheet. Next row should be 2 and then the 3 values and so on.

    I am pasting text file data as I am only allowed to upload 1 file:

    TABLE of CONTENTS
    2010-10-19: Application
    for Determination of Catastrophic Impairment (OCF-19): Dr. Lisa Becker (MD), Omega Medical Associates
    2010-10-19: OCF-19 Report: Dr. Lisa Becker (MD), Omega Medical Associates
    2009-07-18: Ambulance Call Report: Ontario Ministry of Health & Long-Term Care
    2009-07-18: Ambulance Call Report: Ontario Ministry of Health & Long-Term Care
    2009-07-18: Triage/ Emergency Nursing Record: Stratford General Hospital
    1992-03-03: Emergency Department Flowsheet: St. Joseph's Health Centre
    2008-08-01: Inpatient/ One Day Stay Face Sheet: St. Joseph's Health Centre
    2008-08-03: Medication Administration Record: St. Joseph's Health Centre
    2009-06-12: Inpatient/ One Day Stay Face Sheet: St. Joseph's Health Centre
    2009-07-18: CT Cervical Spine: Stratford General Hospital
    2009-07-18: CT Head: Huron Perth Healthcare Alliance
    2009-07-18: Consultation Note: Dr. T. Jimenz (MD), Stratford General Hospita
    2009-07-18: RAD Chest: Stratford General Hospital
    2009-07-18: RAD Hand/ Wrist, Right Forearm: Stratford General Hospital
    2009-07-18: Specimen Inquiry: HPHA & AMGH Hospitals
    2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
    2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
    2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
    2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals
    2009-07-18: Stat Broadcast: HPHA & AMGH Hospitals


    Thanks

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    One way is to use a query table.

    Select A8
    Data > Import External Data > Import Data
    Browse to the text file > Open
    Delimited > Next
    Untick Tab
    Tick Other and type in :
    Finish

    Going forward, if the text file's contents change you can simply right click on one of the cells in the query table > refresh


    Another option is just to open the text file into a new workbook:
    File > Open
    Change the filter to all files
    Browse to the text file > Open
    Follow the Text to Columns instructions as above
    Copy and paste the data into your template

  3. #3
    Join Date
    Jan 2011
    Posts
    3
    Quote Originally Posted by Colin Legg View Post
    One way is to use a query table.

    Select A8
    Data > Import External Data > Import Data
    Browse to the text file > Open
    Delimited > Next
    Untick Tab
    Tick Other and type in :
    Finish

    Going forward, if the text file's contents change you can simply right click on one of the cells in the query table > refresh


    Another option is just to open the text file into a new workbook:
    File > Open
    Change the filter to all files
    Browse to the text file > Open
    Follow the Text to Columns instructions as above
    Copy and paste the data into your template
    I have been told not to use import. I have been asked to achieve this by VBA code.

    Thanks

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    If you go for option one, then the query table is set so all you have to do is refresh the query table. This can be set in the options to happen when the workbook is opened. Much better than VBA, right?

    If you go for option two, then you can automate the process using VBA. To get started use the macro recorder.

    VBA option 3 would be to use file I/O. But the only reason I can imagine that you would be forced to use this would be if it is a homework assignment.

  5. #5
    Join Date
    Jan 2011
    Posts
    3
    Quote Originally Posted by Colin Legg View Post
    If you go for option one, then the query table is set so all you have to do is refresh the query table. This can be set in the options to happen when the workbook is opened. Much better than VBA, right?

    If you go for option two, then you can automate the process using VBA. To get started use the macro recorder.

    VBA option 3 would be to use file I/O. But the only reason I can imagine that you would be forced to use this would be if it is a homework assignment.
    It is not an assignment. I work as a programmer but Excel is not my specialty. Importing data is a lot of work for our managers and they are not good in Excel as well that is why I am asked to acheive this with a click of a button.

    Thanks

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Option (1) achieves it without the need to click anything. It will automatically refresh the data in the workbook when it is opened. The only work is setting up the query table in the first place, which I have described how to do.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •