Playing with CSV and PowerShell | Switch between columns

#csv #beginners #powershell #writing

Hello everyone 🤗

PowerShell mode: ON ✅

If you ever wondered how you can switch between two columns WITHOUT using Excel to do the actions, here you find the answer!

Else if you never wondered...well, stay here because you'll learn something valuable!

Else if you already know...is it the same method? 😏

Else...I'll still advise you to read the whole article! 😉

Case

In this article, you'll recognize the same file as used in Why is my CSV file messed up?.
What if we want to switch between column A (called Date and Time) and column B (called Dividend). We can do this using the power of...PowerShell!



$inputFilePath = "C:\path\KO_stock_dividend (1).csv"


$fileContent = Get-Content -Path $inputFilePath


$fileData = $fileContent | ConvertFrom-Csv


$swappedData = $fileData | ForEach-Object {
    $temp = $_."Date and Time"
    $_."Date and Time" = $_."Dividend"
    $_."Dividend" = $temp
    $_
}


$swappedCsv = $swappedData | ConvertTo-Csv -NoTypeInformation


$swappedCsv[0] = $swappedCsv[0] -replace 'Date and Time', 'tempHeader'
$swappedCsv[0] = $swappedCsv[0] -replace 'Dividend', 'Date and Time'
$swappedCsv[0] = $swappedCsv[0] -replace 'tempHeader', 'Dividend'


$swappedCsv | Set-Content -Path $inputFilePath

Write-Output "Columns and values swapped, and file saved to $inputFilePath"

And this is the output:

➡️➡️➡️Pseudocode⬅️⬅️⬅️

1. Set inputFilePath to the path of the CSV file.
2. Read fileContent from the CSV file at inputFilePath.
3. Convert fileContent to structured data (fileData).
4. For each row in fileData:
    a. Store "Date and Time" value in a temporary variable (temp).
    b. Assign "Dividend" value to "Date and Time".
    c. Assign temp value to "Dividend".
    d. Return the modified row.
5. Convert swappedData to CSV format (swappedCsv) without type information.
6. Replace "Date and Time" with a temporary header name.
7. Replace "Dividend" with "Date and Time".
8. Replace temporary header name with "Dividend".
9. Save swappedCsv back to the CSV file at inputFilePath.
10. Output "Columns and values swapped, and file saved to inputFilePath".

You probably noticed that the file will be over-written. The code can be modified so the integrity of the original file is kept.
Option A) Make sure you back-up your file before executing the script

Option B) Use PowerShell 👓


$inputFilePath = "C:\path\KO_stock_dividend (1).csv"
$outputFilePath = "C:\path\KO_stock_dividend_(1)_modified.csv"


$fileContent = Get-Content -Path $inputFilePath


$fileData = $fileContent | ConvertFrom-Csv


$swappedData = $fileData | ForEach-Object {
    $temp = $_."Date and Time"
    $_."Date and Time" = $_."Dividend"
    $_."Dividend" = $temp
    $_
}


$swappedCsv = $swappedData | ConvertTo-Csv -NoTypeInformation


$swappedCsv[0] = $swappedCsv[0] -replace 'Date and Time', 'tempHeader'
$swappedCsv[0] = $swappedCsv[0] -replace 'Dividend', 'Date and Time'
$swappedCsv[0] = $swappedCsv[0] -replace 'tempHeader', 'Dividend'


$swappedCsv | Set-Content -Path $outputFilePath

Write-Output "Columns and values swapped, and file saved to $outputFilePath"

And that's it!

Now you know how to switch/ swap two columns.

Note: Make sure to replace the path with the one you actually use. Perhaps your file is under "C:\Users\user1\Documents\Reports".