VBscript: compile csv files into one single file

Hello experts,

I am looking for a vbscript procedure to cover the following requirements:
-Compile multiple csv files located in C:\Input folder
-Generate a single Compiled file into C:\Input folder with a name to report in the script and date stamp. Example YYYYMMDD_Reported_Name.csv
-Move the various compiled files into C:\Archive.
First row (header generated in Compiled file should be taken into account by reference reported file in the script

Variables should be the following:

-strInput= Folder in which are located files to compiled and compiled files.
-strArchive=Folder which should receive compiled files
-strCompileFileName=Reference compiled file name
-strHeaderFilename= Reference file which should be read in order to generate headers

-Every file to compiled has the same number of columns.
-Compilation process start as of line 2 in order to avoid having headers in compiled file. If possible have a initHeader variable if we want to reuse script;

-Previous verification actions before launching compile process are the following

If reported folders don’t exist, the procedure should create the folders.



Log actions:
-Generate a log file into C:\Input folder
-Report if folders have been created or already exist.
I attached files to combine for testing

If you have questions or need clarifications please contact me.

Thank you very much for your help.
export_test.csv
export_test_2.csv
LVL 1
LD16Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Bill PrewIT / Software Engineering ConsultantCommented:
A few questions:

  1. When you say compile you mean append, right?  No intelligence, other than skipping line 1, just append each individual file to the result file?
  2. Can you provide an output sample from the test input files you posted, just to eliminate any possible confusion?  You want all the lines that have no data, just semi-colons, correct?
  3. Should only *.CSV files be processed from input folder, or should we just take all files in that folder?
  4. Order of files concatenated together does not matter?
  5. "If possible have a initHeader variable if we want to reuse script" - not sure I know what you meant here, I am imagining this is a switch that indicates if the first line is skipped or not?  Does it have any other impact?
  6. What should the name be for the log file?


»bp
LD16Author Commented:
Thank you for your comment Bill
1-Yes, append all lines coming from multiple files into a single file
2-Yes, semicolon is the reference delimiter. I don't have access to the file I will send you tomorrow. If there is lines without not data they shouldn't be append
3-Input folder will have just csv files.
4-If possible concatenation order be based on datemofied o of each file so the oldest should be append at the begining and the newest at the end.
5-InitHeader variable is the line number from which start the append process. Example InitHeader=3. Append process for the various files should start at line 3. So line 1 and 2 should be omitted.
6-LogName=Name of the script.log.

If you have questions, please contact me.

Thank you again for your help.
Bill PrewIT / Software Engineering ConsultantCommented:
Okay, here's a decent starting point for you, seems to work as desired in a test here.  Save as a VBS file and adjust the constants near the top.

It doesn't sort the files merged by date, just takes them in the order Widows gives them to the program.  That would take a bit more effort, perhaps another question if that's really a requirement.

' Define folders and files to work with
Const cInputFolder = "B:\EE\EE29104435\Input"
Const cArchiveFolder = "B:\EE\EE29104435\Archive"
Const cMergedFile = "B:\EE\EE29104435\Input\[[STAMP]]_Reported_Name.csv"
Const cHeaderFile = "Header.csv"
Const cLogFile = "B:\EE\EE29104435\Input\[[STAMP]]_[[SCRIPTNAME]].log"
Const cHeaderLines = 1
Const cDelim = ";"

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TristateTrue = -1
Const TristateFalse = 0
Const TristateUseDefault = -2

'Create the file system object for creating folders:
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Get current script name and path
strScriptName = Replace(WScript.ScriptName, ".vbs", "", 1, -1, vbTextCompare)
strScriptDir = objFSO.GetAbsolutePathName(".")

' Get date in YYYYMMDD format
strStamp = Year(Now) & Right("0" & Month(Now), 2) & Right("0" & Day(Now), 2)

' Resolve full paths to all files involved (substitute any metadata tags)
strInputFolder = objFSO.GetAbsolutePathname(cInputFolder)
strArchiveFolder = objFSO.GetAbsolutePathname(cArchiveFolder)
strMergedFile = objFSO.GetAbsolutePathname(Replace(cMergedFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strHeaderFile = objFSO.GetAbsolutePathname(cHeaderFile)
strLogFile = objFSO.GetAbsolutePathname(Replace(cLogFile, "[[STAMP]]", strStamp, 1, -1, vbTextCompare))
strLogFile = objFSO.GetAbsolutePathname(Replace(strLogFile, "[[SCRIPTNAME]]", strScriptName, 1, -1, vbTextCompare))

' Create input folder if needed, exit if can't be created
If objFSO.FolderExists(strInputFolder) Then
    LogIt strLogFile, "Using existing input folder: """ & strInputFolder & """", True, True
Else
    MakeDir strInputFolder
    If Not objFSO.FolderExists(strInputFolder) Then
        LogIt "", "Could not create input folder: """ & strInputFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created input folder: """ & strInputFolder & """", True, True
End If

' Create output folder if needed, exit if can't be created
If objFSO.FolderExists(strArchiveFolder) Then
    LogIt strLogFile, "Using existing archive folder: """ & strArchiveFolder & """", True, True
Else
    MakeDir strArchiveFolder
    If Not objFSO.FolderExists(strArchiveFolder) Then
        LogIt "", "Could not create archive folder: """ & strArchiveFolder & """", False, True
        Wscript.Quit
    End If
    LogIt strLogFile, "Created archive folder: """ & strArchiveFolder & """", True, True
End If

' Exit if header file not found
If Not objFSO.FileExists(strHeaderFile) Then
    LogIt strLogFile, "Could not locate header file: """ & strHeaderFile & """", True, True
    Wscript.Quit
End If

' Copy header file to archive file
objFSO.CopyFile strHeaderFile, strMergedFile, True
LogIt strLogFile, "Using header file: """ & strHeaderFile & """", True, True

' Open output archive file to adde data after header line(s)
Set objMergedFile = objFSO.OpenTextFile(strMergedFile, ForAppending, True)

' Process all files in input folder
Set objFolder = objFSO.GetFolder(strInputFolder)
For Each objFile In objFolder.Files

    ' Make sure it's a CSV file
    If LCase(objFSO.GetExtensionName(objFile.Path)) = "csv" Then

        ' Make sure it's not the new merged file we are building
        If LCase(objFile.Path) <> LCase(strMergedFile) Then

            LogIt strLogFile, "Merging file: """ & objFile.Path & """", True, True

            ' Open it for reading
            With objFSO.OpenTextFile(objFile, ForReading, False, TriStateUseDefault)

                ' Read each line
                intLine = 0
                Do Until .AtEndOfStream
                    strData = .ReadLine

                    ' Skip any desired "header" lines
                    intLine = intLine + 1
                    If intLine > cHeaderLines Then

                        ' Skip any "empty" lines
                        If Replace(strData,cDelim, "") <> "" Then

                            ' Write this line to merged output file
                            objMergedFile.WriteLine strData

                        End If

                    End If

                Loop

                ' Done with this file, close it
                .Close

            End With

            ' Move this file to archive location
            LogIt strLogFile, "Archiving file: """ & objFile.Path & """", True, True
            objFile.Move strArchiveFolder & "\"

        End If

    End If

Next

' Done
objMergedFile.Close
Wscript.Quit()


' Small function to write a line to log file and/or the console
Sub Logit (strFile, strText, blnFile, blnConsole)
    ' Write to log file if requested
    If blnFile Then
        With objFSO.OpenTextFile(strFile, ForAppending, True)
            .WriteLine FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
            .Close()
        End With
    End If

    ' Write to console if requested
    If blnConsole Then
        Wscript.Echo FormatDateTime(Now(), vbShortDate) & " " & FormatDateTime(Now(), vbShortTime) & " - " & strText
    End If
End Sub

Sub MakeDir(ByVal strPath)
    ' Small recursive function to create a folder (and all parent folders in path if needed)
    If Not objFSO.FolderExists(strPath) Then
        ' Make sure parent of this folder exists first
        MakeDir objFSO.GetParentFolderName(strPath)
        ' Now create this folder
        objFSO.CreateFolder strPath
    End If
End Sub

Open in new window


»bp

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
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

LD16Author Commented:
Hello Bill,
Tested  but I need some clarifications:

1-Header.csv contains the Header line that should be take as reference for the Append File?
If so it should be located in the same folder of the script or should I add the full path of file.
2-cHeaderLines is the the header line number as of it  the append process start. if cHeaderLines=3 append process will merge the various files as of line 3?

I am asking this as I tested but I just got it the Header lines and not the merged in the Report_File. I tested but I just get the Header and I re-test by reporting cHeaderLine=1 and remove manually line 1 and 2 but I just still getting the Header line reported in Header.csv.

Please find attached csv files used.

Thank you very much for help.
export_02_04_2018_12_10_00.csv
export_01_06_2018_23_17_54.csv
Header.csv
Bill PrewIT / Software Engineering ConsultantCommented:
1-Header.csv contains the Header line that should be take as reference for the Append File?
If so it should be located in the same folder of the script or should I add the full path of file.
Either should work, the VBS script will look in the current folder if no path specified, and of course a full path will work.
2-cHeaderLines is the the header line number as of it  the append process start. if cHeaderLines=3 append process will merge the various files as of line 3?
This indicates the number of header lines to skip in each file.  So 3 would indicate to skip the first 3 lines, and start copying at line 4.


»bp
LD16Author Commented:
Very clear Bill.
I tested and I have just a little problem. For the first appened file, append process start at the same line as header and should be after the header line. I noticed this after I perform a count between the appended ref file and files to append in terms of number of lines.

I attached my test. in the script, I just I modified the position of constant in order to get workingdir.

Thank you very much for your help.
29104435_compile-csv-files-into-one-.zip
Bill PrewIT / Software Engineering ConsultantCommented:
Your header.csv file is missing the <CR><LF> characters at the end, please add them.


»bp
LD16Author Commented:
You are right tested and it works.
Thank  you very much for your help.
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
VB Script

From novice to tech pro — start learning today.