|
Subject:
|
generating a fixed length export file
|
|
Posted By:
|
cooky4
|
Post Date:
|
10/23/2003 1:59:48 PM
|
I am trying to generate a fixed field lenght file from a database. i have the code to get a comma delimited file but the client wants a fixed length.
Is there something i can change in the current code that i have to get the proper file?
the code i am using is from the Beginning VB6 Database book, page 770-772. see below
Thanks for anyhelp
doug
Public Function csv(adoRecordset As ADODB.Recordset) As Boolean
Dim iTotalRecords As Integer
Dim sFileToExport As String
Dim iFileNum As Integer
Dim msg As String
Dim iIndx As Integer
Dim iNumberOfFields As Integer
Screen.MousePointer = vbDefault
On Error Resume Next
With CD1
.CancelError = True
.FileName = "Export.csv"
.InitDir = App.Path
.DialogTitle = "Save Comma Delimited Export File"
.Filter = "Export Files (*.CSV)|*.CSV"
.DefaultExt = "CSV"
'.Flags = cd1OFNOverwritePrompt Or cd1OFNCreatePrompt
.ShowSave
End With
If (Err = 32755) Then
Screen.MousePointer = vbDefault
Beep
msg = "the export operation was canceled." & vbCrLf
iIndx = MsgBox(msg, vbOKOnly + vbInformation, "comma Deleimited Export File")
csv = False
Exit Function
Else
On Error GoTo experror
End If
Screen.MousePointer = vbHourglass
iTotalRecords = 0
sFileToExport = CD1.FileName
iFileNum = FreeFile()
Open sFileToExport For Output As #iFileNum
iNumberOfFields = adoRecordset.Fields.Count - 1
adoRecordset.MoveFirst
Do Until adoRecordset.EOF
iTotalRecords = iTotalRecords + 1
'For iIndx = 0 To iNumberOfFields
'If (IsNull(adoRecordset.Fields(iIndx))) Then
' Print #iFileNum, ",";
'Else
'If iIndx = iNumberOfFields Then
'Print #iFileNum, Trim$(CStr(adoRecordset.Fields(iIndx)));
'Else
' Print #iFileNum, Trim$(CStr(adoRecordset.Fields(iIndx))); ",";
'End If
'End If
'Next
Print #iFileNum,
adoRecordset.MoveNext
DoEvents
Loop
Close iFileNum
Screen.MousePointer = vbDefault
Beep
msg = "Export File " & sFileToExport & vbCrLf
msg = msg & "successfully created." & vbCrLf
msg = msg & iTotalRecords & " records written to disk." & vbCrLf
iIndx = MsgBox(msg, vbOKOnly + vbInformation, "Comma delimited file")
csv = True
Exit Function
experror:
Screen.MousePointer = vbDefault
MsgBox (Err & " " & Err.Description)
csv = False
End Function
|
|
Reply By:
|
pgtips
|
Reply Date:
|
10/24/2003 7:26:41 AM
|
Well you can just change the bits you've commented out. No-one is going to be able to tell you what you need to change it to unless you say what format you need - just saying fixed-length isn't very helpful. We need to know:
-how many fields?
-what length should each field be fixed to?
-if a string is shorter than its allocated length should it be padded wth spaces to the left or to the right?
-if a number is to be written, should it be padded with leading-zeros or spaces?
rgds
Phil
|
|
Reply By:
|
cooky4
|
Reply Date:
|
10/27/2003 2:15:47 PM
|
Each field will have a different length depending on how the database fields were set. i want to be able to pull the data just like i was on the SQL server and going to data transformation services and exporting data to a flat file.
|
|
Reply By:
|
sal
|
Reply Date:
|
10/27/2003 2:42:49 PM
|
Create a DTS package to do exactly what you need it to do. then save it as a vb application and view the source code, or just use the DTS vb file for your purposes.
Sal
|
