Creating a fixed Length text file

I am currently in a big fix. The client wants my company to generate a fixed
length text file from our SQL Server 7 database. I already know how to import
their data by using a schema.ini but I have only found ways to export the
data from our system in a delimited format.

Does anyone know how to create a fixed length text file without manual manipulation
by users. All the code samples I have found is for creating delimited files
for export. It is imperative that I find a way because the client only wants
the data in a fixed length format.

Rob,

After the user defines the fields you are to export you can 'pad' the fields
then print them to a file. I suspect the file will have an extension .txt or
..dat or some such.

What I would do is write a function to do the padding. Add the following
code to a 'new' project's form1 code - It will pad various field types:

Private Sub Form_Load()
Dim iFn As Integer
Dim sRec As String
Const f1 = "Field1"
Const f2 = 2
Const f3 = 23.546
Dim f4 As Date
f4 = Now
sRec = sRec & DoPad(f1, 10, True, " ")
sRec = sRec & DoPad(f2, 5, False, "0")
sRec = sRec & DoPad(f3, 15, False, "0", "#0.00")
sRec = sRec & DoPad(f4, 10, False, " ", "YYYY/MM/DD")
Caption = sRec 'display the result somewhere
iFn = FreeFile
Open "c:\xxxx.txt" For Output As #iFn
Print #iFn, sRec
Close #iFn
End Sub


Private Function DoPad(vntData As Variant, iLen As Integer, _
bLeft As Boolean, sPadWith As String, _
Optional vntFormat As Variant)

Dim vntTemp As Variant

If Not IsMissing(vntFormat) Then
vntTemp = Format$(vntData, vntFormat)
Else
vntTemp = CStr(vntData)
End If
If bLeft Then
vntTemp = vntTemp & String$(iLen - Len(vntTemp), sPadWith)
Else
vntTemp = String$(iLen - Len(vntTemp), sPadWith) & CStr(vntTemp)
End If
DoPad = vntTemp
End Function

Cheers,
Larry Rebich

More tips link to:
http://www.buygold.net/tips.html

Please:
No personal e-mail questions :-)


"Rob Pesce" <rpesce@visto.com> wrote in message
news:3a0b3414$1@news.devx.com...
>
> I am currently in a big fix. The client wants my company to generate a
fixed
> length text file from our SQL Server 7 database. I already know how to
import
> their data by using a schema.ini but I have only found ways to export the
> data from our system in a delimited format.
>
> Does anyone know how to create a fixed length text file without manual
manipulation
> by users. All the code samples I have found is for creating delimited
files
> for export. It is imperative that I find a way because the client only
wants
> the data in a fixed length format.

Look at SQL's BCP (Bulk Copy Process) It's a commandline facility that is
extremely quick. You need to set up a schema file first, but once that's
set up it should be simple. The hard part is finding decent documentation
on the schema files format. I'd look at some 3rd party books at the local
book store.

...joe

"Rob Pesce" <rpesce@visto.com> wrote:
>
>I am currently in a big fix. The client wants my company to generate a
fixed
>length text file from our SQL Server 7 database. I already know how to
import
>their data by using a schema.ini but I have only found ways to export the
>data from our system in a delimited format.
>
>Does anyone know how to create a fixed length text file without manual manipulation
>by users. All the code samples I have found is for creating delimited files
>for export. It is imperative that I find a way because the client only
wants
>the data in a fixed length format.