SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 8 of 8

Thread: Merging Files

  1. #1
    Advanced Member
    Join Date
    Mar 2002
    Location
    Earth, USA, New York, Western
    Posts
    759
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Merging Files

    With two files open and writing to a third, what is the best way to pick data out of the two files to be placed in the third?
    <font class="small">Code:</font><hr /><pre>
    Open "" &amp; ProjectDIR &amp; "\Coll_Dep_Imp.txt" For Append As #7
    Open "" &amp; ProjectDIR &amp; "\CollDepImp.txt" For Append As #8
    Open "" &amp; ProjectDIR &amp; "\CompleteCollDepImp.txt" For Output As #9

    Write #9, "cust", "coll", "eff","tlo","tlc","tlr","hlo","htlc","hlr"

    </pre><hr />

    I now need to state the first two items from file #8 and then the next seven things from file #7, to be written to #9
    Then on the next row going into #9 if there is no data in #7 then use the last data from the previous row. Do this five times because #8 will only have five rows.

    Any Pointers.
    THANKS,
    BMD [img]/images/graemlins/confused.gif[/img]

  2. #2
    Senior Member
    Join Date
    Apr 2003
    Location
    Wisconsin, USA
    Posts
    5,338
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Merging Files

    A couple of questions.

    1. Why are you opening the first two files for append? If you want to read data from those files, you need to open them for read access. Append is a variation of output, not input.
    2. Where are your statements for reading the data in from the first two files?

  3. #3
    Advanced Member
    Join Date
    Mar 2002
    Location
    Earth, USA, New York, Western
    Posts
    759
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Merging Files

    Do you mean how I built the two files? If so one is built from, SQL calls to the main database the other is a text file generated from Excel.

    This is of course the SQL one, it builds two files one with 1 row in it and one with 5 rows.
    <font class="small">Code:</font><hr /><pre>
    iResults = DoQuery(sSql, aData(), sConn)
    Debug.Print iResults
    For C = 0 To UBound(aData, 2)
    For R = 0 To UBound(aData, 1)
    sOutput = sOutput &amp; aData(R, C)
    sOutput = Trim$(sOutput) &amp; ", "
    Next R
    'Debug.Print Trim$(sOutput); " " ' TESTING
    sOutput = ""
    Next C

    MyValue = Int((C * Rnd) + 1)
    Debug.Print "The Random Row Is " &amp; (MyValue)
    Debug.Print "The Customer Is " &amp; (aData(0, MyValue)) &amp; " And The Collateral ID Is " &amp; (aData(1, MyValue)); ""
    Write #3, Trim$(aData(0, MyValue)), Trim$(aData(1, MyValue))
    'Debug.Print "The Collateral ID Is " &amp; (aData(1, MyValue))
    'Write #3, Trim$(aData(1, MyValue))

    Close #3

    Dim i As Integer
    Dim j As Integer
    Dim Unique As Boolean
    Dim MyValueArray(1 To 5) As Integer
    For i = 1 To 5 ' Repeat x5 ...
    Randomize ' Initialize the random-number generator
    Do
    MyValue = Int((C * Rnd) + 1)
    MyValueArray(i) = MyValue
    Unique = True
    For j = 1 To i - 1
    If MyValue = MyValueArray(j) Then
    Unique = False
    End If
    Next j
    Loop Until Unique
    Write #4, Trim$(aData(0, MyValue)), Trim$(aData(1, MyValue))
    Next i
    ' 11/30/2006 END
    </pre><hr />

    After I have these files in the new function I need to re-open the file with five rows and create a file by adding data from another file.

    One file looks like:
    "Post Now","","","12/4/2006","0","VAULT","5","2","VAULT","5"
    "Post Now","","","12/4/2006","","","5","2","VAULT",""
    "Post Now","","","12/4/2006","0","VAULT","5","2","VAULT","5"
    "Post Now","","","","","","","","",""
    "Post Now","","","","","","","","",""

    The other looks like:
    "C5","10421"
    "GR","308345"
    "GR456","200635"
    "F10","2097"
    "TRS","2001"

    I need something like:
    "C5","10421",12/4/2006","0","VAULT","5","2","VAULT","5"
    "GR","308345",12/4/2006","0","VAULT","5","2","VAULT","5"
    "GR456","200635",12/4/2006","0","VAULT","5","2","VAULT","5"
    "F10","2097",12/4/2006","0","VAULT","5","2","VAULT","5"
    "TRS","2001",12/4/2006","0","VAULT","5","2","VAULT","5"

    If there was no value in the cell use the value from the previous row.

    Did I completely mess this up?

    Thanks,
    BMD

    UPDATE
    There will always be date in the cell. I do not need to check and use the row above it....

  4. #4
    Senior Member
    Join Date
    Apr 2003
    Location
    Wisconsin, USA
    Posts
    5,338
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Merging Files

    Why not keep it simple?

    You have data from two files that you want to combine into a third file. You need to open the first two files for read and the third file for write. You then read the values in from the first two files and write to the third.

    So:
    Open File 1 for read
    Open File 2 for read
    Open File 3 for write
    Do
    read values(1) from file 1
    read values(2) from file 2
    write values(1) and values(2) to file 3
    Loop
    Close file 3
    Close file 2
    Close file 1

    The code in your initial post had you opening file 1 and 2 for append, which is a write access, not a read access. I also didn't see you reading the values from file 1 and 2.

    From your original post (my changes in bold)

    Open "" &amp; ProjectDIR &amp; "\Coll_Dep_Imp.txt" For Input As #7 'changed mode from append to input
    Open "" &amp; ProjectDIR &amp; "\CollDepImp.txt" For Input As #8 'changed mode from append to input
    Open "" &amp; ProjectDIR &amp; "\CompleteCollDepImp.txt" For Output As #9

    Input #7,cust, coll, eff
    Input #8,eff, tlo, tlc, ltr, hlo, htlc, hlr


    Write #9, cust, coll, eff,tlo,tlc,tlr,hlo,htlc,hlr 'quotes deleted around variables

  5. #5
    Advanced Member
    Join Date
    Mar 2002
    Location
    Earth, USA, New York, Western
    Posts
    759
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Merging Files

    Why do you do everything the easy way? HAHAHA.... THANKS

    The issue I have is the " in the file
    <font class="small">Code:</font><hr /><pre>
    "cust, coll, eff, tlo, tlc, tlr, hlo, htlc, hlr"
    "C10","m0","12/4/2006","0 - New","VAULT","5 - None","2 - New Location as Home","VAULT","5 - None"
    "C5","26482","12/4/2006","0 - New","VAULT","5 - None","2 - New Location as Home","VAULT","5 - None"
    "G456","2037","12/4/2006","0 - New","VAULT","5 - None","2 - New Location as Home","VAULT","5 - None"
    "C0","14","12/4/2006","0 - New","VAULT","5 - None","2 - New Location as Home","VAULT","5 - None"
    "GR56","2454","12/4/2006","0 - New","VAULT","5 - None","2 - New Location as Home","VAULT","5 - None"
    </pre><hr />

    How can I generate the file without the " just value,value,value,value.....

    Thanks,
    Again and for all your help,
    BMD

  6. #6
    Senior Member
    Join Date
    Apr 2003
    Location
    Wisconsin, USA
    Posts
    5,338
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Merging Files

    That would probably be string manipulation in the variables. But.... If you are dealing with CSV files, any values with spaces in them will get the quotes.

  7. #7
    Advanced Member
    Join Date
    Mar 2002
    Location
    Earth, USA, New York, Western
    Posts
    759
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Merging Files

    I used the Print command instead of the Write

    Print #9, Trim$(cust); ","; Trim$(coll); ","; Trim$(eff); ","; Trim$(tlo); ","; Trim$(tlc); ","; Trim$(ltr); ","; Trim$(hlo); ","; Trim$(htlc); ","; Trim$(hlr)

    Worked Nice....
    BMD

  8. #8
    Senior Member
    Join Date
    Apr 2003
    Location
    Wisconsin, USA
    Posts
    5,338
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Merging Files

    You could also concatenate the output before the print statement.

    Out$ = trim$(cust) &amp; "," &amp; trim$(coll) &amp; "," &amp; .......

    and then
    Print #9, out$

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.11%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:45 AM.

Copyright BetaSoft Inc.