Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Import text file

Posted on 2000-02-22
6
Medium Priority
?
243 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
Industry Leaders: 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 400 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

Technology Partners: 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!

Question has a verified solution.

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

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

610 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