Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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              
0
linoh
Asked:
linoh
1 Solution
 
TigerZhaoCommented:
cant's understand, give me example, work with first 3 line, what's the extract data you need?
0
 
prefixCommented:
Can't understand.
0
 
LewyCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
linohAuthor Commented:
Edited text of question.
0
 
LewyCommented:
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
 
linohAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now