Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    New Lounger
    Join Date
    Oct 2013
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Read data into Excel from text file?

    Hi,

    I need to read a text file into Excel sheet starting from Client Name and ending at 50:Instalments to have each values import into cell.

    I have attached the sample text file and excel sample file.

    In case if some has any better way or idea please tell me.

    Hope some one will help me.

    Thanks.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 977 Times in 907 Posts
    The TXT file is not in a format that is easy to import into Excel.

    Do you want a record per page?
    Do you want a page listing all people, then another page for their data?
    Any other format?

    cheers, Paul

    p.s. I hope that's not real data in that sample!

  3. #3
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,721
    Thanks
    146
    Thanked 156 Times in 149 Posts
    If I had a file like that then I would use VBA to read it in. That's obviously not a simple solution, but your file (as Paul says) is complicated.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    If I had a file like that I would go back to the source that produced the txt file and see whether it had an option to 'export' it into a different format (like a spreadsheet)

    zeddy

  5. #5
    WS Lounge VIP access-mdb's Avatar
    Join Date
    Dec 2009
    Location
    Oxfordshire, UK
    Posts
    1,721
    Thanks
    146
    Thanked 156 Times in 149 Posts
    Why didn't I think of that, zeddy?

  6. #6
    New Lounger
    Join Date
    Oct 2013
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Thanks Paul T for the reply.

    Quote Originally Posted by Paul T View Post
    The TXT file is not in a format that is easy to import into Excel.
    That's true Paul but only available to me is this and i need to do it.

    Do you want a record per page? Well if that is possible then manually data cleaning will be easy for me.

    Do you want a page listing all people, then another page for their data?

    Any other format?
    Paul actually i am not in a position to say anything, infact if you have any way please help me.


    cheers, Paul

    p.s. I hope that's not real data in that sample! Yes, but the format is intact.
    One more thing i would like to mention that those xxxxx in data is not required to be appended in sheet.

    Hope to have a favourable reply.

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,164
    Thanks
    47
    Thanked 977 Times in 907 Posts
    Record per page would mean either manual copy / paste or code as access-mdb suggested.
    How did you get the data into the spreadsheet in the first place?

    My point about the data was you should never post personal data on the internet. If that was my data you posted I would be very unhappy.

    cheers, Paul

  8. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    fjohan,

    Although labor intensive because of inconsistent delimiters and columns, it can still be done. In the textbox, enter the complete path of the text file as shown then click the Run Code button. Assuming that the structure of the text file does not change, the text from the text file will be imported into the spreadsheet then assigned to cells as indicated. I have completed the top half of data to see if this is what you are looking for.

    fjohan1.png

    HTH,
    Maud

    P.S. Just noticed your note about the xxxxx
    Attached Files Attached Files

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    zeddy (2015-03-03)

  10. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,468
    Thanks
    30
    Thanked 61 Times in 57 Posts
    YIKES...you are TOO much, Maudibe!!!

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Maud

    A stunning piece of work that deserves a proper thank you.
    ...I formatted the date cells [M2], [AM2], and [BC2], a very minor change.

    zeddy

  12. #11
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi fjohan

    It would be great if you could post another example .txt file, just to make sure the format is consistent.
    I want to test another solution, but would like another example.

    zeddy

  13. #12
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    Zeddy,

    Coming from you, that is a great compliment.

    Thx

  14. #13
    New Lounger
    Join Date
    Oct 2013
    Posts
    23
    Thanks
    3
    Thanked 0 Times in 0 Posts
    Hi Maud,

    MARVELLOUS Maud,

    It's perfect and appends the data to cells, only the date 'Sanction Date' & 'Closure Date' gets converted, will have to maintain to date format.

    First record is appended properly and how to append the next record of text file to sheet, the text file contains more records.

    Many Thanks.

  15. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi fjohan

    The 'second' record in your original uploaded file is not exactly the same format as the first record section (the second one has a different number of rows).
    Does the number of rows vary for each Client????

    zeddy

  16. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,629
    Thanks
    114
    Thanked 645 Times in 589 Posts
    And will there always be only 2 clients per text file?

    Zeddy, what do you have cookin'?

Page 1 of 3 123 LastLast

Posting Permissions

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