Excel Data Manipulation

[geniusintraining]

Hello Everyone,

I am uploading a CSV from a Click PLC, doing so the rows are in groups of 10 cells (columns B-K), below that in the next row is also 10 and for 4500 cells (450 rows down), my PLC data is 6 cells worth of data (B-G) I need to shift H-K down one row and to the front B-G, I can do this by cutting and inserting cells but this is going to take FOREVER.... can someone point me in the write direction for an easier way of doing this.

Below is what I have done with cut and insert manually, the 'good data' is after I change it and below is how it looks after its imported from the CSV/PLC

2-9 is what I did and the format that I need it in, 11-21 is how it looks when its imported from the Click

 

[parky]

If you have VBA enabled in excel, then create a function to do it for you, this can be done in a couple of ways, first just create a function to move the data around & just run it in the VBA IDE, secondly place a button on the sheet & associate it with the VBA function or at some point on a cell change call the function. There are plenty of examples google for using VBA on spreadsheets.
Even if longhand it becomes a sort of macro so you only write it once, I did many automated spreadsheets for our production guys, they all had access to excel, this was done by creating a macro enabled template, did an SQL to populate sheet 2 for example On loading the template a date picker popped up, the user selected a date for the report, this ran a routine in VBA to grab the data & put it into sheet 2, after this it ran another routine that copied groups of data onto sheet 1 that had been formatted & did some maths for production information.
A simple example (note: off hand not sure of if this is correct for referencing cells but goes something like this Sheet("Sheet1").Cells(1,1) = Sheet("Sheet2").Cells(1,4),
perhaps look at this for examples.
https://www.automateexcel.com/vba-code-examples/

 

[drbitboy]

Not sure I understand.


You want to

• copy cells [H11:K11] to [B12:E12]
• then [H12:K12] to [B12:E13],
• etc.?

Am I missing something?

 

[parky]

If every csv is always in the same format i.e. you need to move the same group of cells then macros are the way, however, if each CSV is different then your stuck.
One other way is to record some macros, for example you start a macro, do the move by cutting & pasting then save it, then select macros, get the one you want & it will do the exact thing you did with the cut & paste you can create many macros depending what you want to do these effectively create a VBA script based on your keyboard or mouse actions.

 

[TheWaterboy]

You can record your keystrokes to an excel macro and then modify / loop it to move stuff around.
Out of box thought . . . can you change how the click exports or perhaps force a CR into the CSV every X characters using a text editor and regex?

Or perhaps bring it in as text data using the import wizard and slice it up that way.

 

[Mispeld]

When moving from H-K to B-E, is the whole row shifting down by one, as implied by "cut and insert"? That is, after manipulating the spreadsheet, do you end up with twice as many rows? If so, it would seem like column F and G would contain formulas, but that does not seem consistent with the context.

 

[drbitboy]

Does each row of the [Hn:Kn] cells overwrite the data in [Bn+1:En+1], or do the [Hn:Kn] cells get their own row?


I am sure whatever you want to do would be trivial with Python or another scripting language, applied directly to the CSV.

I have attached headers and five lines of data in a sample CSV archived into a .ZIP; fix that to a CSV that is arranged the way you want and post the result; I'll write some code to do it in one step, in whatever language is convenient for you.

hdr0,hdr1,hdr2,hdr3,hdr4,hdr5,hdr6,hdr7,hdr8,hdr9
0,1,2,3,4,5,6,7,8,9
10,11,12,13,14,15,16,17,18,19
20,21,22,23,24,25,26,27,28,29
30,31,32,33,34,35,36,37,38,39
40,41,42,43,44,45,46,47,48,49

 

[TheWaterboy]

in whatever language is convenient for you.

MASM please.

 

[parky]

Forth?

 

[MikeyN]

I am guessing that the format is going to remain the same throughout the CSV. My solution requires a second sheet, aptly named "Sheet2". I inserted an activeX Command Button on sheet1, used this VBA, and it seemed to work pretty well on some data that I duplicated based on your screen shot. The first image, "Duplicated Spreadsheet" is how I imagined your data starting. The second image, "Data Moved" is the data on sheet 2 and how I imagined you wanted the data to look. You would still need to copy the date headers, but it is considerably easier than cutting and pasting the amount of data presented.

Option Explicit

Private Sub CommandButton1_Click()

'Declare variables
Dim column As Integer
Dim row As Integer
Dim newColumn As Integer
Dim newRow As Integer

' Initialize variables to 2. This makes sure that we aren't grabbing the erroneous data in column 1 or row 1 if we don't want to.
' The addresses will be moved every time we go to a new line on sheet 2.
column = 2
row = 2
newColumn = 2
newRow = 2

' While loop. Change the value after row < to include or exclude more or less rows as needed.
While row < 450

    ' When you reach the end, go back to the beginning! (Carriage return followed by line feed)
    ' This controls the position that data is grabbed from on Sheet 1
    ' Change the top value to the number of the column where you want to stop grabbing data plus 1. (a = 1, l = 12)
    If column = 12 Then
        column = 2
        row = row + 1
    End If
        
    ' When you reach the end, go back to the beginning! (Carriage return followed by line feed)
    ' This controls the position that data is placed on Sheet 2
    ' Change the top value to the number of the column you would like to stop entering data plus 1. (a = 1, H = 8)
    
    If newColumn = 8 Then
        ' Print the address number every time we go to a new line.
        ThisWorkbook.Sheets("Sheet2").Cells(newRow, 1).Value = ThisWorkbook.Sheets("Sheet1").Cells(newRow, 1) 'Print Address
        
        newColumn = 2
        newRow = newRow + 1
    End If
    
    'Print the data from Sheet 1 into Sheet 2
    ThisWorkbook.Sheets("Sheet2").Cells(newRow, newColumn).Value = ThisWorkbook.Sheets("Sheet1").Cells(row, column).Value
    
    ' Move on to the next column.
    column = column + 1
    newColumn = newColumn + 1
    
Wend
    
    
End Sub

 

[drbitboy]

Forth?

MASM please.

https://www.youtube.com/watch?v=5YztNYiZrzo

 

[geniusintraining]

Thanks guys!

MikeyN if you are ever in NC or need anything from my website let me know.... this worked PERFECT!!!

Or if you have a Paypal account PM me your address

 

[Dayvieboy]

Stopped using .csv files years ago for the most part

We just create pin chart type templates directly on the GUI
that look almost like a csv only better

Then you do do not need to import & export anything.

No DDEPoke or communication script to write.
It is real time live PLC data that every body can read & modify if allowed.

Engineers & Scientists can delete steps, modify timers
or devices without needing a PLC guru or Studio5000 installed and
another $ 9,000 software license on the machine
when the tool is shipped to the customer site

Changes are saved to a recipe array so they can be recalled & viewed

Using indexed offset &
sometimes multidimensional arrays
the data can be moved around, lines inserted, deleted etc.
directly in the PLC