Solved

Import text file

Posted on 2000-02-22
6
201 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

930 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now