Powershell to convert excel to .csv then parse through each sheets

Hi Team,

Am looking for help with below:

I have a excel sheet which needs to be converted to csv. I just need to take sheet 2 & sheet 4 from excel sheet & then convert it to the csv file.

Below is the code I tried: ( Unfortunately the “if” loop isn’t working )

If I disable the if loop, all it does it converts the excel to csv format ( which is NOT what am looking for )

$ExcelFile="C:\source.xlsx"
$CSVFile="C:\target_location\"
$file = Get-ChildItem C:\target_location\"*.xlsx"

#we will iterate every sheet in the file in order to convert it to csv
foreach ($file in $files)
{
#Open the Excel using DCOM
$Excel = New-Object -ComObject Excel.Application

#Should Excel be visible
$Excel.Visible = $false
$Excel.DisplayAlerts = $true

#Open Excel file
$WB = $Excel.Workbooks.Open($file.Fullname)
$ws = $WB.Worksheets | where {$_.name -like "ABCD" -And $_.name -like "KLMN"}

if ($ws)
{
$dest = $file + "\" + $CSVFile

$WB.SaveAs($dest)
}

else {Write "$WB does not contain ABCD & KLMN"}
$WB.close($false)
$Excel.Quit()
Stop-Process - processname EXCEL
}

If I execute the above code it doesn’t error out, but csv will not be created as well.

Any help on this would be great!

Without an example of the xlsx file you’re trying to import, it’s hard to diagnose, but a few ideas.

Do you know if $WS has a value or not? Your IF statement is checking for $WS -eq TRUE for it to run. A breakpoint at line 18 would help you debug that.

Also you’re using a COM object for your excel spreadsheet, which is fine, but there are now better ways to interact with excel.

RamblingCookieMonster has a fantastic module for this. https://www.powershellgallery.com/packages/PSExcel - It’s worth checking out. Here’s how it handles sheet imports:

[pre]
.EXAMPLE
Import-XLSX -Path “C:\Excel.xlsx” -Header One, Two, Five -FirstRowIsData -Sheet 2

    # Import data from C:\Excel.xlsx 
    # Assume first row is data 
    # Use headers One, Two, Five 
    # Pull from sheet 2 (sheet 1 is default) 

[/pre]

Last, if it still doesn’t cooperate, a small sample of your .xlsx can help rule out issues with that.

Also, Do checkout the ImportExcel , Export-ExcelSheet has -Extension parameter which accepts csv.

https://www.powershellgallery.com/packages/ImportExcel/6.0.0

Hi Nathaniel & Kvprasoon,

 

Thanks for the quick response on my query.

Firstly, when I try to install the ImportExcel via powershell as Admin I get below error ( I work in a very restricted environment, so I requested temp admin access for this which stays good for 24 hours )

Install-Module -Name PSExcel
PackageManagement\Install-Package : No match was found for the specified search criteria and module name ‘PSExcel’.
Try Get-PSRepository to see all available registered module repositories.
At C:\Program Files\WindowsPowerShell\Modules\PowerShellGet\1.0.0.1\PSModule.psm1:1772 char:21
+ … $null = PackageManagement\Install-Package @PSBoundParameters
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : ObjectNotFound: (Microsoft.Power…InstallPackage:InstallPackage) [Install-Package], Ex
ception
+ FullyQualifiedErrorId : NoMatchFoundForCriteria,Microsoft.PowerShell.PackageManagement.Cmdlets.InstallPackage

PS C:\windows\system32> Get-PSRepository
WARNING: Unable to find module repositories.

So I’m working with COM object as ImportExcel wasn’t available.

Secondly, I wrote a script to download the file from sharepoint, puts them in separate csv file for each sheets & converts into TXT file for 2 of the sheets.

Finally used the get-content & set-content to concatenate the 2 TXT files into a single TXT file.

How does my final output/TXT looks: ( Sheet3.TXT )

 

