CODEWITHSUNDEEP
excelpowershellcsvxlsx
user3055794
user3055794

Reputation: 1

Powershell append data into existing XLSX with multiple sheets

New to Powershell and looking to learn.

Goal: Trying to take the Data out of a .csv file (14 cells of data per row) and import into an existing .xlsx file Starting on the second row columns (A2:N2).

The .xlsx file has 4 sheets with the one I am looking to edit being labeled "Data". Data sheet/tab has 18 columns, the first 14 are where I would like the imported data starting on row (A2:N2-> End will vary).

Looking for a way to automate the report by filling rows A-N with data from a file (.csv) which gets generated automatically.

Sample of "Data" tab with some values:

Sample of "Data" tab with some values

Current process is to open one xls file and copy/past into cells starting at A2. Looking to automate this and have automated the report -> Emails .xls file, which I convert to .csv and remove some titles and extra info which is not needed using the following code:

Function ExcelCSV ($File)
{
    $pwd = "C:\Users\..." #Removed local path
    $excelFile = "$pwd\" + $File + ".xls"
    $Excel = New-Object -ComObject Excel.Application
    $Excel.Visible = $false
    $Excel.DisplayAlerts = $false
    $wb = $Excel.Workbooks.Open($excelFile)
    foreach ($ws in $wb.Worksheets)
    {
        $ws.SaveAs("$pwd\" + $File + ".csv", 6)
     }
    $Excel.Quit()
 }
$TestFile = (Get-Content .\FileName.xls) -replace 'null',''
$TestFile | Out-File Test.xls
$FileName = "Test"
ExcelCSV -File $FileName
Get-Content Test.csv | Select-Object -Skip 2 | Select-Object -SkipLast 3 | Set-Content Test2.csv

Upvotes: 0

Views: 5943

Answers (1)

Tomek
Tomek

Reputation: 671

Please use great ImportExcel powershell module ImportExcelModule using it You can achieve Your goal by simply doing so

$csv=Import-CSV <YourImportParameters>  
$csv|Export-Excel -Path $pwd -Show  -StartRow 2 -StartColumn 2 -sheet $sheetname

Above will take the object and export it to excel file $pwd,sheet $sheetname starting from second row of second column
If You want to send that via mail to someone afterwards - Powershell can help You do that in 1 line too :)

Upvotes: 2

Related Questions