Formating Files with spaces

[pkw25]

Hello

WE will be receiving a file from the postoffice with details of daily payments through their offices. We then have to reconcile these payments on our system. The problem is the file they send is unformatted and it's upto us to seperate the fields. It looks like

H99Smart Telecom 01030101
D02090440012388282260209001759450120024500103012802010000101
D02440195021640762260244040184800120011340103012802010000101
D02440195021642832260244041088050120048180103012802010000101
D02440195021643202260244040171780120003410103012802010000101
D02440195021643212260244035153170120018160103012802010000101
D02440195021643642260244032490750120027310103012802010000101

I know I can seperate the fields by saving as .CSV and then picking fields manually but this is prone to error as we will be receiving a new file every day.
I've been trying to use the Mid function in VB but I'm Not really getting anywhere
Any Help would be appreciated.

Paraic Walsh

 

[bryanbayfield]

You don't say how you want your fields separarted?

How many fields do you want it split into, how to determine where the split lies (after a certain number of characters, after a certain character) etc etc.

Bit hard to help you without that info! SuperBry!

 

[SkipVought]

pkw,

You have to ask yourself the question, "What are the 'rules' for processing this file?" For instance, it looks as if there may be only two types of lines. How would you process each line into meaningful data elements? How are the two types of lines related datawise?

You need to write down the rules and distill them into a succinct and orderly process. More than likely, with files of this type (ie multiple types of lines of data) you will have to have a procedure (VBA program) that reads each line of data, determine what type that line is in order to process that line properly.

Hope this helps a bit ;-) Skip,
SkipAndMary1017@mindspring.com

 

[pkw25]

Thanks for your response and
Sorry about that omission.
The fields from left to right are described as follows

FIELD1 GROF 4 CHARACTERS
FIELD2 BATCH NO. 4 CHARACTERS
FIELD3 CAPTURE POSITION 2 CHARACTERS
FIELD4 MSG SEQ NO. 6 CHARACTERS
FIELD5 TRANS CODE 3 CHARACTERS
FIELD6 GROF 4 CHARACTERS
FIELD7 ACCOUNT NUM 11 CHARACTERS
FIELD8 E AMOUNT 6 CHARACTERS
FIELD9 SYSDATE(YYMMDD) 6 CHARACTERS
FIELD10 TRANSACTION DATE(DDMMYY)6 CHARACTERS
FIELD11 START CODELINE 7 CHARACTERS

 

[SgtJarrow]

Here's the code string that I came up with to tackle this problem.....Hope you can use it...


'*************Start Code**********************
Public Function ParseString(ByVal strString As String)

' Input String Format
' D02090440012388282260209001759450120024500103012802010000101
' D02440195021640762260244040184800120011340103012802010000101
' D02440195021642832260244041088050120048180103012802010000101
' D02440195021643202260244040171780120003410103012802010000101
' D02440195021643212260244035153170120018160103012802010000101
' D02440195021643642260244032490750120027310103012802010000101

'Output Fields
' FIELD1 GROF 4 CHARACTERS
' FIELD2 BATCH NO. 4 CHARACTERS
' FIELD3 CAPTURE POSITION 2 CHARACTERS
' FIELD4 MSG SEQ NO. 6 CHARACTERS
' FIELD5 TRANS CODE 3 CHARACTERS
' FIELD6 GROF 4 CHARACTERS
' FIELD7 ACCOUNT NUM 11 CHARACTERS
' FIELD8 E AMOUNT 6 CHARACTERS
' FIELD9 SYSDATE(YYMMDD) 6 CHARACTERS
' FIELD10 TRANSACTION DATE(DDMMYY) 6 CHARACTERS
' FIELD11 START CODELINE 7 CHARACTERS

' GROF BTCH CP MSG NO TXN GROF ACCT NO E AMNT SYSDTE TXNDTE CDELINE
' D 0244 0195 02 164364 226 0244 03249075012 002731 010301 280201 0000101
' 1 2345 6789 11 111111 112 2222 22222333333 333344 444444 445555 5555556
' 01 234567 890 1234 56789012345 678901 234567 890123 4567890

Dim db As Database, rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Table1", dbOpenTable)

With rs
.AddNew
.Fields("FIELD1&quot = Mid(strString, 2, 4)
.Fields("FIELD2&quot = Mid(strString, 6, 4)
.Fields("FIELD3&quot = Mid(strString, 10, 2)
.Fields("FIELD4&quot = Mid(strString, 12, 6)
.Fields("FIELD5&quot = Mid(strString, 18, 3)
.Fields("FIELD6&quot = Mid(strString, 21, 4)
.Fields("FIELD7&quot = Mid(strString, 25, 11)
.Fields("FIELD8&quot = Mid(strString, 36, 6)
.Fields("FIELD9&quot = Mid(strString, 42, 6)
.Fields("FIELD10&quot = Mid(strString, 48, 6)
.Fields("FIELD11&quot = Mid(strString, 54, 7)
.Update
End With

End Function
"As far as the laws of mathematics refer to reality, they are not certain; as far as they are certain, they do not refer to reality."--Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[SkipVought]

What about the

H99Smart Telecom 01030101

record??? It seem like you have Header and Detail records


I'd think that you could parse the string into an array and your function return the array. Then your Main would process the array into the recordset for update -- ie take the database calls OUT of your function.

In your Main you...
1. open the db resordset
2. open the data file

then in a loop you
1. read a record
2. parse either header of detail via function
3. populate the recordset fields and update

when you are done reading the file
1. close the file
2. close the db recordset

hope this helps Skip,
SkipAndMary1017@mindspring.com