Concatenate rows in same field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a text file I import to Access, which have a line break at pos. 80.
The string is up to 240 pos. long. Therefore, it is imported in several rows.

To be more precise, it is a .dat file.

How do I concatenate these rows or is it possible to concatente when
importing?

Each record starts with 940S

Field1
940SWI010095624130821 25 12502200206
28 0
0000/0020 60FC071005JPY000000000000000
940SWI050095624130821 25 12502200206
28 0
0000/0062FC071005JPY00000000000000064 C071005JPY000000000000000
940SWI010095624130821 25 12506012390
28 0
0000/0020 60FC071005EUR000000000000000
940SWI050095624130821 25 12506012390
28 0
0000/0062FC071005EUR00000000000000064 C071005EUR000000000000000
940SWI010095624130821 25 16024534328
28 0
0000/0020 60FC071005NOK000001050539237
940SWI020095624130821 25 16024534328
28 0
0000/0061 0710051005D 000000003393866 //
8200
46GIRO 712727 20071005
940SWI070095624130821 25 16024534328
28 0
0000/0086 GIRO

Ronny
 
Concatenation is to put strings TOGETHER. Yet you appear to want to break
strings APART. Which is it please?

If you want to break them apart, just use MIDSTR in VBA.
 
Hi Ronny,

Something like this should do it. It concatenates lines from the input
file until it meets one beginning with "940S", then writes the buffer
to the output file and starts concatenating again.

Sub ConcatenateLines(InFileName As String, _
OutFileName As String)
Dim lngIN As Long
Dim lngOut As Long
Dim Buf As String
Dim Line As String
Dim FirstTime As Boolean

lngIN = FreeFile()
Open InFileName For Input As #lngIN
lngOut = FreeFile()
Open OutFileName For Output As #lngOut

FirstTime = True
Buf = ""

Do Until EOF(lngIN)
Line Input #lngIN, Line
If (Not FirstTime) And (Line Like "940S*") Then
Print #lngOut, Buf
Buf = ""
End If
Buf = Buf & Line
FirstTime = False
Loop

Close #lngIN
Close #lngOut
End Sub
 
Back
Top