Using ADO DB to Manipulate Space Character Filled Strings

[bennybe...@yahoo.com]

Note: This has also been posted in microsoft.public.scripting.vbscript

I'm attempting to use ADO to manipulate data in fixed width text files
as shown here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/scri...

The issue I run into is that the fields which are all spaces or have
trailing spaces end up with the spaces as null. Is there a way to
force the OLE DB provider to bring them in as space characters? For
example, store 2 spaces if the 2 character field is 2 spaces in the
fixed width import file.

Included below is an excerpt of my VBscript and schema.ini file. I
have
tried changing the field type to text in the .ini file and obtained the

same result.

Script:
On Error Resume Next

' Assign constants
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
Const FOR_APPENDING = 8

' Assign file string variables
strPathtoTextFile = "E:\Temp\"
strOutputTextFile = "temp.txt"
strToHostFile = "ALL_TOHOST.txt"

' Create ADO objects
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

' Create file system object for handling text files
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open output text file
Set objTextFile = objFSO.OpenTextFile _
(strPathtoTextFile & strOutputTextFile, FOR_APPENDING, True)

Wscript.Echo "Opening OLEDB Connection"

' Open connection for input text file with OLEDB driver
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=NO;FMT=FixedLength"""

Wscript.Echo "Selecting Records"

' Select records from input file
objRecordset.Open "SELECT * FROM " & strToHostFile, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText

' Obtain field count
strFieldsCount = objRecordset.Fields.Count

' Build record string and write to file
If strFieldsCount > 0 Then
Do Until objRecordset.EOF
For intLoop = 0 To (strFieldsCount - 1)
strRecord = strRecord &
objRecordset.Fields.Item(intLoop)
Next
objTextFile.WriteLine(strRecord)
strRecord = ""
objRecordset.MoveNext
Loop
End If

objTextFile.Close

Wscript.Echo "Script Complete"

Schema.ini
[ALL_TOHOST.TXT]
ColNameHeader = False
CharacterSet = ANSI
Format=FixedLength
Col1=FIELD1 char Width 1
Col2=FIELD2 char Width 2
Col3=FIELD3 char Width 14