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!
Christopher WrightDirector, Service DeliveryAsked:
Who is Participating?
 
CvDConnect With a Mentor Commented:
Ok. I've tried to hackup something. As far as I could see it worked well. I've used the datafile and spreadsheet you supplied. Please test and let me know.

There is a file conversion routine inside, but it's rather slow because you have to read character by character in order to find the eo-of-line character and convert it.

So my advise would be to use other simple methods to do the conversion before importing it. There are several tools available on the internet to do so. In case you do so, you should deactivate the call to the function "convertMe" in the sub "startImport" in module1.
POT---Reference-Numbers.xlsm
0
 
byundtCommented:
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.
0
 
hnasrCommented:
Yes, a sample text file is needed. Is text file like:
NSN:aaaaaaaNSN:bbbbbbbbbbbbbb
NSN:ccccNSN:ddddNSN:eeeeeeeeeeeeeeee
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
byundtCommented:
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.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
0
 
aikimarkCommented:
Do you only need the data from the sections that have "IDENTIFY TO:" in them?

Do you need all of the amendments data?
0
 
aikimarkCommented:
What should happen to the extra references? (when there are more than three)
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
They can be combined in the same cell. I will attach an example of the finished excel file.
0
 
aikimarkCommented:
what was the workbook you posted, if not the result example?
0
 
aikimarkCommented:
Is the text file you posted a representative sample of the actual data?
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Yes. The text file is a representation of the actual data.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
Also, I do need all of the amendments data.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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!
0
 
aikimarkConnect With a Mentor Commented:
@xfitguru

Yes. You have.  I was already in the process of writing an article about parsing and this problem is a good example to tackle.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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
0
 
hnasrConnect With a Mentor Commented:
Planning for data entry is very important fro future analysis.
That being said, this question is a nice one to tickle the brain.

It is a work for an article as aikimark suggested.

Here is a try to display records starting by NSN: and rest of characters.
Hope that will be of help to you.
In immediate window, type readText then enter.
Code is in Module1.

Your uploaded text file renamed D:\t.txt
Public Sub readText()
    'suthor hnasr
    'date Dec 26 2013
    Dim aChar As String ' character to be read from file
    Open "d:\t.txt" For Input As #1
    Dim f1 As String 'concatenated characters to search for a phrase
    Dim cCount As Long 'character count in file
    Dim arr() 'array to hold start of occurence, last element holds end of file count
    Dim nCount As Long 'holds occurence of NSN:
    Do Until EOF(1)
        aChar = Input(1, #1)
        cCount = cCount + 1
         
        If Asc(aChar) <> 10 Then
            f1 = f1 + aChar
        End If
        If InStr("NSN:", f1) = 0 Then
            f1 = ""
        Else
            If f1 = "NSN:" Then
                nCount = nCount + 1
                ReDim Preserve arr(nCount)
                arr(nCount) = cCount - 4
            End If
        End If
    Loop
    Close (1)
    nCount = nCount + 1
    ReDim Preserve arr(nCount)
    arr(nCount) = cCount
    Open "d:\t.txt" For Input As #1
    For nCount = 1 To UBound(arr) - 1
        Debug.Print
        Debug.Print nCount, arr(nCount), Input(arr(nCount + 1) - arr(nCount), #1)
    Next
    Close (1)
End Sub

Open in new window

TransposeFasteners-2.xlsm
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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!!
0
 
CvDCommented:
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
0
 
hnasrCommented:
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
0
 
aikimarkCommented:
@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.
0
 
Christopher WrightConnect With a Mentor Director, Service DeliveryAuthor Commented:
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!!!
0
 
aikimarkCommented:
I'll post a comment in this thread when it is published.
0
 
Christopher WrightDirector, Service DeliveryAuthor Commented:
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!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.