Avatar of Christopher Wright
Christopher WrightFlag for United States of America asked on

Parse A Text File Into An Excel Spreadsheet

Please help anyone.  I have a text file that is over 6MB and has proven to be quite difficult to parse.  It does not have the same format throughout but is close.  The beginning column header is "NSN:" and everything following this should remain on the same row.  The next row should start at the next instance of the word "NSN:" I have been trying over and over to figure this out but to no avail.  I have to get this out to my company sometimes tomorrow if anyone can help me out.  I am a total newbie at a scripting and this is a first for me.  Please help, anyone! I have provided a portion of the text file that needs to be parsed as well as the VBA script I have so far.  I am almost certain that there is a better way to do this.  Thanks in advance for all of the help!
Microsoft ExcelVisual Basic ClassicVB Script

Avatar of undefined
Last Comment
Christopher Wright

8/22/2022 - Mon
byundt

Could you please post a sample text file. It doesn't need to be 6 MB in size--five or so records should be sufficient for testing.
Hamed Nasr

Yes, a sample text file is needed. Is text file like:
NSN:aaaaaaaNSN:bbbbbbbbbbbbbb
NSN:ccccNSN:ddddNSN:eeeeeeeeeeeeeeee
byundt

When you upload a file, you need to:
1.  Click the Attach File link
2.  Click the Browse... button to find the file in your computer or network
3.  After you have selected the file, click the "Open" button in the file browser
4.  Click the Upload File button in the Experts Exchange Comment (or question)
5.  Add some descriptive text in the field with light gray font that says "Enter a brief description of your file (required)"

Omitting step 5 is a common mistake. I usually say "Sample file" because the field doesn't display very many words.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
Christopher Wright

My apologies everyone. I was trying to do post this by phone but obviously failed.  Here are the missing pieces. Please accept my apologies for leaving those out everyone.  :(
Test-File.txt
TransposeFasteners.xlsm
aikimark

Do you only need the data from the sections that have "IDENTIFY TO:" in them?

Do you need all of the amendments data?
aikimark

What should happen to the extra references? (when there are more than three)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Christopher Wright

They can be combined in the same cell. I will attach an example of the finished excel file.
aikimark

what was the workbook you posted, if not the result example?
aikimark

Is the text file you posted a representative sample of the actual data?
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
Christopher Wright

ASKER
Christopher Wright

Yes. The text file is a representation of the actual data.
ASKER
Christopher Wright

The workbook I originally posted contains the VBA script. The format is acceptable but I only sent the last workbook as an additional reference. I am okay with leaving the format the same as the original file I posted. Sorry for any confusion.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Christopher Wright

Also, I do need all of the amendments data.
ASKER
Christopher Wright

Have I provided everything you need? Sorry, don't mean to sound pushy, I was only checking to make sure that I provided all info necessary here. Thanks again for all of your help!
SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Christopher Wright

Awesome aikimark.  I am glad that this can pose a good example here.  I am eager to see your article.  Please let me know once you've posted it. Thanks
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Christopher Wright

Thank you for the script hnasr.  I am able to read the information in the Immediate Window, however, is there a way to have this parsed on the spreadsheet?  Thanks again!!
CvD

Sub importMe()
  Dim arr As Variant
  Dim lFree As Long
  Dim i As Long
  Dim j As Long
  Dim nextRow As Long
  Dim nextCol As Long
  Dim inputLine As Variant
 
  lFree = FreeFile
  nextRow = 0   ' The first row number where to start inserting on the target sheet
  nextCol = 0
  Open "datafile.txt" For Input As #lFree
  Do While Not EOF(lFree)
    Line Input #lFree, arr
    ' If next row does not start with NSN: lower the row counter to make
    ' sure we stay on the same row. A shift in column is assumed in this case.
    If nextRow > 0 And Mid(a, 1, 4) <> "NSN:" Then
      nextRow = nextRow - 1
      nextCol = nextCol + 1
    End If
    arr = Split(inputLine, "NSN:")
    j = IIf(Trim(arr(0)) = "", 1, 0) ' Prevents a first blank line
    For i = j To UBound(arr)
      Worksheets("sheet1").Cells(nextRow, nextCol) = arr(i)
      nextRow = nextRow + 1
      nextCol = 0
    Next
    arr = ""
  Loop
  Close #lFree
End Sub
Hamed Nasr

When the text file was inspected, it appeared to be a stream of text.
After reading the file character wise, it showed being stored as lines.
Hence CvD's comment is fine for one column NSN
You need to repeat the process many times for each column name. You need to watch for repeated column names in same line.

Here is a file modified to store the NSN:.... in column 1.

type readtext in immediate window and enter.
TransposeFasteners-3.xlsm
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aikimark

@hnasr

This file was probably produced in a Unix/Linux system, where the lines are terminated with a Line Feed.  In Windows, there would be a Carriage Return - Line Feed pair of characters.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
aikimark

I'll post a comment in this thread when it is published.
ASKER
Christopher Wright

Thanks CvD!! This worked perfectly.  Might I add this it processed much quicker than I anticipated.  I sincerely appreciate your help here!!

Aikimark,
I am still looking forward to your article pertaining to this. I am curious to see your findings on this as well.  Thanks for all of your help and support here too!

Have a Happy New Year!!!
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck