Powershell Excel Convert

Question

Good Evening,

I have a university project using powershell, in a mock test to edit an excel document. I have no idea on where to start.

I'm hoping you guys can really help me with this. I have attached a spreadsheet. Spreadsheet one (in csv format) before. I want to convert it to spreadsheet two (in .xls format) after. This isn't the most complicated bit.

All the matching unique codes, I want to merge the description column, so only one unique code shows on one line. The unique code which is inserted into the A column is made up of "route name" from AL2 & DropNo from AQ2 column.

I want to do this with any csv file which is sat in "routes" folder. Which will be in c:/documents/routes

Is there any way I can do this at all? Im really struggling!

Answer 1

Hi @Alex Brown ,

maybe this is helpful to start:

Content of CSV file:

ID,FirstName,LastName  
1,Peter,Smith  
2,James,Bond  
3,Mickey,Roth  

Script:

$NewLines = "newID,DisplayName,Email,oldID,FirstName,LastName`r`n" #Define new header  
Import-Csv -Path junk.csv | # read csv file  
ForEach-Object { # for each line in csv  
    $i = $_.ID # get ID value  
    $f = $_.FirstName # get FirstName value  
    $l = $_.LastName # get LastName value  
    $newID = $i + $f.Substring(0,1) + $l.Substring(0,1) # build new ID - ID + first char of firstname and lastname  
    $displayName = '"' + "$l, $f" + '"' # build DisplayName with quotes - , and space in display name  
    $emailAddress = "$f.$l" + "@test.org" # build email-address  
    $NewLines += "$newID,$displayName,$emailAddress,$i,$f,$l" + "`n"  # build new lines with new values  
}  
$NewLines  

Content of $NewLines:

newID,DisplayName,Email,oldID,FirstName,LastName  
1PS,"Smith, Peter",******@test.org,1,Peter,Smith  
2JB,"Bond, James",******@test.org,2,James,Bond  
3MR,"Roth, Mickey",******@test.org,3,Mickey,Roth  

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 2

Good Morning,

I maybe doing something wrong here. But it isn't recognising the csv form. Is there anything I need to add in with the - path or file name.

thanks

Answer 3

Hi @Alex Brown ,

there are a few parameters for the import-csv:

https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/import-csv?view=powershell-7.1

----------

(If the reply was helpful please don't forget to upvote and/or accept as answer, thank you)

Regards
Andreas Baumgarten

Answer 4

Hello,

sorry for the delayed response. I am having trouble executing this. It doesn't seem to change the content in the excel file. Is there something I am doing wrong?

thanks