Hello everyone;
I am looking for a simple macro to extract information from a text file and
paste it into columns in excel.
------------------------------------------------------------------------------------------------
I need to extract the the Video File Name, Project Task ID, Date, From
Time, To Time and paste it into columns in excel as shown below.
Video File Name Project Date
From Time To Time20140901191709109@DVR2_Ch1.asf
NE-LCP_SINS_2014_LC-SB-034_0001 1/9/2014 16:47:09
17:17:0920140901191709500@DVR2_Ch2.asf NE-LCP_SINS_2014_LC-SB-034_0001
1/9/2014 16:47:09 17:17:09
Attached is the text file and excel example.
Specific info:
The Video File name is all the .asf extensions
The Project Task ID is contained in the Directory name
The Date is taken from the .asf file name ex. 20140901191709109@DVR2_Ch1.asf,
gives me a date of > 20140901
The Times are a bit weird and are extracted from the .asf file name. In
this particular case we are taking the UTC time in the file name and
subtracting -2.5 hours to get the "From Time", then taking the file name
and subtracting -2.0 hours to obtain the "To Time". Each video file should
be 30 minutes. ex. 20140901191709109@DVR2_Ch1.asf, gives a From Time of
16:47:09 and a To time of 17:17:09.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
Video File Name,Project,Date,From Time,To Time
20140901191709109@DVR2_Ch1.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,16:47:09,17:17:08
20140901191709500@DVR2_Ch2.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,16:47:09,17:17:08
20140901191709656@DVR2_Ch3.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,16:47:09,17:17:08
20140901194709813@DVR2_Ch1.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,17:17:09,17:47:08
20140901194710047@DVR2_Ch2.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,17:17:09,17:47:08
20140901194710203@DVR2_Ch3.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,17:17:09,17:47:08
20140901201710812@DVR2_Ch1.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,17:47:10,18:17:09
20140901201711359@DVR2_Ch2.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,17:47:10,18:17:09
20140901201711516@DVR2_Ch3.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,17:47:10,18:17:09
20140901204710922@DVR2_Ch1.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,18:17:10,18:47:09
20140901204711094@DVR2_Ch2.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,18:17:10,18:47:09
20140901204711235@DVR2_Ch3.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,18:17:10,18:47:09
20140901211710922@DVR2_Ch1.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,18:47:10,19:17:10
20140901211711265@DVR2_Ch2.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,18:47:10,19:17:10
20140901211711406@DVR2_Ch3.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,18:47:10,19:17:10
20140901214711922@DVR2_Ch1.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,19:17:11,19:47:10
20140901214712438@DVR2_Ch2.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,19:17:11,19:47:10
20140901214712578@DVR2_Ch3.asf,NE-LCP_SINS_2014_LC-SB-034_0001,1/9/2014,19:17:11,19:47:10
Volume in drive I is Seagate Backup Plus Drive
Volume Serial Number is 061B-9493
Directory of
I:\VisualWorks\Projects\PROJECT\NE-LCP_SINS_2014_LC-SB-034_0001\DATA_20140901191709109
09/01/2014 05:18 PM 470,254,325 20140901191709109@DVR2_Ch1.asf
09/01/2014 05:18 PM 470,462,325 20140901191709500@DVR2_Ch2.asf
09/01/2014 05:18 PM 470,422,331 20140901191709656@DVR2_Ch3.asf
3 File(s) 1,411,138,981 bytes
Directory of
I:\VisualWorks\Projects\PROJECT\NE-LCP_SINS_2014_LC-SB-034_0001\DATA_20140901194709813
09/01/2014 05:48 PM 470,390,325 20140901194709813@DVR2_Ch1.asf
09/01/2014 05:48 PM 470,478,331 20140901194710047@DVR2_Ch2.asf
09/01/2014 05:48 PM 470,414,331 20140901194710203@DVR2_Ch3.asf
3 File(s) 1,411,282,987 bytes
Directory of
I:\VisualWorks\Projects\PROJECT\NE-LCP_SINS_2014_LC-SB-034_0001\DATA_20140901201710812
09/01/2014 06:18 PM 470,150,325 20140901201710812@DVR2_Ch1.asf
09/01/2014 06:18 PM 470,182,325 20140901201711359@DVR2_Ch2.asf
09/01/2014 06:18 PM 470,286,325 20140901201711516@DVR2_Ch3.asf
3 File(s) 1,410,618,975 bytes
Directory of I:\VisualWorks\Projects\PROJECT\Test\DATA_20140905182344453
09/05/2014 03:55 PM 1,547,573 20140905182344078@DVR2_Ch1.asf
09/05/2014 03:55 PM 4,315,573 20140905182344453@DVR2_Ch2.asf
09/05/2014 03:55 PM 4,275,567 20140905182344640@DVR2_Ch3.asf
3 File(s) 10,138,713 bytes
Total Files Listed:
445 File(s) 165,834,604,455 bytes
0 Dir(s) 1,850,604,470,272 bytes free
