Using Powershell to export from a SQL Server 2008 R2 to a fixed width text file

Question

I have a Powershell script that executes 2 SQL statements against a SQL Server 2008R2 database and at the moment it puts the data into 2 CSV files.

#Connection Strings
$Database = "*****SQL"
$Server = "L*******2008R2"
#SMTP Relay Server
#Export File
$AttachmentPath = "\\c******.csv"
$TempAttachmentPath = "\\cd*****.cvs"
$AttachmentPath2 = "\\p**********.cvs"
$TempAttachmentPath2 = "\\m**********.cvs"

# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "Select Distinct
  s*****t.s*****e,
  p*****n.p******y,
  p******n.p*****l,
  p*****n.p*****m + Replicate(' ', (50 - Len(p*****n.p*****am))),
  p*****n.p*****am,
  Case When Len(a*****s.*****en) > 0 Then RTrim(ad****s.a*****n) + ', ' Else ''
  End + ad****ss.a****t + Replicate(' ',(100 - len(Case When Len(ad*****s.a****n) > 0 Then RTrim(ad*****s.a******n) + ', ' Else ''
  End + a******s.a*****t))),
  a*******s.a******y,
  a*******s.a******n,
  a*******s.a******d,
  p*****on.p******b
From
  p*****n Inner Join
  s*****t On p****.pe****d = st****t.p****y Inner Join
  a*****s On pe****n.a****ey = a****s.a****d Inner Join
  e****l On s*****t.s*****d = e*****l.s*****y Inner Join
  c*****o On e****l.c*****y = c*****e.c*****d
Where
  s*****t.pa*****y = (Select
    Max(s.p*****y)
  From
    st*****nt s
  Where
    s.s*****de = st*****t.s******e) And
  p*****n.p*****e = 'S**T' And
  c****e.p*****ey >= 34
Order By
  s******t.s******de"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Export Hash Table to CSV File
$objTable | Export-CSV $TempAttachmentPath
gc $TempAttachmentPath | ? { $_ -notlike '#TYPE System.Data.DataRow' } | sc $AttachmentPath
(gc $AttachmentPath) | % {$_ -replace '"', ""} | out-file $AttachmentPath -Fo -En ascii
Remove-Item $TempAttachmentPath


$SqlQuery = "Select
  RTrim(c*****e.csecode) + Replicate(' ', 24 - Len(c*****e.****de)),
  Left(RTrim(c****e.c******c), 50) + Replicate(' ', 50 - Len(Left(c*****e.c*****c,50))),
  s*****s.pa*****r
From
  co****e Inner Join
  s****s On ****e.p****ey = s****rs.p****d
Where
  c*****e.p*****y >= 36"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Export Hash Table to CSV File
$objTable | Export-CSV $TempAttachmentPath2
gc $TempAttachmentPath2 | ? { $_ -notlike '#TYPE System.Data.DataRow' } | sc $AttachmentPath2
(gc $AttachmentPath2) | % {$_ -replace '"', ""} | out-file $AttachmentPath2 -Fo -En ascii
Remove-Item $TempAttachmentPath2

This code works however there is a new request. The user want this data in a text file with no delimiter just the column width from the database.

Table1

field1 Char(10)
field2 Char(12)
field3 Char(14)
Field4 Char(7)

Data in file:

Another   One         Bites         Bites  

I am new to Powershell so I would like some help please

Thanks Paul

Answer 1

Trying to use Powershell's export-csv and ADO.NET is more difficult than it's worth. BCP.exe which ships with SQL Server as well as SSIS provide native ability to generate fixed length export files. Here's an example using bcp.exe which you can run in Powershell (other than create and insert test generation):

USE tempdb;

CREATE TABLE t1 (
   charcol CHAR(16) NULL, 
   varcharcol VARCHAR(16) NULL, 
   varbinarycol VARBINARY(8)
);
GO
INSERT INTO t1 VALUES ('No blanks', 'No blanks', 0x00ee);
INSERT INTO t1 VALUES ('Trailing blank ', 'Trailing blank ', 0x00ee00);
GO

#Generate BCP format file
bcp tempdb..t1 format nul -c -f "C:\Users\Public\fixedlentext.fmt" -T -S MyServer

#Edit generated file
10.0
3
1       SQLCHAR             0       16      "\t"     1     charcol                      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       16      "\t"     2     varcharcol                   SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       17      "\r\n"   3     varbinarycol                 ""


#Replace "\t" with "" for fixed length and save file
10.0
3
1       SQLCHAR             0       16      ""     1     charcol                      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             0       16      ""     2     varcharcol                   SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             0       17      "\r\n"   3     varbinarycol                 ""

#bcp out fixed length using format file
bcp "SELECT * FROM tempdb..t1" queryout "C:\Users\Public\fixedlentext.txt" -c -T -SMyServer -f "C:\Users\Public\fixedlentext.fmt"

Answer 2

It is possible, but it'll be ugly. Fixed-width file formats are terrible to deal with.

Keep everything leading up to the export-csv. You'll have to dump the content out to file manually after that. From ADO.NET, you can get the widths of each field.

Once you have the width of each field, you will need to loop through each row in the DataSet, and for each field get the value, then pad it out to the required length. Construct a single string for the row, then append it to your output file with out-file.

You'll have to do the same for the header as well (and write that to your output file before the loop in the paragraph above) - but what if a field name is longer than the data contained in it? Or are the headers not required?

It's a lot of extra work, and it'll run slower than your current script (especially after you fix the current script to use export-csv with the appropriate parameters as I noted in my comment above).