Convert CSV to XLSX via PowerShell

Revisiting an old friend as some date format junk was needing adjustment and leading 0s from txt file were being dropped. Script originally brought txt into xlsx and all data was “General” format. Tweak noted below brought all data into xlsx as “Text” format.

Changed:

$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
to
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count

Entire script with stuff removed to protect the martians:

# Define locations and delimiter
$SourceFolder = "\\server\share\path1\path2"
$csv = $SourceFolder+"\sourcefile.txt" #Location of the source file
$xlsx = $SourceFolder+"\Cool Filename "+(Get-Date -f dd)+"."+(Get-Date -f MM)+"."+(Get-Date -f yyyy)+".xlsx" #Desired location of output
$delimiter = "|" #Specify the delimiter used in the file

# REMOVE ALL .XLSX files in the folder
Remove-Item $SourceFolder\* -include *.xlsx

# Create a new Excel workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $csv)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.
$Workbook.SaveAs($xlsx,51)
$excel.Quit()

# Legacy file process rename tasks
dir \\server\share\path1\file.txt | Rename-Item -NewName {"New_Filename_"+(Get-Date -f yyyyMMdd)+".csv"}
dir \\server\share\path1\file2.txt | Rename-Item -NewName {"Another_File_"+(Get-Date -f yyyyMMdd)+".csv"}

Source: https://code.adonline.id.au/csv-to-xlsx-powershell/

Using Exclaimer with other Security Vendor

Resolution was found in KB note from Exclaimer. Specifically the note to select the “Stop processing more rules” option to force the Exclaimer rule to run and to prevent Office 365 from running the *VENDOR* rule until the email is returned from Exclaimer Cloud with a signature attached. At this point, the Exclaimer Transport Rule will have its exception triggered by the Exclaimer message header once it returns, and the *VENDOR rule will be used instead.

https://support.exclaimer.com/hc/en-gb/articles/360019730552-Reconfiguring-third-party-security-solution-connectors-to-work-with-Exclaimer-Cloud

How to Use Notepad to Create a Log File

Nice tip for Notepad from weekly email from Veeam’s Gostev.

How to Use Notepad to Create a Log File


Summary


Microsoft Notepad is a word processing tool included with Windows and is installed by default under the Accessories program group. You can use it to create a log-type file that adds the current date and time each time the Notepad file is opened. This article describes how to create a log file with Notepad.

More Information


To create a log file in Notepad:

  1. Click Start, point to Programs, point to Accessories, and then click Notepad.
  2. Type .LOG on the first line, and then press ENTER to move to the next line.
  3. On the File menu, click Save As, type a descriptive name for your file in the File name box, and then click OK. When you next open the file, note that the date and time have been appended to the end of the log, immediately preceding the place where new text can be added. You can use this functionality to automatically add the current date and time to each log entry.

Source: https://support.microsoft.com/en-us/help/260563/how-to-use-notepad-to-create-a-log-file

Amazon Workspaces now provides IP address-based access control

Good stuff to lock down access to Amazon WorkSpaces…

Amazon WorkSpaces now provides you with the ability to control the IP addresses from which your WorkSpaces can be accessed. Using IP address-based control groups, you can define and manage groups of trusted IP addresses, and only allow users to access their WorkSpaces when they’re connected to a trusted network. This feature helps you gain greater control over your security posture.

More here