mirror of
https://github.com/dfinke/ImportExcel.git
synced 2025-12-06 00:23:20 +00:00
96493e059b
If data in the Excel file contains single quotes, then the insert will bomb as it will be malformed. Quotes would need escaped. Not all languages will support the same escape method, so I recommend it being set by an optional parameter.
Example of what will fail:
Name
-----------
Fry's
INSERT INTO [Name] Values ( 'Fry's' )
Corrected for quotes (such as needing to escape a single quote with an additional single quote)
INSERT INTO [Name] Values ('Fry''s')
Example code:
$ConvertToSqlParams = @{TableName = ‘dbo.Names’
Path = ‘C:\temp\data.xlsx’
ConvertEmptyStringsToNull = $true
UseMSSQLSyntax = $true
SingleQuoteStyle = "''"
}
$SQLInsert = ConvertFrom-ExcelToSqlInsert @ConvertToSqlParams
57 lines
1.8 KiB
PowerShell
57 lines
1.8 KiB
PowerShell
function ConvertFrom-ExcelToSQLInsert {
|
|
[CmdletBinding()]
|
|
param(
|
|
[Parameter(Mandatory = $true)]
|
|
$TableName,
|
|
[Alias("FullName")]
|
|
[Parameter(ValueFromPipelineByPropertyName = $true, ValueFromPipeline = $true, Mandatory = $true)]
|
|
[ValidateScript( { Test-Path $_ -PathType Leaf })]
|
|
$Path,
|
|
[Alias("Sheet")]
|
|
$WorksheetName = 1,
|
|
[Alias('HeaderRow', 'TopRow')]
|
|
[ValidateRange(1, 9999)]
|
|
[Int]$StartRow,
|
|
[string[]]$Header,
|
|
[switch]$NoHeader,
|
|
[switch]$DataOnly,
|
|
[switch]$ConvertEmptyStringsToNull,
|
|
[switch]$UseMsSqlSyntax,
|
|
[Parameter(Mandatory = $false)]
|
|
$SingleQuoteStyle
|
|
)
|
|
|
|
$null = $PSBoundParameters.Remove('TableName')
|
|
$null = $PSBoundParameters.Remove('ConvertEmptyStringsToNull')
|
|
$null = $PSBoundParameters.Remove('UseMsSqlSyntax')
|
|
$null = $PSBoundParameters.Remove('SingleQuoteStyle')
|
|
|
|
$params = @{} + $PSBoundParameters
|
|
|
|
ConvertFrom-ExcelData @params {
|
|
param($propertyNames, $record)
|
|
|
|
$ColumnNames = "'" + ($PropertyNames -join "', '") + "'"
|
|
if($UseMsSqlSyntax) {
|
|
$ColumnNames = "[" + ($PropertyNames -join "], [") + "]"
|
|
}
|
|
|
|
$values = foreach ($propertyName in $PropertyNames) {
|
|
if ($ConvertEmptyStringsToNull.IsPresent -and [string]::IsNullOrEmpty($record.$propertyName)) {
|
|
'NULL'
|
|
}
|
|
else {
|
|
if ( $SingleQuoteStyle ) {
|
|
"'" + $record.$propertyName.ToString().Replace("'",${SingleQuoteStyle}) + "'"
|
|
}
|
|
else {
|
|
"'" + $record.$propertyName + "'"
|
|
}
|
|
}
|
|
}
|
|
$targetValues = ($values -join ", ")
|
|
|
|
"INSERT INTO {0} ({1}) Values({2});" -f $TableName, $ColumnNames, $targetValues
|
|
}
|
|
}
|