Split large excel file to multiple smaller file by user defined rows through powershell

Question

Im looking to split large excel file into multiple excel file

My sample excel file

Name Value value2
abc1  10    100
abc2  20    200
abc3  30    300
abc4  40    400
abc5  50    500
abc6  60    600
abc7  70    700
abc8  80    800

Expected result

Batch1.xlsx

Name Value Value2
abc1 10    100
abc2 20    200
abc3 30    300

Batch2.xlsx

Name Value Value2
abc4 40    400
abc5 50    500
abc6 60    600

Batch3.xlsx

Name Value Value2
abc7 70    700
abc8 80    800

Myscript strucks in loops .. As a beginner looking for some assistance ..

Example: user choosing data to be splitted with 3 rows ., If have 8 rows data of input file ., file3.xlsx can keep remaining 2 rows .

$nom = Read-Host 'Enter number of rows of data want to be in a file'

$nom = [int]$nom + [int]1

$nom1 = 'A'+ $nom

$nxc = 100

$meto = 'A1'



For ($nom; $nom -le $nxc) {
$excel = New-Object -ComObject Excel.Application
$excel.visible = $true
$workbook = $excel.workbooks.open("C:\Users\admin\Desktop\rax\Master\in.xlsx")
$worksheet = $workbook.sheets.item("Sheet1") 
$worksheet.Range("$meto","$nom1").EntireRow.copy()

$wb2=$excel.workbooks.open("C:\Users\admin\Desktop\rax\out.xlsx")
$targetRange=$wb2.Worksheets.Item('Sheet1').Range("A1").EntireRow
$wb2.Worksheets.Item('Sheet1').Activate()
$targetRange.PasteSpecial(-4163)
$meto = $wb2.Worksheets.Item('Sheet1').UsedRange.Rows.Count
$wb2.RefreshAll()
$wb2.Save() 
$workbook.Worksheets.Item('Sheet1').Activate()
$met = $workbook.Worksheets.Item('Sheet1').UsedRange.Rows.Count
$nxc = $met

$meto = [int]$meto + [int]1


$nom = [int]$meto - [int]1
$nom = [int]$nom + [int]$nom
$nom
$excel.Quit()

} 

Answer 1

You can utilize an awesome module developed by Doug finke . Import-Excel

below code will solve you problem.

$r=@()
$t=$C=1
Import-Excel -Path C:\Temp\test.xlsx|Foreach-Object -Process {
    #Append rows in an array
    $r += $_

    #Save in a new excel when count reaches 3
    if($C -eq 3){
        $r | Export-Excel -Path C:\Temp\test_$t.xlsx

        #reset values
        $r=@()
        $c=1
       $t++
    }
    else{
        #increment row count
        $c++
    }
}

#save remaining rows
$r|Export-Excel -Path C:\Temp\test_$t.xlsx

You can rename variables accordingly.