Link to home
Start Free TrialLog in
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!
Avatar of byundt
byundt
Flag of United States of America image

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.
Yes, a sample text file is needed. Is text file like:
NSN:aaaaaaaNSN:bbbbbbbbbbbbbb
NSN:ccccNSN:ddddNSN:eeeeeeeeeeeeeeee
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.
Avatar of Christopher Wright

ASKER

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
Do you only need the data from the sections that have "IDENTIFY TO:" in them?

Do you need all of the amendments data?
What should happen to the extra references? (when there are more than three)
They can be combined in the same cell. I will attach an example of the finished excel file.
what was the workbook you posted, if not the result example?
Is the text file you posted a representative sample of the actual data?
Yes. The text file is a representation of the actual data.
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.
Also, I do need all of the amendments data.
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
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!!
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
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
@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
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'll post a comment in this thread when it is published.
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!!!