VBA Code to copy specific contents from 2 text files to separate excel columns

**Miteshkg** · 01-11-2016, 05:08 AM

HI all,

I've a specific requirement where in there will be 2 text files which will have information like normal text plus date and time stamp of communication. Basically, the VB code should read each line of text file and copy date and time to one column in excel and other text contents to second column. all text contents are available only after date and time format like [‎12/‎30/‎2015 10:01 PM] Srinivasan Varadaraj: Dear All, We have .....................blablabla....
So what I want is copy ‎12/‎30/‎2015 10:01 PM in Column A and everything after ":" should be in Column B..

Like this, Code should read through end of text file and where ever the time stamp is written, copy to Excel Column A and remaining text after ":" to Column B.

Let me know if I've made my self clear to explain my requirement. I need to submit a project by EOD tomorrow. Any quick help is highly appreciated.

Please Help

**Miteshkg** · 02-10-2016, 04:39 AM

Need help guys....

**Akuini** · 02-10-2016, 05:45 AM

Try this:
Open the the text file in Excel then run the macro.
(Backup your file first)

Please Login or Register  to view this content.

**Akuini** · 02-10-2016, 06:02 AM

Just to be clear, this is in one row, right?

[‎12/‎30/‎2015 10:01 PM] Srinivasan Varadaraj: Dear All, We have bla bla

**Miteshkg** · 02-10-2016, 07:02 AM

No date and contents are on 2 different row in text file...

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 [email protected]

[‎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:

**Miteshkg** · 02-10-2016, 07:03 AM

No Date and contents are in 2 different rows in text file.

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 [email protected]

[‎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:

**jindon** · 02-10-2016, 07:22 AM

http://forum.chandoo.org/threads/vba...columns.27254/

If attached does not work, just ignore.

Workbook, original txt file attached.

**Miteshkg** · 02-10-2016, 07:34 AM

Wow....Amazing jindon...Many thanks...this is what I was looking for....
1. Can we merge the column C with all the comments in one cell
2. Is it possible to add additional line (probably with Orange color) after every 30 mins of incident timeline mentioned in column A

**jindon** · 02-10-2016, 07:48 AM

Post a workbook with your desired result.

**Miteshkg** · 02-10-2016, 07:58 AM

Basically, team members will copy the group chat details regarding incident in one file and service now ticket updates in second file. Both word files are attached for your reference. we will ask the team to copy the contents from both Chat window and ticketing tool to notepad. Thats not an issue.

So, once they copy the group chat details in one notepad and run the macro,
date and time should go to Column A
Name of the person should go to Column B
Contents of the chat should go to Column C (this is what we have already achieved)

Similarly, they will copy the contents of service now ticket tool updates to notepad 2 and run the macro and
date and time should go to Column A
Name of the person should go to Column B
Contents of the chat should go to Column C (this should be append in the file)

after which entire sheet should be formatted as table and all cells should be sort with incident timeline chronological order and after every 30 mins...a orange line should be added

Required output is also attached as Excel sheet in which you can ignore the column A.

but rest of the things should be similar which is a manual process as of now but want to automate the same.

**jindon** · 02-10-2016, 08:10 AM

You have attached Word doc, not Text file.

**Miteshkg** · 02-10-2016, 08:25 AM

Please find the text file attached.

**jindon** · 02-10-2016, 09:25 AM

I'm confusing.

Is there any relation between your desire result and those 2 text files?

What is interval? from what time?
What is Process column?

**Miteshkg** · 02-10-2016, 11:47 AM

Sorry for the confusion.

Let me describe in another way.
Team member has group chat details and those will be copied to text file. Now, we need that text file import in excel and all the details should go to respective columns like, Date and Time in Column A, Name in Column B and Contents of chat between 2 date/time to Column C. Now, this part is already completed with your previous help.

Those 2 attached text files are the Incident group chat and ticket updates. Team members copy those details in 2 different text file and then manually copying the details in excel sheet. We want to automate this and with your earlier code, now I will be able to achieve automation in first text file - "P2 Incident Chat" which has group chat details.

For second file - "P2 Incident service now updates" I need similar functionality that Date and Time in Column A, Name in Column B and Contents between 2 date/time to Column C.

After which,
- Can we merge the contents of column C in one cell? (as mentioned in my desired excel sheet)
- Once the date and time column is sort in a chronological order, right from first row time, every 30 mins, we need to add blank orange line.

Please unblock the VB project so that I can do the further modification as per my requirement. Thanks in advance.

Really appreciate your follow up and great help on this requirement.

**jindon** · 02-11-2016, 12:24 AM

Try

Please Login or Register  to view this content.

**Miteshkg** · 02-11-2016, 02:39 AM

Hi jindon,

Thanks for the code...The code for IncidentChat is working absolutely fine as per my requirement where in I've used "P2 Incident Chat.txt" file as my text file for import.

However, second code IncidentUpdate is not working and giving error as
Run-time error '1004':
Sort method of Range class failed.

I'm using P2 Incident service now updates.txt file as import file for 2nd code.

On debugging, it is prompting to .Sort .Cells(1), 1 line...

Please help to overcome this issue.

Rest of the things are as per my requirement.

Question: - I would create 2 buttons to call both the macros. Would IncidentUpdate code will append the contents to existing excel sheet or do I need to run the code on separate excel sheet?

**jindon** · 02-11-2016, 02:47 AM

Working fine for me

**Miteshkg** · 02-11-2016, 03:20 AM

Thanks...I guess the Incident update text file was modified by trimming the leading space and remove empty lines. I guess that will work.

Many Thanks to you for resolving the issue in just one day....u really made my day....appreciate your valuable time and prompt response.

Cheers!!

**Miteshkg** · 02-22-2016, 01:37 AM

Issue resolved.

**Miteshkg** · 02-22-2016, 01:40 AM

Issue resolved.

**jindon** · 02-22-2016, 01:46 AM

Miteshkg

You can not ask multiple questions in one thread, so open a new thread for your new question.
Suggest you to attach source txt file and a workbook with desired result in a new thread.

**jindon** · 02-22-2016, 03:31 AM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

VBA Code to copy specific contents from 2 text files to separate excel columns

LinkBack

Thread Tools

Rate This Thread

Display

VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Re: VBA Code to copy specific contents from 2 text files to separate excel columns

Thread Information

Users Browsing this Thread

Similar Threads

Macro to separate data based on contents of a column to 2 new excel files

VBA code - copy selected text, separate string text into different cells and tidy up

[SOLVED] code to clear all contents in columns with specific text in row 2

Export Columns to separate Text files

VBA Code to open text files and copy information over to an excel sheet

Exporting large number of columns along with 1 fixed column into separate text files

Import 2 text files into 2 separate columns?

Bookmarks

Bookmarks

Posting Permissions