UserID

thy3245

rop9087

ew36645

 

tyt0975

rtio9065

 

trew2345

io90789

Am trying to delete/truncate all spaces on the final TXT file & remove the column name as well, but the Trim() function doesn’t do the job am looking for.

  1. Remove the spaces
  2. Remove Column name: UserID & --
My script looks like below:

#Specify the path of the Output CSV files
$Newfile = “C:\Users\Desktop\Script\Sheet1”
$Newfile2 = “C:\Users\Desktop\Script\Sheet2”

Create an Object Excel.Application using Com interface

$objExcel = New-Object -ComObject Excel.Application

Disable the ‘visible’ property so the document won’t open in excel

$objExcel.Visible = $false

Open the Excel file and save it in $WorkBook

$WorkBook = $objExcel.Workbooks.Open($FilePath)

Load the WorkSheet ‘Sheet1’ & ‘Sheet2’

$WorkSheet = $WorkBook.Worksheets | where-object {$.name -like “Sheet1”}
$WorkSheet2 = $WorkBook.Worksheets | where-object{$
.name -like “Sheet2”}

#SaveAs option to ‘;’ limited Excel sheet to CSV format
$WorkSheet.SaveAs($Newfile+".csv",6,0, 0, 0, 0, 0, 0, 0, $true)
$WorkSheet2.SaveAs($Newfile2+".csv",6,0, 0, 0, 0, 0, 0, 0, $true)

Import-Csv C:\Users\Desktop\Script\Sheet1.csv -Delimiter ‘;’ | Select-Object UserID -Skip 1 | Out-File C:\Users\Desktop\Script\Sheet1.txt

Import-Csv C:\Users\Desktop\Script\Sheet2.csv -Delimiter ‘;’ | Select-Object UserID -Skip 1 | Out-File C:\Users\Desktop\Script\Sheet2.txt

Get-Content Sheet1.txt, Sheet2.txt | Set-Content Sheet3.txt

#Close the WorkBook
$WorkBook.close($false)

#Close the Excel connection
$objExcel.Quit()

I recommend formatting your code for better reading.

https://github.com/kvprasoon/Images/blob/master/PowerShell/CodePosting.gif

#Specify the path of the Output CSV files
$Newfile = "C:\Users\Desktop\Script\Sheet1"
$Newfile2 = "C:\Users\Desktop\Script\Sheet2"

# Create an Object Excel.Application using Com interface

$objExcel = New-Object -ComObject Excel.Application

# Disable the 'visible' property so the document won't open in excel
$objExcel.Visible = $false

# Open the Excel file and save it in $WorkBook
$WorkBook = $objExcel.Workbooks.Open($FilePath)

# Load the WorkSheet 'Sheet1' & 'Sheet2'

$WorkSheet = $WorkBook.Worksheets | where-object {$_.name -like "Sheet1"}
$WorkSheet2 = $WorkBook.Worksheets | where-object{$_.name -like "Sheet2"}

#SaveAs option to ';' limited Excel sheet to CSV format
$WorkSheet.SaveAs($Newfile+".csv",6,0, 0, 0, 0, 0, 0, 0, $true)
$WorkSheet2.SaveAs($Newfile2+".csv",6,0, 0, 0, 0, 0, 0, 0, $true)

Import-Csv C:\Users\Desktop\Script\Sheet1.csv -Delimiter ';' | Select-Object UserID -Skip 1 | Out-File C:\Users\Desktop\Script\Sheet1.txt

Import-Csv C:\Users\Desktop\Script\Sheet2.csv -Delimiter ';' | Select-Object UserID -Skip 1 | Out-File C:\Users\Desktop\Script\Sheet2.txt

Get-Content Sheet1.txt, Sheet2.txt | Set-Content Sheet3.txt

#Close the WorkBook
$WorkBook.close($false)

#Close the Excel connection
$objExcel.Quit()