Remember That The Regex Engine Is Eager

Just a reminder:

Regex is eager and will return the first match in the piped list expressions: (<= |<>|<)

that was important for me for trying to match equality signs, for example: "<>" or "<=" vs "<".

https://www.regular-expressions.info/alternation.html

Converting CSV files into XML files using PowerShell

At work I have to  resort sometimes to PowerShell to provide a quick solution to a problem.

For example, I had to do some CSV conversions and manipulations, which seemed to be easier done in PowerShell rather than in SQL Server.

1. I will document here a CSV to XML file conversion, which looked to me short and sweet:

Idea inspired from http://blogs.msdn.com/b/powershell/archive/2007/05/29/using-powershell-to-generate-xml-documents.aspx

#create xml file - export_xml 
$file = "C:\tmp\file.csv"
$fileoutxml = "C:\tmp\file.xml"
$xml=Import-Csv $file -Delimiter ";" -Header "c1","c2","c3" |
 foreach { " <row c1=`"{0}`" c2=`"{1}`" c3=`"{2}`"/>`n" -f
 $_.c1,$_.c2,$_.c3 } 
$xml="<export key_field=`"c1`">`n " + $xml + "</export>" | Out-File $fileoutxml -Encoding UTF8 

2. and a replace with Regular Expressions:
here, I am first replacing ; by | and trimming up the data at the same time
and then changing the swapping the order of the two last columns. it is important that $2$1 is surrounded by single quotes - script stops working if we use double quotes for some reason.

(Get-Content $file ) |
Foreach-Object {$_ -replace "(\s*;\s*)", -replace "(?<=^[^|]*\|[^|]*)(\|[^|]*)(\|[^|]*$)", '$2$1'; } |
Set-Content $fileout -Encoding Unicode