Sharing a tip that will guide many of you in converting CSV to EXCEL in a fast manner.

We have some scripts where the creator has got the large data in a variable or exported to CSV and than called excel to write data one row at a time which was taking around 10 to 12 hours to finish the execution.

We have reworked on the logic & used this TIP to reduce the execution to just one hour as excel creation /Formatting doesn’t take any time now, We are utilizing the same principle as being used in GUI for converting csv to excel.

Here is the Sample code:

##################################################

$xl = new-object -comobject excel.application
$xl.visible = $false
$Workbook = $xl.workbooks.open($csv)
$Worksheets = $Workbooks.worksheets
$Workbook.SaveAs($exl, 51)
$Workbook.Saved = $True
$xl.Quit()

#######################################
$xl = new-object -comobject excel.application
$xl.visible = $false
$Workbook = $xl.workbooks.open($exl)
$worksheet1 = $workbook.worksheets.Item(1)
for ($c = 1; $c -le 15; $c++) {
$worksheet1.Cells.Item(1, $c).Interior.ColorIndex = 39
}
$headerRange = $worksheet1.Range(“a1”, “o1”)
$headerRange.AutoFilter() | Out-Null
$headerRange.entirecolumn.AutoFit() | Out-Null
$worksheet1.rows.item(1).Font.Bold = $True
$workbook.Save()
$workbook.Close()
$xl.Quit()

########################

Lets now just check what’s this code is doing..

########################################

$xl = new-object -comobject excel.application                           # call Excel
$xl.visible = $false                                                                                 # if you set it to true than excel will be visible on screen
$Workbook = $xl.workbooks.open($csv)                                    # Open CSV file , you can enter path in “” as well
$Worksheets = $Workbooks.worksheets                                     #fetch worksheets
$Workbook.SaveAs($exl, 51)                                                             #Save as xlsx(51), if you want to save as xls use (1), path can be                                                                                                                                   #used in””
$Workbook.Saved = $True                                                                 #save workbook
$xl.Quit()                                                                                                   # quit out of excel

####################Now lets format the excel we saved########

$xl = new-object -comobject excel.application
$xl.visible = $false
$Workbook = $xl.workbooks.open($exl)                                      # open excel
$worksheet1 = $workbook.worksheets.Item(1)                          #open worksheet
for ($c = 1; $c -le 15; $c++) {                                                                # change color index of the row 1 till element 15 to 39
$worksheet1.Cells.Item(1, $c).Interior.ColorIndex = 39
}
$headerRange = $worksheet1.Range(“a1”, “o1”)                       #select the rage from a1 to o1
$headerRange.AutoFilter() | Out-Null                                           # apply autofilter
$headerRange.entirecolumn.AutoFit() | Out-Null                    #apply autofit
$worksheet1.rows.item(1).Font.Bold = $True                            # set the values of first row to be BOLD
$workbook.Save()
$workbook.Close()
$xl.Quit()

###############

This method you can use instead if writing elements one by one to excel as this is the faster way, you just can export to CSV & than save as excel.

Thanks for reading and downloading

Tech Wizard

https://techwizard.cloud

https://syscloudpro.com/