Imports a folder of Excel files into SQL folders

ExcelToSqlExample.ps1

###########
## SETUP ##
###########

## get some data into xlsx files :)
$svr = Connect-DbaInstance -SqlInstance mssql1 -SqlCredential (Get-Credential)

foreach ($tbl in (Get-DbaDbTable -SqlInstance $svr -Database AdventureWorks2017 | where schema -eq 'sales' | select -first 10)){
    Invoke-DbaQuery -SqlInstance $svr -Database AdventureWorks2017 -Query "Select top 10 * from $($tbl.Schema).$($tbl.name)" |
    Export-Excel -Path "c:\temp\excel\$($tbl.name).xlsx"  -ExcludeProperty ItemArray, RowError, RowState, Table, HasErrors
}


############
## SCRIPT ##
############

## Database to put stuff in - I'm using $svr as my instance is a container and I'm connecting with sa,
## can just type instance name for SqlInstance and use Windows Auth
New-DbaDatabase -SqlInstance $svr -Name DataFromExcel

## Get Excel files

$excelFiles = Get-ChildItem C:\temp\Excel
foreach ($file in $excelFiles) {
    # store data from excel in variable
    $data = Import-Excel -Path $file.FullName

    # use data as InputObject (performance is better than piping)
    # -AutoCreateTable will do it's best... you should create table ahead of time to avoid crazy data types :)
    # batch size can be changed to whatever you like
    Write-DbaDataTable -SqlInstance $svr -Database DataFromExcel -Table $file.BaseName -AutoCreateTable -BatchSize 10000 -InputObject $data

    # cleanup variable
    Remove-Variable data
}

# you could also use this to do multiple at a time: https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-Parallel.ps1