Solved

Import text file

Posted on 2000-02-22
6
214 Views
Last Modified: 2010-05-02
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              
0
Comment
Question by:linoh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Expert Comment

by:TigerZhao
ID: 2550125
cant's understand, give me example, work with first 3 line, what's the extract data you need?
0
 
LVL 1

Expert Comment

by:prefix
ID: 2550451
Can't understand.
0
 
LVL 3

Expert Comment

by:Lewy
ID: 2552018
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:linoh
ID: 2556322
Edited text of question.
0
 
LVL 3

Accepted Solution

by:
Lewy earned 100 total points
ID: 2558317
Should the following line from the example:
12710229481,,K & S HAGOE,991207,31,,-12.50,BP GLENDALE593M,00100706
read:
12710229481,,K & S HAGOE,991207,31,-12.50,BP GLENDALE593M,00100706
with only one comma between the 31 and –12.50?

To restate the requirements:
Lines with a value of 30 in column 1 are to be placed in the output string array as a single line in its entirety. Columns 2 – 5 of this line are saved in a temporary string. Include the comma after column 5.

Lines with a value of 31 in column 1 are to be placed in the output string array as a line with the temporary string from the preceding type 30 line appended with the type 31 line in its entirety.

Lines with any other value are ignored.

Lines are trimmed of spaces before processing to eliminate any problems. It may be determined that this is not necessary.
When checking for the value in column 1, include the comma character to prevent detecting 300, 3099 and so on.

Use the following code:
    Dim File1 As Integer, A$, I As Integer, P As Integer
    Dim extractedData() As String, HoldHeader 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$) 'incase there are spaces
        S = S + 1
        ReDim Preserve extractedData(S) 'add a new string to array
        If Mid$(A$, 1, 3) = "30," Then
            P = 4 'start pass the first comma
            For I = 2 To 5 'columns to extract
                P = InStr(P, A$, ",") + 1 'find the next field
            Next I
            HoldHeader = Mid$(A$, 4, P - 4) 'save for 31 type lines
            extractedData(S) = A$
        ElseIf Mid$(A$, 1, 3) = "31," Then
            extractedData(S) = HoldHeader & A$
        End If
        Debug.Print extractedData(S)
    Wend
    Close #File1


0
 

Author Comment

by:linoh
ID: 2563253
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.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question