Importing non-delimited text file into excel

[Miteshkg]

Hi All,

I've a specific requirement where in I need to import text file which is non-delimited and contents should be copied to Excel in different columns...

Text file contents is as below.

Need VBA code to copy this in Excel different columns like

DAte and time should go to Column A
Name of the person before : should go to Column B
and contents between : and new date start [ should go to Column C

Please reach me on +91 98197 23533 or mitesh.golwala@gmail.com

[‎12/‎30/‎2015 10:01 PM] Srinivasan Varadaraj:
Dear All,
We have report of “TFS application tier connection shows packet drops”. We have initiated a P2 INC3515622 & opened the P2 Bridge. I will be the incident manager.
The SMT team is contacting resources to join the P1/P2 incident bridge.
Business Impact : 230 users impacted.
Call In: 1-855-488-1100
Participant Code: 20482240
[‎12/‎30/‎2015 10:10 PM] Kamlesh Chaudhari:
we created ticket with GCX ticket no is 1872
[‎12/‎30/‎2015 10:22 PM] Srinivasan Varadaraj:
Kamlesh from CMC Joined the call
Anand from L2 Network joined the call
[‎12/‎30/‎2015 10:23 PM] Srinivasan Varadaraj:
Team is currently waiting for an update from the GCX vendor team.
[‎12/‎30/‎2015 10:47 PM] Srinivasan Varadaraj:
Dear All,


L2 Network team joined the call and working.
Ticket is raised with the GCX vendor, ticket #: 1872.
Further troubleshooting is in progress.
[‎12/‎30/‎2015 11:14 PM] Srinivasan Varadaraj:
Dear All,
L2 network team has provided technical information which was requested by GCX.
GCX vendor is working on the issue.
At the moment the issue is reported only for TFS application.
No ETR provided by vendor GCX.
[‎12/‎30/‎2015 11:25 PM] Kamlesh Chaudhari:
L2 - Network tem is providing information about VPLS to GCX
[‎12/‎30/‎2015 11:58 PM] Srinivasan Varadaraj:
Dear All,
L2 Network team confirmed that the issue is only specific to Pune VPLS.
Vendor GCX is working on the issue.
At present no ETR provided by vendor GCX.
[‎12/‎31/‎2015 12:29 AM] Srinivasan Varadaraj:
Dear All,
GCX Vendor, L2 network team and Pune It support team are working on the issue.
No ETR provided.
[‎12/‎31/‎2015 12:56 AM] Anand Kumar:
With Sachin we verified the connectivity like this
SWIDIS01 ge-1/0/0---------------LAN0_0 Riverbed Wan0_0--------------VPLS device
Asked him to replace the cable between Riverbed and VPLS device
Riverbed Wan0_0--------------VPLS device
[‎12/‎31/‎2015 12:59 AM] Srinivasan Varadaraj:

 

[Steve_R]

Reading your file using LineInput as follows:

If you file contains header data or blank lines, you'll need to write code to ingnore those lines

Option Explicit

Sub ParseFileToCells()

    Dim myFile As String
    Dim strLine As String
    Dim i As Long
    Dim timeDateVal As String
    Dim strName As String
    Dim strMsg As String
    myFile = "c:\temp\TextFile.txt"

 i = 1
    Open myFile For Input As #1   ' Open file.
    Do While Not EOF(1)   ' Loop until end of file.
        Line Input #1, strLine   ' Read line into variable.
        strLine = Trim(strLine)
        If Left(strLine, 1) = "[" Then    'new record

            If i > 1 Then
                Range("C" & i).Value = strMsg
            End If
            strMsg = ""
            i = i + 1
            ' grab time/date
            timeDateVal = Mid(strLine, 2, 19)
            Range("A" & i).Value = timeDateVal
            'get name
            strName = Mid(strLine, 23, Len(strLine) - 23)
            Range("B" & i).Value = strName
        Else

            strMsg = strMsg & Chr(10) & strLine

        End If


        'Debug.Print strLine   ' Print to the Immediate window.
    Loop
    Close #1   ' Close file.

End Sub

Result from your sample:

Excel 2010

	A	B	C
1	Date	Name	Content
2	30/12/2015 22:01	Srinivasan Varadaraj	Dear All, We have report of “TFS application tier connection shows packet drops”. We have initiated a P2 INC3515622 & opened the P2 Bridge. I will be the incident manager. The SMT team is contacting resources to join the P1/P2 incident bridge. Business Impact : 230 users impacted. Call In: 1-855-488-1100 Participant Code: 20482240
3	30/12/2015 22:10	Kamlesh Chaudhari	we created ticket with GCX ticket no is 1872
4	30/12/2015 22:22	Srinivasan Varadaraj	Kamlesh from CMC Joined the call Anand from L2 Network joined the call

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[Miteshkg]

Thanks Steve for your Great Help...This is working as expected.....

 

[Steve_R]

I always enjoy solving problems requiring LineInput method. Sometimes what seems easy can become difficult if the pattern changes. It is a bit of a relief to hear your document stayed consistent.

 

[Miteshkg]

I always enjoy solving problems requiring LineInput method. Sometimes what seems easy can become difficult if the pattern changes. It is a bit of a relief to hear your document stayed consistent.

Hi Steve,
I've a new requirement....may be you can help me out. Please find the attached text file which I need to import in excel and again....
DAte and time should go to Column A
Incident status before - should go to Column B
and contents between - and new date start should go to Column C

 

[Miteshkg]

18-Feb-16 18:00:26 VW Incident INC0172339 Priority 2 - High notification Email sent
Show email details Sent: smt@mphasis.com
From
IT Service Desk
Sent
18-Feb-16 18:00:26
To
smt@mphasis.com
Subject
VW Incident INC0172339 Priority 2 - High notification

18-Feb-16 18:00:07 System Changed: Incident state, Status
Incident state: Closed was: Resolved
Status: Closed was: Resolved

17-Feb-16 17:48:55 VW Incident INC0172339 Priority 2 - High notification Email sent
Show email details Sent: smt@mphasis.com
From
IT Service Desk
Sent
17-Feb-16 17:48:55
To
smt@mphasis.com
Subject
VW Incident INC0172339 Priority 2 - High notification

17-Feb-16 17:48:40 Ganesh Gn Changed: Work Notes (Internal Activity Log)
Spoke to Jemima, she advised to send the resolve notification, with Degradation or down time yet to be determined since it involves both Downtime and Degradation of service.

17-Feb-16 16:05:57 VW Incident INC0172339 Priority 2 - High notification Email sent
Show email details Sent: smt@mphasis.com
From
IT Service Desk
Sent
17-Feb-16 16:05:57
To
smt@mphasis.com
Subject
VW Incident INC0172339 Priority 2 - High notification

17-Feb-16 16:05:57 INC0172339 requires your attention (Comments Updated) Email sent
Show email details Sent: Shobha.Rangarao@mphasis.com
From
NO-REPLY - IS Service Desk <donotreply@servicedesk.vwg.co.uk>
Sent
17-Feb-16 16:05:57
To
Shobha.Rangarao@mphasis.com
Subject
INC0172339 requires your attention (Comments Updated)

17-Feb-16 16:05:56 INC0172339 requires your attention (Comments Updated) Email sent
Show email details Sent: Shobha.Rangarao@mphasis.com
From
NO-REPLY - IS Service Desk <donotreply@servicedesk.vwg.co.uk>
Sent
17-Feb-16 16:05:56
To
Shobha.Rangarao@mphasis.com
Subject
INC0172339 requires your attention (Comments Updated)
Show email details Sent: Shobha.Rangarao@mphasis.com
From
NO-REPLY - IS Service Desk <donotreply@servicedesk.vwg.co.uk>
Sent
17-Feb-16 16:05:56
To
Shobha.Rangarao@mphasis.com
Subject
INC0172339 requires your attention (Comments Updated)

17-Feb-16 16:05:56 INC0172339 has been resolved and is pending closure Email sent
Show email details Sent: Norman.King@vwg.co.uk
From
NO-REPLY - IS Service Desk <donotreply@servicedesk.vwg.co.uk>
Sent
17-Feb-16 16:05:56
To
Norman.King@vwg.co.uk
Subject
INC0172339 has been resolved and is pending closure

17-Feb-16 16:05:45 Priya Daniel Changed: Additional Comments
All loads have been completed for NVS,Options and Bonus