Recursively read UNC path and count line for specific file

Here is a script that will count the number of lines in each FooBar.csv file it find in all subdirectories.

I want to also show the parent folder for each FooBar.csv file.

 

# Script to count the lines in each FooBar.csv file, so determining the total open orders each day.

$outFile = “FooBarCount.csv”

If (Test-Path $outFile) {
    Del $outFile
}

echo ‘Directory,LineCount’ > $outFile

$i=0
$List = Get-ChildItem -path “\\<servername>\d$\Archive\Main” -Recurse -Include FooBar.csv
foreach ($file in $List)
{
 $result = Get-Content $file | Measure-Object -Line | Select-Object -ExpandProperty Lines;

    $directory = Split-Path $file.fullname -Parent #get the directory full name
    $parent = Split-Path $directory -Leaf # get the last directory folder name

    $printline = $parent + ‘,’ + $result

    echo $printline
    echo $printline >> $outFile
}

Formatting datestamps on LOAD DATA

I have a csv (of Control M batch data) that I need to load into a MySQL table, but it has a selection of timestamps and dates that I need to format, so that they can be accepted by the MySQL TIMESTAMP and DATA data types.

Data Example…

20141031,20141031,MY_GROUP,MY_JOB,01/11/2014 00:30,01/11/2014 00:30,Ended OK

Here’s my code with the appropriate formatting:

 LOAD DATA INFILE 'D:/Build/MySQLDump/ControlM stats for Kieran.csv' INTO TABLE stats FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' (title1,title2,report_gen,title3,title4,title5,title6,title7,title8,title9,title10,title11,title12,title13,@order_date,title15,job_name,@start_time,@end_time,job_status,title16) SET order_date = STR_TO_DATE(@order_date, '%Y%m%d'), start_time = STR_TO_DATE(@start_time, '%d/%m/%Y %H:%i'), end_time = STR_TO_DATE(@end_time, '%d/%m/%Y %H:%i');

I’ve used the @variable placeholder so I can reference the column name in the SET command.

 ‘%Y%m%d’ : will format 20141031 correctly.

  ‘%d/%m/%Y %H:%i’ : will format 01/11/2014 00:30 correctly. Note the uppper “H” for 24Hour Format.