Consulting

Results 1 to 10 of 10

Thread: Import .txt File with normal Space Delimiters

  1. #1

    Import .txt File with normal Space Delimiters

    Hello!

    I am trying to use a macro so that it will look for this .txt file and open it as a fixed width delimited so that I can have it edit and save as an excel.

    .txt = BlankContracts.txt (Wont let me attached says incorrect file, just a .txt file)

    So basically I will have an auto scheduler pull up the correct macro enabled excel form, this macro will then look for this file "BlankContracts.txt" on "C:\Users\Gill\Desktop" then open the txt file in excel as a fixed width delimited. Then I want it to search for the word "CONLST" and delete that row and the 4 rows after each time it finds the word.
    Last edited by GillsITWorld; 05-25-2020 at 10:35 AM.

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    I am not sure what fixed width means, per se. Some fixed widths are variable in location for the start but maybe a set width for field length.

    To attach a txt file in this forum, rename as a csv file and then explain that you did that it your post.

    As for finding the file, Dir() will tell you if it exists in the folder\path\filename.ext.

    You can record a macro for how it would import to Excel. Then save it as csv, xlsx, xlsm, or such.

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    (Wont let me attached says incorrect file, just a .txt file)
    Rename it with an additional .zip extension and try again
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    Ok, following your instructions, I have attached the file, with a false .zip file extension.

    BlankContracts.txt.zip

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    So ...

    Delete lines like 1-4 (CONLST line + 3) (you said 4)

    Lines 5 + 6 look to be header lines -- leave or delete?? If Delete, than CONLST+5 more lines

    Row 7 is data

    Row 8 (CUR GP%) seems to be data, but a different layout than row 7. How do you want to handle that?


    Capture.JPG
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Ok, so yes you are correct, I didn't even realize that Row 5 also had header info.
    So Delete lines (CONLST line + 3) for each time CONLST appears and the rest of the data should be fine as is.

  7. #7
    Just looking for follow up!

  8. #8
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    It's not so easy, some Item#s have 2 rows associated, others 3, nor is there a constant number of rows to remove at the top of each page.
    On Sheet1 of the attched is, I think, 90% of a solution; the problem for me was knowing how to split column C (Headed Merged) - I guessed at the widths and the headers.
    To finish this off properly I need to know how to split that column better. To that end, Sheet4 has the result before any splitting of Column C. I've copied column C of that sheet to the sheet Column to split, and highlighted the rows with the most text in. If you can copy that sheet and show me where to split the data and give me header names I can finish the job - perhaps you can use Text-to-Columns?

    This solution uses Power Query. The tables can be refreshed by right-clicking the table and choosing Refresh. Only it won't work when you get this file because BlankContract.txt is not in the same place on your computer. To correct this for Sheet1, right-click that table and choose Refresh - it will complain. Dismiss the message, right-click again and choose Table then Edit Query…. A new window will pop up where you need to click on the top step of the Applied Steps on the right, called Source,

    2020-06-18_221239.jpg

    then click on Edit Settings:

    2020-06-18_221446.jpg

    Then browse to your file, click OK.
    Then click Close & Load top left:

    2020-06-18_221709.jpg

    and you may just need to do another right-click and refresh on the table.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  9. #9
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Just looking for follow up, on follow up!
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #10
    HAHA! Thank you, with the world in chaos this has taken a little of a back seat compared to a lot of whats going on. However I'll be in office next week and will be able to play a little more!

Posting Permissions

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