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

rs1 · April 20, 2019, 5:29am

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!

artisanbytecrafter · April 20, 2019, 7:25am

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. PowerShell Gallery | PSExcel 1.0.2 - 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.

kvprasoon · April 20, 2019, 8:52am

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

rs1 · April 25, 2019, 9:33am

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.

Remove the spaces
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()

kvprasoon · April 25, 2019, 1:13pm

I recommend formatting your code for better reading.

rs1 · April 25, 2019, 3:23pm

#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()