Import Xcel file

Question

Post reply

Import Xcel file

Bruin

SSCrazy

Points: 2058

July 14, 2023 at 2:45 pm

Go to Answer

#4235128

$File = "C:\CostLoads\Book1.xlsx"
$Instance = "xxxxx"
$Database = "DBA_USAGE"

$fileName =  [System.IO.Path]::GetFileNameWithoutExtension($File)

foreach($sheet in Get-ExcelSheetInfo $File)
{

$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = $fileName + '-' + $sheet.Name

Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -AutoCreateTable -Table $tablename 

}

I'm trying to import my xcel file into SQL but receive this message.

--The certificate chain was issued by an authority that is not trusted--

Michael L John

One Orange Chip

Points: 27055
More actions
July 17, 2023 at 5:37 pm
Answer
#4237432
Bruin wrote:
$File = "C:\CostLoads\Book1.xlsx"
$Instance = "xxxxx"
$Database = "DBA_USAGE"

$fileName = [System.IO.Path]::GetFileNameWithoutExtension($File)

foreach($sheet in Get-ExcelSheetInfo $File)
{

$data = Import-Excel -Path $File -WorksheetName $sheet.name | ConvertTo-DbaDataTable
$tablename = $fileName + '-' + $sheet.Name

Write-DbaDataTable -SqlInstance $Instance -Database $Database -InputObject $data -AutoCreateTable -Table $tablename

}
I'm trying to import my xcel file into SQL but receive this message. --The certificate chain was issued by an authority that is not trusted--
Look up Get-Credential in DBATools
$cred = Get-Credential
$srv = Connect-DbaSqlServer -Sqlserver MyServer -Credential $cred
I'm pretty sure that creating a credential object, where you can set "TrustServerCertificate"
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Duplicate of https://www.sqlservercentral.com/forums/topic/import-xcel-file

Bruin SSCrazy Points: 2058 More actions · Answer 2

Bruin

SSCrazy

Points: 2058

July 14, 2023 at 4:06 pm

#4235186

sorry hit enter twice connection froze.

Bruin SSCrazy Points: 2058 More actions · Answer 3

# Variables for Import-Excel
$FolderName         = "c:\fileloading\"
$FileName           = "book1.xlsx"
$FileNameWithFolder = join-path $FolderName $FileName
$FileNameWithFolder
#Variables for Write-SQLTableData

$SQLServerInstance = "SQLEXPRESS"
$Database          = "cb_staging"
$SchemaName        = "dbo"
$TableName         = "Tmp_Parts"

,(Import-Excel -path $FileNameWithFolder -ErrorAction Stop) |
      Write-SqlTableData -serverinstance $SQLServerInstance -DatabaseName $Database -SchemaName $SchemaName -TableName $TableName  -force  -ErrorAction Stop

I have this working, but how can I do multiple Tabs in spreadsheet to different tables?

Thanks.

Bruin SSCrazy Points: 2058 More actions · Answer 4

This code seems to find each TAB, but how can I combine with above script to load both tabs to different Tables in SQL?

param(
    [Parameter(Mandatory)]
    $path
)


$hash = @{ }

$e = Open-ExcelPackage $path

foreach ($sheet in $e.workbook.worksheets) {
    $hash[$sheet.name] = Import-Excel -ExcelPackage $e -WorksheetName $sheet.name
}

Close-ExcelPackage $e -NoSave

$hash