Automatically Combine a Folder of CSV Files.

[DaleJ92]

Afternoon All,

I have just signed up today so any help is much appreciated and i will help in return where possible.

The issue i am having is i have an old machine that im using to gauge parts, every reading it outputs as a .CSV file.

So after 20 parts i have 20 separate .CVS files with the same format just one line of Data.

Is it possible to automatically merge these files into one Excel file?
I have seen a method but it seems long winded and i was just wondering if there was a way of doing it automatically.

I was hoping to create a live document if possible so every time a new part is gauged it automatically gets added to the file.

Please see picture below, showing an example of the filenames.

Any help with this is greatly appreciated.

Many thanks in advance.

Dale

snipex.PNG

[CK76]

There are number of ways to do it.

But you'll need to provide sample csv and how it should be combined.

To upload file, use "Go Advanced" button and follow "Manage Attachments" hyperlink. It will launch new tab/window for managing uploads.

Several methods.
1. Get PowerQuery add-in from MS. And use binary combine.
2. Use VBA. Using Dir function to iterate through files, open and copy range.
3. Use VBA. Using ADODB and SQL query to combine CSV. This is bit complex if you are not used to it, and will require schema.ini file to be created.
etc.

[DaleJ92]

Many thanks for the quick reply, i have attached an example of the .CSV file so you can see the format.

Basically i just want the individual records to be into one table so i can do Live SPC, same layout just the records on the following rows.

From your list of options would any allow automatic merging? so say when a macro button is pressed it adds any additional .CSV files that have been added.

obviously when a new part is gauged i can easily update a graph without having to manually add the new data every time.

I hope you can understand what i mean im not best with wording haha.

Many thanks

[CK76]

Need bit more info. How should the combined/merged data look like?

Just flat table with exactly same headers, and any additional data (file) added below?

[DaleJ92]

Yeah the same headers as the attached .CSV file with the additional data on the rows descending.

Many thanks for your help.

[CK76]

If using PowerQuery method follow instruction in the link.
https://www.myonlinetraininghub.com/...-from-a-folder

For PQ Add-in download follow link below.
https://www.microsoft.com/en-ca/down....aspx?id=39379

If using VBA method. See below for sample code using variation of method 2 in post #2.
This assumes that you have all csv to be imported in same folder. Change fPath as needed.
See sample attached.

Sub Demo()
Dim fPath As String: fPath = "C:\Test\CSV" & "\" 'Change path as needed
Dim fExt As String: fExt = "*.CSV"
Dim mFile As String, fColl As New Collection
Dim intFF As Integer: intFF = FreeFile()
Dim i As Integer, j As Integer
mFile = Dir(fPath & fExt)
Do While Len(mFile) > 0
    fColl.Add fPath & mFile
    mFile = Dir()
Loop
j = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To fColl.Count
    Open fColl(i) For Input As #intFF
    Do Until EOF(intFF)
        Line Input #intFF, ReadData
        If InStr(ReadData, "Date and time") Then
        Else
            j = j + 1
            Cells(j, 1).Resize(, 53) = Split(ReadData, ",")
        End If
    Loop
    Close #intFF
Next

End Sub

Note: Depending on your process, this requires additional coding. My recommendation is to move files to another folder, once imported.

[DaleJ92]

Morning CK76,

Many thanks, your VBA option works perfectly.

I added a little so on open it deletes the data and merges capturing any new data.

Just a small one is there a way to slightly adjust the VBA a little to get it rename a few columns headers?

i didn't notice at first but some of the header the machine generates are confusing.

MANY MANY Thanks for your help with this though

Dale J

Private Sub Workbook_Open()

Range("A2:AQ99999").ClearContents

Dim fPath As String: fPath = "I:\MARPOSSGAUGEDATA" & "\" 'Change path as needed
Dim fExt As String: fExt = "*.CSV"
Dim mFile As String, fColl As New Collection
Dim intFF As Integer: intFF = FreeFile()
Dim i As Integer, j As Integer
mFile = Dir(fPath & fExt)
Do While Len(mFile) > 0
    fColl.Add fPath & mFile
    mFile = Dir()
Loop
j = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To fColl.Count
    Open fColl(i) For Input As #intFF
    Do Until EOF(intFF)
        Line Input #intFF, ReadData
        If InStr(ReadData, "Date and time") Then
        Else
            j = j + 1
            Cells(j, 1).Resize(, 53) = Split(ReadData, ",")
        End If
    Loop
    Close #intFF
Next

End Sub

[alansidman]

Dale

Code Tags Added
Your post does not comply with Rule 2 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found at http://www.excelforum.com/forum-rule...rum-rules.html



(I have added them for you today. Please take a few minutes to read all Forum Rules and comply in the future.)

[CK76]

Just a small one is there a way to slightly adjust the VBA a little to get it rename a few columns headers?

Actually, code isn't doing anything with headers. Since headers will be constant. I skip them in import process.
It's just manual copy and paste from source csv file. You can change it to anything you want, and it won't impact the code.