Text manipulation 2

  • Thread starter Thread starter jmonty
  • Start date Start date
J

jmonty

Ok. Still a little cloudy, but I think what you said was
that you can clean the file up into a 3 column ("neat-
rowed") table (Table1) right?
Again make sure the field sizes are set to the correct
length, then loop throught the records creating a new
fixed-width text file:
Field1 & 2 are the names of the fields in your table, use
as many as you need. Use Trim to remove any leading or
trailing spaces and format them to the Field Size as I
described in the last post:

Sub ExportTextFile()
Dim db As Database
Dim rst As Recordset
Dim Directory As String
Dim MyString As String
Set db = CurrentDb
'Get the current directory and create new
'textfile for output
Directory = (Mid(db.NAME, 1, Len(db.NAME) _
- Len(Dir(db.NAME))))
Open Directory & "\TestOutput.txt" For Output As #1
Set rst = db.OpenRecordset("Table1", dbOpenDynaset)
rst.MoveFirst
Do While Not rst.EOF
MyString = Format(Trim(rst![Field1]),"@@@@@@@@@@") _
& Format(Trim(rst![Field2]), "00000.00" & Chr(13)
Print #1, MyString
rst.MoveNext
Loop
Close #1
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Look in the same directory that the db resides in for the
new fixed-width text file named "TestOutput.txt"
Use this new, clean file to import.
Hope this helps.
 
My bad - I missed a )


MyString = Format(Trim(rst![Field1]),"@@@@@@@@@@") _
& Format(Trim(rst![Field2]), "00000.00") _
& Chr(13)

sorry.

-----Original Message-----
Ok. Still a little cloudy, but I think what you said was
that you can clean the file up into a 3 column ("neat-
rowed") table (Table1) right?
Again make sure the field sizes are set to the correct
length, then loop throught the records creating a new
fixed-width text file:
Field1 & 2 are the names of the fields in your table, use
as many as you need. Use Trim to remove any leading or
trailing spaces and format them to the Field Size as I
described in the last post:

Sub ExportTextFile()
Dim db As Database
Dim rst As Recordset
Dim Directory As String
Dim MyString As String
Set db = CurrentDb
'Get the current directory and create new
'textfile for output
Directory = (Mid(db.NAME, 1, Len(db.NAME) _
- Len(Dir(db.NAME))))
Open Directory & "\TestOutput.txt" For Output As #1
Set rst = db.OpenRecordset("Table1", dbOpenDynaset)
rst.MoveFirst
Do While Not rst.EOF
MyString = Format(Trim(rst![Field1]),"@@@@@@@@@@") _
& Format(Trim(rst![Field2]), "00000.00" & Chr(13)
Print #1, MyString
rst.MoveNext
Loop
Close #1
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

Look in the same directory that the db resides in for the
new fixed-width text file named "TestOutput.txt"
Use this new, clean file to import.
Hope this helps.


-----Original Message-----
JMonty,
You are right in that I wish to create a Fixed-
width textfile which I can then import into either Access
or VB6 using an array based on a custom datatype. The
reason for doing this is that I have found the Access
TransferText function to be unreliable, in that I can
import a file which contains many hundreds of records and
for no apparent reason it may miss one. Import the same
file under the same conditions again and it may complete.
While the error rate is small, the data is financial in
nature and needs to be correct!.
The text as it stands is more of a print file than fixed
width, While the detail I wish to extract is in columns, a
single record of three fields maybe spread over many lines
and the number of lines can vary. At the moment I chop
the file up into columns using the access text import
feature and then use a query to delete lines which don't
contain any of the three fields I am interested in. A bit
of code then does some string manipulation to get the
three fields lined up into neat rows.
The access importer must, some-how, strip out all the
carriage returns and make each line the same length before
actually importing it, it is this which I wish to emulate.

Another way maybe to run through the file in MS Word, and
place one of three characters (which are not already used)
in-front of each of the fields. Then go through the file
in VB looking for one of the three special characters,
this sounds slow though and would require Word automation
which I don't really know.

Any ideas :-)

.
Click to expand...
.
Click to expand...
 
Back
Top