Gram123
Thread Starter
- Joined
- Mar 15, 2001
- Messages
- 1,829
I currently have a PS1 script that takes yesterday's log files from a server, collates them into a single file and emails the file out in csv format. Out of necessity this always includes the first file from today's date, as that file contains the final activity from yesterday as well as the first activity from today.
Problem is, that means some lines of data will appear in today's file and tomorrow's file.
The filename of the daily csv file always contains yesterday's date in the format <7-char filename>dd-mm-yyyy.csv, e.g.:
At the outset, one of these daily csv files will consist of tens of thousands of rows of data, with all data in column A. Each string begins with a date/time stamp, so the values in col A are in the format yyyymmdd_hhmmss<log file information> e.g.:
I then run a macro on the csv file, beginning with inserting two new columns to split the data into 2 columns using the formula:
In other words, it places the date & time stamp in one col A, and all the rest of the guff in col B. It then goes off and does a whole bunch of other stuff.
What I need to do first, though (don't mind whether it's before or after the column has been split into two), is delete all rows where the date does not match the filename.
So, in VBA, here's what I need to do:
- Get the filename of the active file as a variable.
- Extract the date from the filename, i.e. chars 8 to 17 - e.g. 19-03-2015
- Convert the variable's format, so it matches the first 8 chars of the values in Column A, i.e. 20150319
- Delete all rows in the data that do not begin with this value.
I realise this could be done by the PS1 script before the daily email is issued, but evidence suggests this would slow down the script considerably, so I'd rather the processing was by the Excel macro on the user's PC, than by the script on the server.
Thanks in advance!
G123
Problem is, that means some lines of data will appear in today's file and tomorrow's file.
The filename of the daily csv file always contains yesterday's date in the format <7-char filename>dd-mm-yyyy.csv, e.g.:
ABCDEFGH19-03-2015.csv
At the outset, one of these daily csv files will consist of tens of thousands of rows of data, with all data in column A. Each string begins with a date/time stamp, so the values in col A are in the format yyyymmdd_hhmmss<log file information> e.g.:
20150319_093015< >long string of log information
I then run a macro on the csv file, beginning with inserting two new columns to split the data into 2 columns using the formula:
Range("A2").FormulaR1C1 = "=LEFT(RC[2],15)"
Range("B2").FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-15)"
Range("B2").FormulaR1C1 = "=RIGHT(RC[1],LEN(RC[1])-15)"
In other words, it places the date & time stamp in one col A, and all the rest of the guff in col B. It then goes off and does a whole bunch of other stuff.
What I need to do first, though (don't mind whether it's before or after the column has been split into two), is delete all rows where the date does not match the filename.
So, in VBA, here's what I need to do:
- Get the filename of the active file as a variable.
- Extract the date from the filename, i.e. chars 8 to 17 - e.g. 19-03-2015
- Convert the variable's format, so it matches the first 8 chars of the values in Column A, i.e. 20150319
- Delete all rows in the data that do not begin with this value.
I realise this could be done by the PS1 script before the daily email is issued, but evidence suggests this would slow down the script considerably, so I'd rather the processing was by the Excel macro on the user's PC, than by the script on the server.
Thanks in advance!
G123