How to convert a CSV with one column in to more columns PowerShell

You should know a bit about PowerShell for this blog post.

For another blog post I needed a list of countries and local dial-in numbers for Microsoft Teams PSTN.

When copying and pasting the table in Excel, it was all in one column, and this triggered me to see if I could fix this with PowerShell.

Nice, but I did not succeed.

In the end I asked colleague Maurice Lok-Hin to have a look, and as always, he has the answer within a few minutes. So all credits in this post go to Maurice!


Full script first.

For those who just want to continue their work, here is the full script first. For those who need an explanation, look at the paragraph.

$csv = Import-CSV 'C:\temp\CSV.csv'
$NEWCSV = [system.Collections.Generic.List[system.Object]]::new()

for ($i = 0; $i -lt $csv.Count; $i++) {
   $object =  [PSCustomObject]@{
        Country = $csv.country[$i]
        Number = $csv.country[$i + 1]
    }
    $NEWCSV.Add($Object)
    $i++
}
$NEWCSV | Export-CSV 'C:\temp\CSVNEW.csv' -NoTypeInformation

how does the script create multiple columns, and how do I add more columns?

Let’s start with breaking the script down per line.


Import-CSV

$csv = Import-CSV -Path 'C:\temp\CSV.csv'

I think Import-CSV speaks for itself. In the command above you import a CSV into an empty object as above in $csv. Make sure to change the path to the existing csv file.

For more about Import-Csv click here.


[system.Collections.Generic.List [system.Object]]::new()

$NEWCSV = [system.Collections.Generic.List[system.Object]]::new()

With this command you create a list in PowerShell And via PSCustomObject you can easily add new data to this.


for ($i = 0; $i -lt $csv.Count; $i++)

for ($i = 0; $i -lt $csv.Count; $i++) {

}

The ‘for’ loop is the most interesting here. The for loop is really nothing but a repetition of commands up to a certain time, or value.

In our ‘for’ loop we start with 0, which is the ‘$i’ and we end when the ‘$i’ equals or exceeds the number of lines in your csv. This is ‘$csv.Count’.

The 0 represents the first line in your CSV file. For me this is a column called ‘Country’.

And finally you have the ‘$i ++’ the ++ stands for ‘$i = $i + 1’. This means that it adds +1 to $i until the $csv.count number is reached.

For more about the ‘for’ loop please click here.


[PSCustomObject]@{}

$object =  [PSCustomObject]@{
        Country = $csv.country[$i]
        Number = $csv.country[$i + 1]
    }
    $NEWCSV.Add($Object)

“The idea behind using a PSCustomObject is to have a very simple way to create structured data. Take a look at the first example and you will have a better idea of what that means.”

The text above is from powershellexplained.com and I’m not going to explain what a PSCustom object is, because they can do it much better.

Click here for the link.


What about more columns?

Well, it is as easy as adding an extra line in the ‘[PSCustomObject]@{}’.

For every column you’ll need an extra number to $i. So when you start using more columns you could change the $i++ to ‘$i = $i + 2’. and if you add nother column you’ll need to use ‘$i = $i + 3’.

$object =  [PSCustomObject]@{
        Country = $csv.country[$i]
        Number = $csv.country[$i + 1]
        City = $csv.country[$i + 2]
        CountryCode = $csv.country[$i + 3]
    }
    $NEWCSV.Add($Object)
$i = $i + 3

$i++

$i++ -eq $i = $i +1

The ‘++’ literally adds +1 to add to ‘$i’, so basically the following command is executed: ‘$i = $i +1’. With ++ it is shorter and therefore better, as any programmer would say..


Export-CSV

$NEWCSV | Export-CSV -Path 'C:\temp\CSVNEW.csv' -NoTypeInformation

Export-Csv speaks for itself. The data in your List is exported to a CSV file.
A column is created for each header.

I have the following headers:

  • Country
  • Number

And therefore there will be 2 columns with titles Country and Number and below that the value.

For more about Export-Csv click here.


Credits

All credits go to my colleague and great tutor, Maurice Lok-Hin.

Published by

Bas Wijdenes

My name is Bas Wijdenes and I work as a Senior Services Engineer @ Wortell. In my spare time I write about the error messages that I encounter during my work. I am currently occupied with Azure infrastructure and automating with PowerShell.

Leave a Reply

Your email address will not be published. Required fields are marked *