asked on

Import text file

I need some help with the following text file. I need to extract a data from column 2 to column 6 from a line input beginning with 30 in column 1 and add to the next line with column 1 beginning with 31(see example). this process will continue until the line input column 1
no longer 31. the process will continue until eof() ( column 1 starting with 31 are the details of column 1 starting with 30). much appreciated and thank you very much. I have written a code to extract all line input starting with 30, but unable to do the rest.
example ( Expected Result);

30,12710229481,,K & S HAGOE,991207,N,8658.39,8658.39,5.7100,4.8000,0.00,0.00,0.00,1.53,,0.00
12710229481,,K & S HAGOE,991207,31,,-12.50,BP GLENDALE593M,00100706
12710229481,,K & S HAGOE,991207,31,-400.00,ATM CARDIFF\CARD,00100707

(first 5 column shown above are come from line input 1 starting with numerical value of 30. extract (col 2 to 6 inclusive) and paste it to the next line while col 1 starting with numerical 30 - this process must be continued while line input col 1 staring with 31, as Line input starting with ID 31 are the transaction details of line input starting with ID 30)

30,12710229481,,K & S HAGOE,991207,N,8658.39,8658.39,5.7100,4.8000,0.00,0.00,0.00,1.53,,0.00
31,-12.50,BP GLENDALE593 M,00100706
31,-400.00,ATM CARDIFF\CARD,00100707
30,12710239041,,HOOILTON WD & DG,991207,N,-85419.10,0.00,0.0088,0.0000,0.00,0.00,142.17,0.00,,0.00
30,12710239042,,HOOILTON WD & DG,991207,N,-59705.24,0.00,0.0091,0.0000,0.00,0.00,23.71,0.00,,0.00
31,-8.00,LOAN ADMIN FEE,00100018
30,12710239043,,HOOILTON WD & DG,991207,N,-99891.77,0.00,0.0080,0.0000,0.00,0.00,33.67,0.00,,0.00
30,12710239081,,WD&DG HOOILTON,991207,N,3833.05,3833.05,0.0000,3.1000,0.00,0.00,0.00,0.84,,0.00
31,-20.00,WESTPACBELROSE S,00100214
31,-10.30,SWONNO PTY LTDAU,00100215
31,-32.52,MOBIL 10225INGLE,00100216
30,12710247000,,R & R BOOD,991207,N,-72502.12,0.00,0.3020,0.0000,0.00,0.00,40.52,0.00,,0.00
30,12710247081,,R & R BOOD,991207,N,-31128.93,8871.07,0.0000,0.0000,0.00,0.00,37.90,0.00,,40000.00
31,-100.00,ATM CHARLESTOWN\,00100818
30,12710247082,,R & R BOWD,991207,N,-7998.97,22001.03,0.0000,0.0000,0.00,0.00,9.82,0.00,,30000.00
30,12710257700,,R & L RITOHIE,991207,N,-31670.42,0.00,0.1572,0.0000,0.00,0.00,17.71,0.00,,0.00
30,12710257781,,R & L RITOHIE,991207,N,-7129.42,42870.58,0.0000,0.0000,0.00,0.00,8.75,0.00,,50000.00
30,12710257783,,RITOHIE: R & L,991207,N,-79895.32,29104.68,0.3800,0.0000,0.00,0.00,98.63,0.00,,109000.00
30,12710294800,,WOLTON: D & S,991207,N,-129272.50,0.00,0.0000,0.0000,0.00,0.00,72.25,0.00,,0.00

TigerZhao

cant's understand, give me example, work with first 3 line, what's the extract data you need?

prefix

Can't understand.

Lewy

Try this:

Dim File1 As Integer, A$, I As Integer, P As Integer
Dim extractedData() As String, S As Integer
S = -1
File1 = FreeFile
Open "C:\junk.txt" For Input As #File1
While Not (EOF(File1))
Line Input #File1, A$
A$ = Trim(A$)
If Mid$(A$, 1, 3) = "30," Then
S = S + 1
ReDim Preserve extractedData(S)
P = 4
For I = 2 To 6
P = InStr(P, A$, ",") + 1
Next I
extractedData(S) = Mid$(A$, 4, P)
ElseIf Mid$(A$, 1, 3) = "31," Then
extractedData(S) = extractedData(S) & Mid$(A$, 3)
End If
Wend
Close #File1

linoh

ASKER

Edited text of question.

ASKER CERTIFIED SOLUTION

Lewy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

linoh

ASKER

Excellent, Lewy you are genius. I have worked on this problems for the last 2 weeks and have written numerous lines of code. once again thankyou very much for your help and much appreciated.