1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: combining two files into one

Discussion in 'Business Applications' started by JeffSchwartz, Feb 7, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    I am needing to repeatedly merge or append two documents into one document. The documents are currently csv files and can obviously be opened and worked with in either WORD or EXCEL. I am wondering what would be the easiest way to repeatedly combine two files into one? The files that I am working with have names such as:

    Z001_01A
    Z001_01
    Z002_01A
    Z002_01
    Z001_02A
    Z001_02
    Z002_02A
    Z002_02
    ..... Z002_31

    I would want to combine them as follows"

    Z001_01A with Z002_01A
    Z001_01 with Z002_02
    Z001_02A with Z002_02A
    Z001_02 with Z002_02


    Basically, I am wanting to merge report 01 (Z001) with report 02 (Z002) for each day of the month [_01 through _31] from both of two cash registers [one with the "A" and one without it.]


    I have very little experience writing macros. I am one of those newbies that is still recording macros. Is there a way that I could code a MACRO in either WORD or EXCEL that would combine the two files together?
     
  2. bkmgy

    bkmgy

    Joined:
    Feb 4, 2012
    Messages:
    142
  3. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    you can drop to a command prompt, navigate to your data folder and type in:
    copy Z001_01A.csv + Z002_01A.csv Z20012A.csv
    If your file names vave spaces, you will need quotes around the names, and the Z2012A.csv will be the name of the new file....
    Have more to combine? Use up arrow to recall the last command, use left and right arrow to move through the command and simply edit what you want changed.
     
  4. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56

    Thank you for your suggestions!

    I guess if I am going to step through each pairing of files like either of these to replies have suggested, I could just as well cut and paste the one file into the other.

    I am hoping that there is a way that a macro can be set up so that I don't have to go through and individually combine 60 sets of two files each month.
     
  5. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    some automation in this may be possible. There would have to be some definition in how a macro, or cmd file would know what files to combine. Are the names / naming conventions the same? This month you combine Z001_01A.csv + Z002_01A.csv to make Z20012A.csv. What will they be next month, the month after.?.
     
  6. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    Here is a vbscript I modified to match your information. This simply reads a file, writes, closes opens the next file and keeps writing to the same file. You could probably come up with a way to logically process all your files here. It will take a little work. We can talk about that later. Save this code as Read2Write1.vbs (or whatever.vbs).

    You can edit in note pad, change the UNC Paths In 'file definitions' to match your needs. If want to try VBscript this is a great starter project. This has not been tested.
    Code:
    '***********************************
    'Read 2 files write 1
    'draceplace
    '02/11/2013
    ''***********************************
    '*******File handling Constants*****
    Const ForReading = 1
    Const ForWriting = 2
    Const ForAppending = 8
    '******Counters********************
    cntRead1 = 0  
    cntRead2 = 0
    cntWrite = 0
    '*******File Definitions**********************
    InputFile1 = "H:\Ascripts\Z001_01A.csv"
    InputFile2 = "H:\Ascripts\Z002_01A.csv"
    
    outFile = "H:\Ascripts\Z001_01A with Z002_01A" 
    '**********************************************
    Msg1 = "Read 2 Files and Write1" & vbCrlf & _
    "Input1= " & InputFile1 & VbCrlf & " Input2= " & Inputfile2
    "------------------------------------------------------------------"
    '**Start of Program****************************
    selMistake = MsgBox(Msg1,VbOkCancel,"Cash Register Merge")
    If selMistake = vbOK then '---Big IF will cancel whole process-----
     
    '****************************************************************
    '********open input file*****************************
      Set objFSO = CreateObject("Scripting.FileSystemObject")
      Set objTextFile = objFSO.OpenTextFile(InputFile1, ForReading)
      Set objOutFile = objFSO.OpenTextFile(outFile, ForWriting, True)
    
      'skipping the header'
      'strLine = objtextFile.ReadLine
    '****************************************************************
    '********Loop til end of input csv file******************************
    Do While objTextFile.AtEndOfStream <> True
      strLine = objtextFile.ReadLine
       		cntRead1 = cntRead1 + 1
           objOutFile.WriteLine ReadLine
    		 cntWrite = cntWrite + 1
     
     Loop
     
     objTextFile.Close
     
     'Second input process
     Set objTextFile = objFSO.OpenTextFile(InputFile2, ForReading) 
    Do While objTextFile.AtEndOfStream <> True
      strLine = objtextFile.ReadLine
       		cntRead2 = cntRead2 + 1
      		 objOutFile.WriteLine ReadLine
    		 cntWrite = cntWrite + 1
     
     Loop
    
    
    '****End of Job Routine ***********************************************
    End If  'Goes with message box for Cancel
    msgEOJ = vbCrLf & "Number Read Input1: " & cntRead1 & _
    vbCrLf & "Number Read Input1: " & cntRead2 & _
    vbCrLf & "Records output: " & cntWrite
    
    selEOJ = MsgBox(msgEOJ,VbOk,"Cash Register Merge")
    
    '***********Close Files if open.
    If selMistake = vbOK then
     objTextFile.Close
     objOutFile.Close
     Set objFSO = Nothing    
     Set objTextFile = Nothing
     Set objOutFile = Nothing
    
     Else
    End If
    
     
  7. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    I tried the above code. It created the "output file" but the file is empty. Any ideas ??
     
  8. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    20_2_Many,

    The file names are the same from month to month. They are saved in different directories.
     
  9. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    Since the files are .csv, I was able to combine two files into one using WORD and recorded the following macro:

    -------------------------------------------
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    '
    Documents.Open FileName:="C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Z002_02A.CSV", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:="", _
    Encoding:=1252
    Selection.WholeStory
    Selection.Copy
    Documents.Open FileName:="C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Z001_02A.CSV", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto, XMLTransform:="", _
    Encoding:=1252
    Selection.MoveDown Unit:=wdScreen, Count:=9
    Selection.TypeParagraph
    Selection.PasteAndFormat (wdFormatOriginalFormatting)
    ActiveDocument.SaveAs2 FileName:="C:\Users\Kurts work c\Desktop\Jeff's stuff\1c\Sales02A.csv", FileFormat:=wdFormatText, _
    LockComments:=False, Password:="", AddToRecentFiles:=True, WritePassword _
    :="", ReadOnlyRecommended:=False, EmbedTrueTypeFonts:=False, _
    SaveNativePictureFormat:=False, SaveFormsData:=False, SaveAsAOCELetter:= _
    False, Encoding:=1252, InsertLineBreaks:=False, AllowSubstitutions:=False _
    , LineEnding:=wdCRLF, CompatibilityMode:=0
    End Sub

    -----------------------------------------------

    This macro seems to work, even when I change the file names to use other files. I'm sure there is some
    correcting that could be done, since the macro was recorded. I'm still learning how to do that.

    I imagine I could copy this process in the macro to repeat it, changing the file names for each of the 54 desired pairings. This would get to be a long macro. I don't know enough to know how I could somehow loop the process through the pairings.

    Another challenge I would have has to do with the names of the files from month to month. The two digits after the underscore refer to the date of that report. There are two reports (one with the "A" and the end and one without it) created for each of the days except for Sunday. Is there a way that I could create the macro to include all possible dates and have it skip to the next repetition on an error for "no such file?"

    I suppose I could also create "dummy" reports for the Sundays and just ignore the Sales reports that it creates for those days.

    Any ideas on the best way to proceed?
     
  10. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    ...In my script above 3 problems (I can repost but fixes pretty simple)
    This line needs .csv:
    outFile = "H:\Ascripts\Z001_01A with Z002_01A.csv"

    Line22 should be '------ instead of "-------- (single tick)

    And the two lines--
    objOutFile.WriteLine readLine
    need to be like this.
    objOutFile.WriteLine strLine

    I'm thinking you can add an input file with input/output files names in it set logic in the script to merge the two files and name with output name.

    Psuedo Code:

    Do WhileFileNameInput is not EOF
    Read FileNameInput file line1
    GetNames infile1, infile2, outfile
    Open outfile
    Open/Read/write/Close infile1
    Open/Read/write/Close infile2
    Close outfile
    Loop for next set of names
     
  11. 20_2_Many

    20_2_Many

    Joined:
    Jun 29, 2012
    Messages:
    518
    Jeff, Dan is getting you closer to solution. Still, as indicated earlier, to automate this we (he) will need a list of each - the first file name, 2nd file name, and combined file name. These names will have to match-up for which goes with which. The full path of where those files are, and where the output combined file should go we also need.
     
  12. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    If the second filename is a miss type...I see the pattern? If the paths are consistant this might can be done without the FileName input.

     
  13. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I don't know the overall picture here but perhaps copying all the files into one csv and taging\adding the register name on each transaction would get you a better transaction file to work with? You could pull that file into Access and report as desired.

    If these were the only files in a directory we would NOT care what there names were, just spin through the directory and copy each line and add the file name to the end?
     
  14. JeffSchwartz

    JeffSchwartz Thread Starter

    Joined:
    Jan 31, 2013
    Messages:
    56
    Our cash registers name the files that I am working with. For some reason they break the sales data into two seperate reports, which I am trying to combine together to ultimately use in a pivot table to make various reports of the data.

    The first of the two reports are titled Z001*. The second one are titled Z002*.
    The two digits after the underscore is the day of the month the reports were run.
    An "A" is added to the end of the name of the files from the second of our two registers to save data onto the card for that day.

    So..I want to combine:

    [Z001_01.csv] with [Z002_01.csv] into [Sales01.csv] {....the data from register one on January 1st
    [Z001_01A.csv] with [Z002_01A.csv] into [Sales01A.csv] {....the data from register two on January 1st
    [Z001_02.csv] with [Z002_02.csv] into [Sales02.csv] {....the data from register one on January 2nd
    [Z001_02A.csv] with [Z002_02A.csv] into [Sales02A.csv] {....the data from register two on January 2nd
    [Z001_03.csv] with [Z002_03.csv] into [Sales03.csv] ................
    [Z001_03A.csv] with [Z002_03A.csv] into [Sales03A.csv]
    [Z001_04.csv] with [Z002_04.csv] into [Sales04.csv]
    [Z001_04A.csv] with [Z002_04A.csv] into [Sales04A.csv]
    [Z001_05.csv] with [Z002_05.csv] into [Sales05.csv]
    [Z001_05A.csv] with [Z002_05A.csv] into [Sales05A.csv]
    [Z001_06.csv] with [Z002_06.csv] into [Sales06.csv]
    [Z001_06A.csv] with [Z002_06A.csv] into [Sales06A.csv]
    [Z001_07.csv] with [Z002_07.csv] into [Sales07.csv]
    [Z001_07A.csv] with [Z002_07A.csv] into [Sales07A.csv]
    [Z001_08.csv] with [Z002_08.csv] into [Sales08.csv]
    [Z001_08A.csv] with [Z002_08A.csv] into [Sales08A.csv]
    [Z001_09.csv] with [Z002_09.csv] into [Sales09.csv]
    [Z001_09A.csv] with [Z002_09A.csv] into [Sales09A.csv]
    [Z001_10.csv] with [Z002_10.csv] into [Sales10.csv]
    [Z001_10A.csv] with [Z002_10A.csv] into [Sales10A.csv]
    [Z001_11.csv] with [Z002_11.csv] into [Sales11.csv]
    [Z001_11A.csv] with [Z002_11A.csv] into [Sales11A.csv]
    [Z001_12.csv] with [Z002_12.csv] into [Sales12.csv]
    [Z001_12A.csv] with [Z002_12A.csv] into [Sales12A.csv]
    [Z001_13.csv] with [Z002_13.csv] into [Sales13.csv]
    [Z001_13A.csv] with [Z002_13A.csv] into [Sales13A.csv]
    [Z001_14.csv] with [Z002_14.csv] into [Sales14.csv]
    [Z001_14A.csv] with [Z002_14A.csv] into [Sales14A.csv]
    [Z001_15.csv] with [Z002_15.csv] into [Sales15.csv]
    [Z001_15A.csv] with [Z002_15A.csv] into [Sales15A.csv]
    [Z001_16.csv] with [Z002_16.csv] into [Sales16.csv]
    [Z001_16A.csv] with [Z002_16A.csv] into [Sales16A.csv]
    [Z001_17.csv] with [Z002_17.csv] into [Sales17.csv]
    [Z001_17A.csv] with [Z002_17A.csv] into [Sales17A.csv]
    [Z001_18.csv] with [Z002_18.csv] into [Sales18.csv]
    [Z001_18A.csv] with [Z002_18A.csv] into [Sales18A.csv]
    [Z001_19.csv] with [Z002_19.csv] into [Sales19.csv]
    [Z001_19A.csv] with [Z002_19A.csv] into [Sales19A.csv]
    [Z001_20.csv] with [Z002_20.csv] into [Sales20.csv]
    [Z001_20A.csv] with [Z002_20A.csv] into [Sales20A.csv]
    [Z001_21.csv] with [Z002_21.csv] into [Sales21.csv]
    [Z001_21A.csv] with [Z002_21A.csv] into [Sales21A.csv]
    [Z001_22.csv] with [Z002_22.csv] into [Sales22.csv]
    [Z001_22A.csv] with [Z002_22A.csv] into [Sales22A.csv]
    [Z001_23.csv] with [Z002_23.csv] into [Sales23.csv]
    [Z001_23A.csv] with [Z002_23A.csv] into [Sales23A.csv]
    [Z001_24.csv] with [Z002_24.csv] into [Sales24.csv]
    [Z001_24A.csv] with [Z002_24A.csv] into [Sales24A.csv]
    [Z001_25.csv] with [Z002_25.csv] into [Sales25.csv]
    [Z001_25A.csv] with [Z002_25A.csv] into [Sales25A.csv]
    [Z001_26.csv] with [Z002_26.csv] into [Sales26.csv]
    [Z001_26A.csv] with [Z002_26A.csv] into [Sales26A.csv]
    [Z001_27.csv] with [Z002_27.csv] into [Sales27.csv]
    [Z001_27A.csv] with [Z002_27A.csv] into [Sales27A.csv]
    [Z001_28.csv] with [Z002_28.csv] into [Sales28.csv]
    [Z001_28A.csv] with [Z002_28A.csv] into [Sales28A.csv]
    [Z001_29.csv] with [Z002_29.csv] into [Sales29.csv]
    [Z001_29A.csv] with [Z002_29A.csv] into [Sales29A.csv]
    [Z001_30.csv] with [Z002_30.csv] into [Sales30.csv]
    [Z001_30A.csv] with [Z002_30A.csv] into [Sales30A.csv]
    [Z001_31.csv] with [Z002_31.csv] into [Sales31.csv]
    [Z001_31A.csv] with [Z002_31A.csv] into [Sales31A.csv]


    Each of the Z001 reports contains data similar to the following:


    MODEL#,TE-2200
    MACHINE#,MC#01
    REPORT #,FIX
    FILE #,FILE001
    MODE #,Z
    Z COUNTER #,1430
    DATE #,1/2/2013
    TIME #,8:19 PM
    GROSS #,314
    GROSS $,1044.51
    NET #,87
    NET $,951.52
    CAID #,0
    CAID $,455.4
    CATL #,0
    CATL $,0
    CA- #,0
    CA- $,0
    CA+ #,0
    CA+ $,0
    CHID #,0
    CHID $,0
    CHTL #,0
    CHTL $,0
    CH- #,0
    CH- $,0
    CH+ #,0
    CH+ $,0
    CKID #,0
    CKID $,45.15
    CKTL #,0
    CKTL $,0
    CK- #,0
    CK- $,0
    CK+ #,0
    CK+ $,0
    CRID(1) #,0
    CRID(1) $,677.57
    CRID(2) #,0
    CRID(2) $,0
    CRID(3) #,0
    CRID(3) $,0
    CRID(4) #,0
    CRID(4) $,0
    CRTL #,0
    CRTL $,0
    CR- #,0
    CR- $,0
    CR+ #,0
    CR+ $,0
    CAID2 #,0
    CAID2 $,0
    CATL2 #,0
    CATL2 $,0
    CA- 2 #,0
    CA- 2 $,0
    CA+ 2 #,0
    CA+ 2 $,0
    CHID2 #,0
    CHID2 $,0
    CHTL2 #,0
    CHTL2 $,0
    CH- 2 #,0
    CH- 2 $,0
    CH+ 2 #,0
    CH+ 2 $,0
    CKID2 #,0
    CKID2 $,0
    CKTL2 #,0
    CKTL2 $,0
    CK- 2 #,0
    CK- 2 $,0
    CK+ 2 #,0
    CK+ 2 $,0
    CRID2(1) #,0
    CRID2(1) $,0
    CRID2(2) #,0
    CRID2(2) $,0
    CRID2(3) #,0
    CRID2(3) $,0
    CRID2(4) #,0
    CRID2(4) $,0
    CRTL2 #,0
    CRTL2 $,0
    CR- 2 #,0
    CR- 2 $,0
    CR+ 2 #,0
    CR+ 2 $,0
    FSID #,0
    FSID $,0
    FSCACG #,0
    FSCACG $,0
    EBTTL #,0
    EBTTL $,0
    EBTCACG #,0
    EBTCACG $,0
    RF #,0
    RF $,0
    CUST #,87
    CUST $,0
    AVRG #,0
    AVRG $,0
    FEE #,0
    FEE $,0
    + #,0
    + $,0
    NB #,0
    NB $,0
    C-1 #,0
    C-1 $,0
    C-2 #,0
    C-2 $,0
    CECA1 #,0
    CECA1 $,0
    CECK1 #,0
    CECK1 $,0
    CECA2 #,0
    CECA2 $,0
    CECK2 #,0
    CECK2 $,0
    CECA3 #,0
    CECA3 $,0
    CECK3 #,0
    CECK3 $,0
    CECA4 #,0
    CECA4 $,0
    CECK4 #,0
    CECK4 $,0
    DC #,0
    DC $,139.46
    COUPON #,0
    COUPON $,0
    REF #,0
    REF $,0
    CLEAR #,0
    CLEAR $,0
    ROUND #,0
    ROUND $,0
    ROUND #,0
    ROUND $,0
    CANCEL #,3
    CANCEL $,9.47
    DECLA #,0
    DECLA $,0
    TA1 #,0
    TA1 $,120.73
    TX1 #,0
    TX1 $,0
    EX1 #,0
    EX1 $,0
    TA2 #,0
    TA2 $,652.64
    TX2 #,0
    TX2 $,46.47
    EX2 #,0
    EX2 $,0
    TA3 #,0
    TA3 $,0
    TX3 #,0
    TX3 $,0
    EX3 #,0
    EX3 $,0
    TA4 #,0
    TA4 $,0
    TX4 #,0
    TX4 $,0
    EX4 #,0
    EX4 $,0
    TA5 #,0
    TA5 $,0
    TX5 #,0
    TX5 $,0
    EX5 #,0
    EX5 $,0
    TA6 #,0
    TA6 $,0
    TX6 #,0
    TX6 $,0
    EX6 #,0
    EX6 $,0
    TA7 #,0
    TA7 $,0
    TX7 #,0
    TX7 $,0
    EX7 #,0
    EX7 $,0
    TA8 #,0
    TA8 $,0
    TX8 #,0
    TX8 $,0
    EX8 #,0
    EX8 $,0
    TA9 #,0
    TA9 $,0
    TX9 #,0
    TX9 $,0
    EX9 #,0
    EX9 $,0
    TA10 #,0
    TA10 $,0
    TX10 #,0
    TX10 $,0
    EX10 #,0
    EX10 $,0
    NON TAX #,0
    NON TAX $,146.47

    Each of the Z001 reports contains data similar to the following:


    CASH #,56
    CASH $,228.8
    TRES.CARD #,6
    TRES.CARD $,45.15
    CREDIT2 #,0
    CREDIT2 $,0
    HELP #,0
    HELP $,0
    SUBTOTAL #,0
    SUBTOTAL $,0
    CHARGE #,0
    CHARGE $,0
    CREDIT #,26
    CREDIT $,677.57
    PD #,0
    PD $,0
    LOAN #,1
    LOAN $,226.6
    P.UP #,0
    P.UP $,0
    T/S2 #,0
    T/S2 $,0
    T/S1 #,0
    T/S1 $,0
    . #,0
    . $,0
    CLK# #,0
    CLK# $,0
    0 #,0
    0 $,0
    QT #,0
    QT $,0
    CORR #,3
    CORR $,9.47
    PLU# #,0
    PLU# $,0
    PRC #,0
    PRC $,0
    #/NS #,5
    #/NS $,0
    CE #,0
    CE $,0
    RF #,0
    RF $,0
    - #,0
    - $,0
    %- #,26
    %- $,75.98
    %- #,31
    %- $,63.48
    RCT ON/OFF #,0
    RCT ON/OFF $,0

    Eventually I will turn the data horizontal so that the data from each register for each day forms a row. I assume it is easier to combine the two reports row than as columns.

    I don't have any experience with Access. I guess I have assumed that I would work with Excel to create the reports. I dunno????

    Is is significantly faster and more efficient to combine the files in Excel that in Word?
     
  15. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,583
    I'm going to fold for the day. Not sure of my availabilty for the next coulple of days. I have a vendor coming in I have to work with while they are here. I'll check in when I can.

    I'm thinking you need to flatten the files as we combine them. Were you able to get the script to combine the files?
    Also Seems the data doesn't match up? 206 rows in z1 and 56 rows in z2? Not sure how that match back up.
    Maybe and example of what the end result might look like would help us understand.

    Forget about Word. Its not for numbers. Access is a reporting beast. Excel is good for displaying and tweeking results. Some people can make Excel sing.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Similar Threads - Solved combining files
  1. BratDawg
    Replies:
    1
    Views:
    253
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1088632

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice