Merge select CSV / txt files

[Tunesmith]

Hi,

Looking to create a .csv file merging macro that can merge files selected by an Excel user into one combined single-sheet Excel document.
In my use case whatever .csv files selected by the user will all have the same set of columns with one header row in each one.

In the past I've used a customized version of the "Merging a Range from Selected Workbooks" from this knowledge page: (https://msdn.microsoft.com/en-us/lib....aspx#Anchor_3
But it's had a few quirks:

• It doesn't account for a header row in first file selected.
• It applies some formatting to columns (specifically noticeable with columns containing dates/times e.g. "dd/mm/yyyy hh:mm:ss" whereby the resulting combined file has lost the date information for a "Custom" format).

I recently stumbled upon this macro which seems promising on this page (Merge list of csv, txt files) (http://analystcave.com/merge-csv-fil...s-in-a-folder/).
But it doesn't fit my use case completely, so I'm looking for help with updating it:

• It is hard-coded which are the input and output files. I'd like the input files to be selected via Application.GetOpenFilename instead and the output to simply be left Open at the end (leaving it to the user to save it).
• It checks for a header row in the first input file but still leaves blank rows in the output file (where the headers would have been for subsequent merged files). There should be no empty rows in between merged files in the output.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

It sounds like it should be an easy quick fix but I've been staring myself blind for a while now, any help is much appreciated.

[AlphaFrog]

Have you tried Ron's free RDBMerge add-in? I think it will do what you want.

RDBMerge, Excel Merge Add-in

[Tunesmith]

Hi, I have looked at his add-in in the past, the older script cited on the Microsoft knowledge page was written by RDB and uses as far as I understand much of the same code underneath.
Unfortunately I can't use external add-ins in this case in the event the routine needs to be called from another process.

[AlphaFrog]

Try this.

You still may have issues with custom date formats. CSV files don't have serial dates. They have text. When Excel reads a text-date from a text file, it attempts to convert it to a serial-date (a numeric value) which then you can apply any date format you like. If Excel doesn't convert the text-date to a serial-date, it may look like the original text from the CSV file, but Excel only sees it as text and not as a serial-date.

Please Login or Register  to view this content.

[Kenneth Hobson]

That routine does add an extra blank line. See if this change helps.

Please Login or Register  to view this content.

You will need a slight tweak for #4. I do like that dialog better than GetFileOpenFilename().

Please Login or Register  to view this content.

[Tunesmith]

Hi, I haven't had the chance to test out your suggestions yet. Will do shortly, but would it be possible to adjust it so the macro does not save an output "Merged.csv" file and instead make use of an unsaved temp workbook as the output that is left open to the user at the end of the sub?

[Kenneth Hobson]

An advantage to the method that you posted is that it works on closed files in the background so it is very fast.

I showed you how to open the created file in the last line of code. If you want to rename the CSV created file, then that is easily done in the macro. Once open, if you really wanted to copy that sheet to another open workbook, that is easily done too. The syntax for that is easily seen by a recorded macro.

[Tunesmith]

Hi, sorry let me clarify. What I'm looking for it not to have any "Merged.csv" output file saved at all, but to simply put the selected merged files' data in an opened Excel workbook as part of the sub (via Workbooks.Add(xlWBATWorksheet).Worksheets(1) as example) and present the merged data to the user at the end in that sheet. Does that make sense?