CODEWITHSUNDEEP
powershell
Błażej
Błażej

Reputation: 137

create csv from xls using powershell

I want create powershell script which create me csv file from .xls file but I don't know excacly how to use powershell wihout vba.

So far i have this :

ConvertTo-Csv "C:\Users\Me\TestsShella\test.xlsx"  | Out-File Q:\test\testShella.csv

But it doesn't working.

Upvotes: 0

Views: 7490

Answers (3)

LCabaAres
LCabaAres

Reputation: 15

Maybe this could work:

rename-item -Path "C:\Users\Me\TestsShella\test.xlsx" -NewName "item.csv"

you will get a message when open the CSV, but the format of CSV is like XLSX.

Upvotes: 0

user6811411
user6811411

Reputation:

With Excel present on the running machine use it as a COM-object:

## Q:\Test\2019\01\31\SO_54461362.ps1

$InFile = Get-Item "$($Env:USERPROFILE)\TestsShella\test.xlsx"
$OutFile= $InFile.FullName.replace($InFile.Extension,".csv")

$Excel = new-object -ComObject "Excel.Application"
$Excel.DisplayAlerts = $True
$Excel.Visible = $False # $True while testing

$WorkBook = $Excel.Workbooks.Open($InFile.FullName)
$WorkBook.SaveAs($OutFile, 6) # 6 -> type csv
$WorkBook.Close($True)

$Excel.Quit()
[void][System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel)

Depending on the locale (decimal point/comma) the csv file will either be comma or semicolon seperated.

Without Excel being installed, use the already suggest module ImportExcel

$InFile = Get-Item "$($Env:USERPROFILE)\TestsShella\test.xlsx"
$OutFile= $InFile.FullName.replace($InFile.Extension,".csv")

Import-Excel $Infile.FullName | Export-Csv $OutFile -NoTypeInformation

This yields a .csv file with all fields double quoted and comma seperated.

Upvotes: 2

Fazer87
Fazer87

Reputation: 1242

There is a prebuilt library for this:

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

You will then have the import-excel function/cmdlet available to you and will be able to import, convert to csv and then export

Upvotes: 0

Related Questions