How to concatenate all files from one folder into one ignoring headers #2

Hi Experts,

I have this question after resolving the following

https://www.experts-exchange.com/questions/29109077/How-to-concatenate-all-files-from-one-folder-into-one-ignoring-headers.html#a42623555

How can I modify the command to

1- Ignore all headers
2- include only files created in specific date range
3- include only files with specific names, like "*PatSched* and "*PatChanges*"

Thanks in advance.
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
One of my old colleagues on a different Forum wrote this several years ago. I haven't used it but Will's code was good. Test it on a copy of your database.

Sub Import_multiple_csv_files()
     'Modified from WillR - www.willr.info (December 2004)
     
    Const strPath As String = "C:\Addresspoint\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File  Array
    Dim intFile As Integer 'File Number
     
     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.csv")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
         MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list of files &  import to Access
     'creating a new table called MyTable
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acImportDelimi, , _
        "Test", strPath & strFileList(intFile)
         'Check out the TransferSpreadsheet options in the Access
         'Visual Basic Help file for a full description & list of
         'optional settings
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Open in new window

0
Bill PrewCommented:
1- Ignore all headers
How are "headers" identified, is it always just the first line of every file, or is there some other pattern?
2- include only files created in specific date range
Please give some examples of what this selection would be. DO you just want a from and a to date, or a certain number of days, etc?
3- include only files with specific names, like "*PatSched* and "*PatChanges*"
All files will be in the same base folder? And only files matching one or more normal file patterns will be selected?


»bp
0
ProfessorJimJamCommented:
we do not need to reinvent the wheel.  MVP fellow Ron De Bruin has built an excellent free Excel Add-in that can do what you have described.

rdbmerge.png
you can download the add-in for free from here RDBMerge, Excel Merge Add-in for Excel for Windows

You can use the parameters in the add-in to select which files and to ignore the header you set the range to start from A2.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

bfuchsAuthor Commented:
@ProfessorJimJam,
This is something I will run on win task scheduler on a daily bases, do they offer such an option?

@Bill,
How are "headers" identified, is it always just the first line of every file, or is there some other pattern?
Always the 1st line.
Please give some examples of what this selection would be. DO you just want a from and a to date, or a certain number of days, etc?
Would start with files created in the last 24 hours, with option to modify..
All files will be in the same base folder? And only files matching one or more normal file patterns will be selected?
Yes.
What is considered abnormal?

@Roy,
Will this (besides do the concatenation) also address those 3 issues?
Don't see option for date created for example?

@All,
Forgot to mention, I need them concatenated in order they were created.


Thanks,
Ben
0
Bill PrewCommented:
Okay, so the list looks like this now.

1- Ignore all headers
2- include only files created in specific date range
3- include only files with specific names, like "*PatSched* and "*PatChanges*"
4- concatenate files in date/time created order, oldest to newest

1, 3, and 4 can easily be done with small mods to the BAT script you had. And with the use of a small VBS helper routine it could handle 2 as well.  BAT is not good at all with date/time comparisons...

Is BAT acceptable?

Of course Powershell could do it too, but not sure your comfort level there...


»bp
0
bfuchsAuthor Commented:
Is BAT acceptable?
Yes.

Thanks,
Ben
0
Bill PrewCommented:
Okay, this should get you started. Save in the same folder, and adjust the BaseDir, then run the BAT script.

***** EE29116756.BAT *****
@echo off
setlocal EnableDelayedExpansion

rem Set path to folder where files to process exist
set BaseDir=B:\EE\EE29116756\Files

rem Set name for merged output file
set OutputFile=merged.txt

rem Get path to folder where this BAT script exists
set ScriptPath=%~dp0

rem Delete output file if it exists
if exist "%BaseDir%\%OutputFile%" del "%BaseDir%\%OutputFile%"

rem Process all matching files in base folder, sorting by create date/time
for /f "tokens=*" %%F in ('dir /b /a-d /od /tc "%BaseDir%\*PatSched*" "%BaseDir%\*PatChanges*"') do (

    rem For each file, call VBS script to calculate created age in hours from now
    set Fileage=
    for /f "tokens=*" %%A in ('cscript //nologo "%ScriptPath%\EE29116756.vbs" "%BaseDir%\%%~F"') do (
        set Fileage=%%A
    )

    rem If we got it's age, see if it meets criteria, if so append to output file skipping first line
    if defined FileAge (
        if !FileAge! LEQ 24 (
            more +1 "%BaseDir%\%%~F" >> "%BaseDir%\%OutputFile%"
        )
    )
)

Open in new window

***** EE29116756.VBS *****
' Create filesystem object
Set objFSO = WScript.CreateObject("Scripting.FileSystemObject")

' Make sure a file passed on commandline
If (WScript.Arguments.Count < 1) Then
    Wscript.StdErr.WriteLine "*ERROR* no filename specified."
    Wscript.Quit
Else
    strFile = objFSO.GetAbsolutePathname(WScript.Arguments(0))
End If

' If file exists, return it's age in hours
If Not objFSO.FileExists(strFile) Then
    Wscript.StdErr.WriteLine "*ERROR* file not exists on """ & strFile & """."
    Wscript.Quit
Else
    Set objFile = objFSO.GetFile(strFile)
    Wscript.StdOut.WriteLine DateDiff("h", objFile.DateCreated, Now)
End If

Open in new window


»bp
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
@Bill,
I saved as the following
@echo off
setlocal EnableDelayedExpansion

rem Set path to folder where files to process exist
set BaseDir=H:\FTP

rem Set name for merged output file
set OutputFile=merged.txt

rem Get path to folder where this BAT script exists
set ScriptPath=%~dp0

rem Delete output file if it exists
if exist "%BaseDir%\%OutputFile%" del "%BaseDir%\%OutputFile%"

rem Process all matching files in base folder, sorting by create date/time

for /f "tokens=*" %%F in ('dir /b /a-d /od /tc "%BaseDir%\*SchChanges*"') do (

    rem For each file, call VBS script to calculate created age in hours from now
    set Fileage=
    for /f "tokens=*" %%A in ('cscript //nologo "%ScriptPath%\EE29116756.vbs" "%BaseDir%\%%~F"') do (
        set Fileage=%%A
    )

    rem If we got it's age, see if it meets criteria, if so append to output file skipping first line
    if defined FileAge (
        if !FileAge! LEQ 24 (
            more +1 "%BaseDir%\%%~F" >> "%BaseDir%\%OutputFile%"
        )
    )
)

Open in new window

Saved both under same folder as those files exists (see attached), but nothing gets created.
or perhaps it does, but i don't know where to look for it...?

Thanks,
Ben
Untitled.png
0
Bill PrewCommented:
Well, for one thing, since you changed the name of the VBS script (which is fine), you need to reflect the new name on this line:

    for /f "tokens=*" %%A in ('cscript //nologo "%ScriptPath%\EE29116756.vbs" "%BaseDir%\%%~F"') do (

Open in new window


The output file will be in the same folder as the files being merged.


»bp
0
bfuchsAuthor Commented:
Thank you!
0
Bill PrewCommented:
Welcome, hope that is useful.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.