forked from dfinke/ImportExcel
-
Notifications
You must be signed in to change notification settings - Fork 0
/
ConvertFromExcelToSQLInsert.ps1
150 lines (127 loc) · 4.71 KB
/
ConvertFromExcelToSQLInsert.ps1
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
#Escapes single occurences of characters in a string. Double occurences are not escaped. e.g. ''' will become '''', NOT ''''''.
#Default is to replace single quotes
#Sourced from: https://stackoverflow.com/a/33956262/270794
Function EscapeChar {
param(
[Parameter(Mandatory = $true)][String] $param,
[Parameter(Mandatory = $false)][String] $charToEscape
)
if ($charToEscape -eq '') {
$charToEscape = "'"
}
$cleanedString = ""
$index = 0
$length = $param.length
for ($index = 0; $index -lt $length; $index++) {
$char = $param[$index]
if ($char -eq $charToEscape) {
if ($index +1 -lt $length -and $param[$index + 1] -eq $charToEscape) {
++$index ## /!\ Manual increment of our loop counter to skip next char /!\
}
$cleanedString += "$charToEscape$charToEscape"
continue
}
$cleanedString += $char
}
return $cleanedString
}
function ConvertFrom-ExcelToSQLInsert {
<#
.PARAMETER ColumnMap
Ordered hashtable of input column names to output column names.
.EXAMPLE
An example
#>
param(
[Parameter(Mandatory = $true)]
$TableName,
[Alias("FullName")]
[Parameter(ValueFromPipelineByPropertyName = $true, ValueFromPipeline = $true, Mandatory = $true)]
[ValidateScript( { Test-Path $_ -PathType Leaf })]
$Path,
[Alias("Sheet")]
$WorkSheetname = 1,
$ColumnMap,
$LiteralColumns,
[switch]$Oracle = $false,
[switch]$Unique,
[switch]$Trim = $false,
[int]$HeaderRow = 1,
[string[]]$Header,
[switch]$NoHeader,
[switch]$DataOnly
)
$null = $PSBoundParameters.Remove('TableName')
$null = $PSBoundParameters.Remove('ColumnMap')
$null = $PSBoundParameters.Remove('LiteralColumns')
$null = $PSBoundParameters.Remove('Unique')
$null = $PSBoundParameters.Remove('Oracle')
$null = $PSBoundParameters.Remove('Trim')
$params = @{} + $PSBoundParameters
$script:raw_output = @()
ConvertFrom-ExcelData @params {
param($propertyNames, $record)
if ($LiteralColumns.Count -gt 0)
{
If ( $Oracle )
{
$ColumnNames = (( $LiteralColumns.Keys -join ", ") + ", ")
}
Else
{
$ColumnNames = ("'" + ( $LiteralColumns.Keys -join "', '") + "', ")
}
$values = foreach ($propertyName in $LiteralColumns.Keys) { $LiteralColumns[$propertyName] }
$targetValues = "'" + ($values -join "', '") + "',"
}
$iterator = @()
If ($ColumnMap.Count -gt 0)
{
$iterator = $ColumnMap.Keys
If ( $Oracle )
{
$ColumnNames += ( $ColumnMap.Values -join ", ")
}
Else
{
$ColumnNames += "'" + ( $ColumnMap.Values -join "', '") + "'"
}
}
Else
{
$iterator = $PropertyNames
$ColumnNames += "'" + ($propertyNames -join "', '") + "'"
}
$trimmedValues = foreach ($propertyName in $iterator)
{
If ( $Trim -eq $true -and $record.$propertyName -ne $null) {
$record.$propertyName.ToString().Trim()
}
else {
$record.$propertyName
}
}
$values = foreach ($value in $trimmedValues) { if ($value -ne $null) {EscapeChar($value)} else {$value}}
$targetValues += "'" + ($values -join "', '") + "'"
If ( $Unique -eq $true)
{
$script:raw_output += $("INSERT INTO {0} ({1}) Values({2});" -f $TableName, $ColumnNames, $targetValues)
} Else
{
"INSERT INTO {0} ({1}) Values({2});" -f $TableName, $ColumnNames, $targetValues
}
}
If ($Unique) { $script:raw_output | Sort-Object | Get-Unique }
# $data = Import-Excel @params
# $PropertyNames = $data[0].psobject.Properties |
# Where-Object {$_.membertype -match 'property'} |
# Select-Object -ExpandProperty name
# $ColumnNames = "'" + ($PropertyNames -join "', '") + "'"
# foreach ($record in $data) {
# $values = $(foreach ($propertyName in $PropertyNames) {
# $record.$propertyName
# })
# $targetValues = "'" + ($values -join "', '") + "'"
# "INSERT INTO {0} ({1}) Values({2});" -f $TableName, $ColumnNames, $targetValues
# }
}