Automatically transferring, organizing, and analyzing sets of data from one csv file into an xlsm

[wicked1591]

I have a machine that automatically collects readings from sensors and puts them into a csv file. Each time I run my machine, it collects 68 (but sometimes 69) rows of readings and puts them into 3 columns (time, force, tension in that order). For example, the raw data for one set of readings would go from cell A1 to C69 if there are 68 readings.

I want to be able to automatically copy this data into a .xlsm file where each set of data can then be automatically and separately analyzed. My critera for analysis are that a graph of data is generated, the maximum of the force column is displayed, and the difference between peak tension and initial tension is displayed.

The raw data in the csv continuously adds to columns A-C, but when it is copied I would like each set of data to start every 4 columns in the analysis template. So the first data set is A-C, the second is D-F, etc. 

The logic I had in mind for the VBA would be a conditional that checks if the next 68 rows of the csv are empty, and if they are not, then the data is transferred. Then, another conditional would check if the cells in the analysis template are empty, and if they aren't, then the analysis criteria is generated.

Someone with a somewhat similar problem to me found this code, although I'm a beginner to VBA and do not know exactly how to modify it to meet my needs.

Sub CopyValueDown() 

Dim lRow As Integer

    lRow = Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
    If lRow > 1 Then 
        Sheets("Report").Range("A2:B2").AutoFill _
                  Destination:=Range("A2:B" & lRow)
    end if
End Sub

The main problem I'm having with this is that the data is sometimes 69 readings due to the extremely fast sensor read time. I have tried writing a line to the serial monitor that creates a space between each set of data so I could check for that to differentiate between data sets, but something about my baud rate causes it to print strange characters instead of a space, so I have to check if column B or C is empty, since A is still populated by strange characters.

I have created a template for the data analysis file with just one set of data that I will attach. I will also attach the raw data csv file which contains 5 readings, and also demonstrates how the readings are inconsistent and occasionally take up 69 rows instead of 68. The readings can be differentiated by a human by the time resetting and the tension dropping.

[#2]

@wicked1591 Have you tried Power Query? Specifically the Combine Binaries option? You can save your .csv files to a single folder, then use PQ to import and append the data to the reporting workbook. As you add new files, just refresh, and PQ will update only the newest additions. PQ will also append the filename to each dataset, so that will allow you to make your differentiation between them.

It will eliminate copy/pasting, and you can create summaries with PivotTables and charts. If you add a slicer for dataset, then you can quickly switch views.

HTH

[#3]

@Smitty Smith  Thanks for the reply. I don't think power query is right for me because all of my data is coming from one csv file, and I don't really have a time efficient way to separate the data sets into different files.

I have done some thinking to simplify the problem though, and this is what I really need:

I want to take 68 cells by 3 columns from the csv, copy that, and paste it in a xls file every 4th column.

So, it would take cells A1:C69 and copy them to A1 in another sheet. Then, cells A70:C137 would copy to A4 in the other sheet. The problem I have is that this example only transfers 2 data sets. I know how to copy these specific cells, but what I don't know how to do and what to do is have it function for an indefinite number of cells. So every 68x3 cells, copy into the 4th column over in the same row.

Is there a VBA function that would take X cells and cut/copy them to every Yth column?

[#4]

@wicked1591 You might need to play with the offsets a bit, but this should get you started:

Sub foo()
'   Copy from
    '   A1:C69
    '   A70: C137, etc
    
    Dim i As Long
    Dim x As Long
    
    Dim lr As Long
    Dim lc As Long
    
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    x = 69
    
    For i = 1 To lr Step 69
        lc = Cells(1, Columns.Count).End(xlToLeft).Offset(, 2).Column
        Range("A" & i & ":C" & x).Copy Cells(1, lc)
        x = x + 69
    Next i
        
End Sub