Solved

Import text file

Posted on 2000-02-22
6
219 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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 …
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…

737 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