This repository has been archived by the owner on Oct 14, 2020. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Fmt-Create-lg.ps1
59 lines (46 loc) · 2.4 KB
/
Fmt-Create-lg.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
<#
.Synopsis
This function runs BCP utility for exporting the corresponding *.fmt file for bulk import utility for a db table
.Description
This runs once per local DB instance, currently supports only local DB. Exports an fmt file with the default settings:
format nul -f = for generating non-XML file
-c = for character set information
-t, = sets the "," table column delimiter for the fmt import file
-S = specifying a corresponding DB server name that the DB/table resides in
-T = using DB trusted connection via integrated security for creating the format. file
.Parameter DBServName
The local Sql server instance that the bcp utility is run against
.Parameter DBName
The local database name to connect with
.Parameter TableName
The database table name that will be used by bcp to generate the corresp. formatting file
.Example
PS :\> .\Fmt-Create.ps1
BCP import file generated OK..
The layout of the output file (per table) is shown below:
12.0
7
1 SQLCHAR 0 21 "," 1 ColumnKey ""
2 SQLCHAR 0 80 "," 2 ColumnName1 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 80 "," 3 ColumnName2 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 200 "," 4 Name3 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 12 "," 5 Name4 ""
6 SQLCHAR 0 1 "," 6 Column5 ""
7 SQLCHAR 0 400 "\r\n" 7 Column6 SQL_Latin1_General_CP1_CI_AS
#>
Function Get-FmtFile()
{
param(
[string]$DBServName,
[string]$DBName,
[string]$TableName
)
if (-not(Test-Path "$env:USERPROFILE\Documents\$TableName-layout.fmt")) {
$DBServName = "$([System.Environment]::MachineName)\{0}" -f $DBServName; $DBSel = "{0}.dbo.{1}" -f $DBName, $TableName
$args = @("$DBSel format nul","-c -f $env:USERPROFILE\Documents\$TableName-layout.fmt -t, -S $DBServName -T")
Start-Process -FilePath "bcp" -ArgumentList $args -WindowStyle Hidden
Write-Host "BCP import file generated OK.."
}
else {Write-Host "File already exists, run the script with different args.."}
}
Get-FmtFile -DBServName "YourLocalServerInstanceName" -DBName "YourDBName" -TableName "YourTableName"