Using powershell fetch data from Excel and write it to CSV file

Question

Hi was trying to fetch data from a Excel file and save it as a CSV using powershell. I need column B to be the header and values in Column D in the second row. I have 53 rows.

screenshot of the Excel file

I wrote the below powershell code which is saving as a CSV file but the encoding is not proper. When I use terraform csvcode, it throws error.

$strPath = "C:\project.xlsx"
$AssetInv = "C:\project.txt"
$AssetInvcsv = "C:\project.csv"
$objExcel = New-Object -ComObject Excel.Application
$objExcel.Visible = $false

$WorkBook = $objExcel.Workbooks.Open($strPath)
$worksheet = $workbook.sheets.item("Sheet1")

$c = 2
for ($r = 3; $r -le 53; $r++)
{
    $Data = 0
    $Data = $worksheet.Cells.Item($r, $c).Value2
    $Data1= [string]$Data
    $Data1 + "," | Out-File -filepath $AssetInv -append -NoNewline -Encoding utf8
}
$c = 4
for ($r = 3; $r -le 53; $r++)
{
    $Data = 0
    $Data = $worksheet.Cells.Item($r, $c).Value2
    $Data1= [string]$Data
    $Data1 + "," | Out-File -filepath $AssetInv -append -NoNewline -Encoding utf8
}
$objExcel.quit()

I am able to correct this issue by saving the file as a TXT file and then importing and exporting as CSV again. I donot want to save it as txt and again Import and export as CSV. How do I do that ?

import-csv $AssetInv -delimiter "," | export-csv $AssetInvcsv -NoTypeInformation

postanote · Accepted Answer · 2020-04-30 20:35:25Z

0

Take look at the help files for the Export-Csv cmdlet. Yet, if this is a real .xl* file, then the Excel OM, provides methods open a .xl*, do stuff, and save as .csv

$excel               = New-Object -ComObject Excel.Application
$excel.Visible       = $true
$excel.DisplayAlerts = $true

$wb    = $excel.Workbooks.Open($filepath)
# Do all your Excel file manipulations here

# Save, close, clean-up
$wb.SaveAs('D:\Temp\FileData.csv',6,'')
$excel.Quit()
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

answered Apr 30, 2020 at 20:35

postanote

16.4k3 gold badges17 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using powershell fetch data from Excel and write it to CSV file

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related