seperate G-Code

[Dr.Evil925]

I need to seperate out a G-Code file into multiple cells. Any sugestions?
This is my start file.

N3G54G90G0X6.2Y.185S10000M3 <colgroup><col width="64"></colgroup><tbody> </tbody>

<tbody>
</tbody>

I want my result to be

N3

G54

G90

G0

X6.2

Y.185

S10000

M3

<tbody>
</tbody>

 

[Rick Rothstein]

Hmm, Rick I'm wondering if we again have an issue of different operating systems, processors, memory, Excel versions or the like? I've copied the data as you described and your code consistently takes about 3.5 seconds for me.

Below is my corrected code from post #12. For the same data this is running in 1.08 seconds. I'd be interested to hear what time result it gives on other systems.

Peter wins... by a lot! Here are the timings from my computer (the ones I did not post were taking so long, I stopped them before they finished)...

Peter - Message 18:  0.578125 seconds

Rick - Message 16:   1.625 seconds

SHG - Message 11:    51.45508 seconds

PGC - Message 15:    53.125 seconds

In defense of my code, I said about it that "I'm thinking this may be one of the fastest possible codes for this application" and it is "one of the fastest", I never said it was the fastest.

 

[Peter_SSs]

Here are the timings from my computer...

Given those results ... I want a new computer!!!

 

[Rick Rothstein]

Given those results ... I want a new computer!!!

Yeah, I got this computer at the end of November... and it is not even the fastest model I could have bought... there was an option to overclock the CPU by about 50%, but I decided I did not need that much speed (plus there was a concern about stability given how warm this room becomes in the summer).

 

[Rick Rothstein]

Peter wins... by a lot! Here are the timings from my computer (the ones I did not post were taking so long, I stopped them before they finished)...

Peter - Message 18:  0.578125 seconds

Rick - Message 16:   1.625 seconds

SHG - Message 11:    51.45508 seconds

PGC - Message 15:    53.125 seconds

In defense of my code, I said about it that "I'm thinking this may be one of the fastest possible codes for this application" and it is "one of the fastest", I never said it was the fastest.

I have reworked my code a little bit and I have gotten much closer to your speed... the following code runs at about 0.6445313 seconds, so I am within about 7/100 of a second of your code's speed... a statistical tie in my books).

Sub SplitGCodesQuicker()
  Dim R As Long, C As Long, LastRow As Long, MaxCols As Long, Letter As Long, Index As Long
  Dim GCodes() As String, vIn As Variant, vOut As Variant, t
  MaxCols = 1 + Evaluate("MAX(LEN(A1:A" & Rows.Count - 1 & "))") / 2
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  vIn = Range("A2:A" & LastRow)
  ReDim vOut(1 To LastRow - 1, 1 To MaxCols)
  For R = 2 To LastRow
    Letter = 1
    Index = 1
    For C = 2 To Len(vIn(R - 1, 1))
      If Mid(vIn(R - 1, 1), C, 1) Like "[A-Za-z]" Then
        vOut(R - 1, Index) = Mid(vIn(R - 1, 1), Letter, C - Letter)
        Letter = C
        Index = Index + 1
      End If
    Next
    vOut(R - 1, Index) = Mid(vIn(R - 1, 1), Letter, Len(vIn(R - 1, 1)) - Letter + 1)
  Next
  Range("B2:B" & LastRow).Resize(, MaxCols) = vOut
End Sub

The interesting thing is I can shave a full 1/10 of a second off my timing by replacing this line of code (that would have made my code faster than yours by the way)...

MaxCols = 1 + Evaluate("MAX(LEN(A1:A" & Rows.Count - 1 & "))") / 2

with this one...

MaxCols = 8

My first line of code for MaxCols calculates to 17 (replacing that calculation with a hard-coded MaxCols=17 only shaves off a hundredth or so of a second, so it is not the hold up)... apparently the size of the array that VB has to deal with noticeably affects the timing. Unfortunately, I cannot think of an efficient way to calculate that value of 8. Later today I may try using your ReDim Preserve idea and see what that does.

By the way, I just looked... our codes are not all that that dissimilar... it looks like you repeatedly truncate the text coupled with Left function calls in order to march down each string whereas I use a moving Mid function call instead.

 

[shg]

Awright, one more for me, taking off on Peter's approach. I measure 0.64s on my laptop.

Sub shGCodes()
    Const nCol      As Long = 10
    Dim asOut()     As String
    Dim iCol        As Long
    Dim avs         As Variant
    Dim nRow        As Long
    Dim iRow        As Long
    Dim f           As Single
    
    f = Timer

    With Range("A2", Cells(Rows.Count, "A").End(xlUp))
        avs = .Value
        nRow = UBound(avs, 1)
        ReDim asOut(1 To nRow, 0 To nCol - 1)

        For iRow = 1 To nRow
            iCol = 0
            asOut(iRow, iCol) = avs(iRow, 1)

            Do While GString(asOut(iRow, iCol), asOut(iRow, iCol + 1), iCol)
            Loop
        Next iRow

        .Offset(, 1).Resize(nRow, nCol).Value = asOut
    End With
    MsgBox Timer - f & " s"
End Sub

Function GString(ByRef sInp As String, ByRef sSub As String, ByRef i As Long) As Boolean
    Dim iChr        As Long

    For iChr = 2 To Len(sInp)
        If Mid(sInp, iChr, 1) Like "[A-Z]" Then
            sSub = Mid(sInp, iChr)
            sInp = Left(sInp, iChr - 1)
            i = i + 1
            GString = True
            Exit For
        End If
    Next iChr
End Function

I was surpised that the Like operator in this (simple) instance is significantly faster than a Case statement.

 

[Peter_SSs]

Function GString(ByRef sInp As String, ByRef sSub As String, ByRef i As Long) As Boolean

My wife says I can't participate in any forums involving GStrings!!

 

[Darth yoda]

Hi guys. Got interested in this thread because I program CNC g-code. I think the original poster bailed out a while back but I still have a question for him.

Why do you need to put this in Excel? There are many good formatters for g-code out there and some are free.

A little info for you guys, the "N" number is a line number and has nothing to do with code itself.
The "G" series code tell the machine how to move (left, right, up ,down, etc.)
F is machine speeds and Q,R,T,K,J..............are variables that help the machines do there job.
Different machine either accept spaces or don't depending on the make and model. Some use line numbers others don't. It's a mixed up world and you have to know your machines likes and needs.

Now the real reason I posted......I'm going to steal your code!!!!!! LOL
This is a nice thread and you guys have helped me a lot on this particular subject. And others for that matters. Thanks!!!

EDIT: g-code is a txt file but it also uses .cn , .dnc , and a host of other extensions. BUT all are basic txt.

 

[Darth yoda]

What about this one guys? It took .094 secs with Peters code.
The % dictates start and finish of the g-code. Anything in parenthesis is an operator note. Peters code separated each word into letters. YOU DONT NEED TO WRITE CODE if you dont want to, its just something to look at.

%
:0081( QL6 PROFILE MAR 7 )

(TURN ON OPTIONAL STOP)

(PART REV 002) 
(PART STICKOUT 5.5 INCHES)
(T1 CUT OFF TOOL) 
(T5 VBMT 35 DEGREE CUTTER)
(T7 1 INCH DRILL)
(T9 BORING BAR)

G00 X10. Z.05
N10 T0505

(PART SET UP)
N20 G00 Z.25
N30 X2.7

(FACING)
N40 G50 S800
N50 S800 M03
N60 G00 X2.5 Z0.
N70 G01 X0. F.01
N80 G00 Z.1

(ROUGH PASS)
N90 G00 X2.2 F.015
N100 G01 Z-4.416 F.015
N110 G00 X2.3 Z.01 F.015
N120 G00 X2.0 F.015
N130 G01 Z-4.416 F.015
N140 G00 X2.1 Z.1 F.015

(CLEARANCE CUT)
N150 G00 X1.388 Z.09 F.006
N160 G01 X1.809 Z-.118 F.006
N170 G00 X2.2 Z.0 F.006

(PROFILE)
N180 G00 X1.388 Z0. F.006
N190 G03 X1.809 Z-.211 R.211 F.006
N200 G01 Z-.492 F.006
N210 G02 X1.837 Z-.595 R.393 F.006
N220 G03 X1.864 Z-.699 F.006
N230 G01 Z-1.181 F.006
N240 G03 X1.837 Z-1.285 R.393 F.006
N250 G02 X1.809 Z-1.388 R.393 F.006
N260 G01 Z-1.868 F.006
N270 G02 X1.825 Z-1.898 R.059 F.006
N280 G01 X1.84 Z-1.911 F.006
N290 G03 X1.850 Z-1.93 R.039 F.006
N300 G01 Z-1.936 F.006
N310 G02 X1.850 Z-1.955 R.393 F.006
N320 G01 X1.840 Z-1.968 F.006
N330 G03 X1.809 Z-1.998 R.059 F.006
N340 G01 Z-3.893 F.006
N350 G01 X1.793 Z-4.291 F.006
N360 G01 Z-4.416 F.006
N370 G00 X10. Z5. F.006
N380 Z10. F.006
N390 M01

(DRILL)
N400 T0707
N410 G50 S400
N420 S400 M03
N430 G00 X0. Z.03 F.03
N450 G01 X0. Z-5.3 F.03
N460 G00 Z.1
N470 T0700
N480 G00 X5. Z5.
N490 M01

(BORING CYCLE)
N500 T0909
N510 G50 S500
N520 S500 M03
N530 G00 Z.02 X1.19 F.006
N540 M01
(LOOK AT CUT DEPTH)
N550 G01 Z-4.43 F.006
N560 G00 Z.02 F.006

(FINISH PASS)
N600 G00 X1.203 F.006
N610 G01 Z-4.43 F.006
N620 G00 X1.1 F.006
N630 G00 Z.5 F.006
N640 G00 X5. Z5. F.006
N650 M01

(CUT OFF)
N660 T0101 M03
N670 G50 S150
N680 G00 X2.3 Z-4.36 F.006
(OVERALL LENGTH PLUS TOOL WIDTH)
N690 G01 X1.3 F.006
N700 G00 X10. F.006
N710 G00 Z10. F.006
N720 M01

(PART STOP)
N730 T0505
N740 G00 Z.1 X1.
N750 T0500
N760 M30
%

 

[Rick Rothstein]

What about this one guys? It took .094 secs with Peters code.
The % dictates start and finish of the g-code. Anything in parenthesis is an operator note. Peters code separated each word into letters. YOU DONT NEED TO WRITE CODE if you dont want to, its just something to look at.
....<<file data snipped for space>>...

You didn't give us an indication of the layout you wanted from that file, so I guessed (if I guessed wrong, the code can be modified). Here is a macro that asks you to select the file, processes that file totally in memory and then outputs the result to the active worksheet (which must be empty... the code checks). The layout I chose is Column A contains the N numbers, the remaining columns contain the numbers (only) from the codes that follow that N number. There is a G number before the first N number... I output as the first line with Column A left blank because there was no N number associated with it. All other lines in the file are ignored.

Sub ProcessGCodeFile()
  Dim X As Long, Z As Long, FileNum As Long, Index As Long
  Dim TotalFile As String, PathFile As String
  Dim NLines() As String, Gspaced() As String, LinesOut() As String
  Const MaxPossibleGCodesPerLine As Long = 20
  If ActiveSheet.UsedRange.Cells.Count = 1 And Len(ActiveSheet.UsedRange.Cells(1).Value) > 0 Then
    MsgBox "The active worksheet must be totally empty..." & vbLf & _
           "I see data on the currently active sheet", vbCritical
    Exit Sub
  End If
  With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Show
    On Error GoTo NoFileSelected
    PathFile = .SelectedItems(1)
  End With
  FileNum = FreeFile
  Open PathFile For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  NLines = Split(Replace(TotalFile, "G00", "N G00", , 1), vbNewLine & "N")
  ReDim LinesOut(1 To UBound(NLines), 1 To MaxPossibleGCodesPerLine)
  For X = 1 To UBound(NLines)
    Gspaced = Split(Split(NLines(X), vbNewLine)(0))
    Index = 0
    For Z = 0 To UBound(Gspaced)
      Index = Index + 1
      LinesOut(X, Index) = Mid(Gspaced(Z), 2 + (Z = 0))
    Next
  Next
  Range("A1").Resize(UBound(LinesOut), UBound(LinesOut, 2)) = LinesOut
NoFileSelected:
End Sub

 

[Peter_SSs]

.. active worksheet (which must be empty... the code checks). .

Sub ProcessGCodeFile()
  If ActiveSheet.UsedRange.Cells.Count = 1 And Len(ActiveSheet.UsedRange.Cells(1).Value) > 0 Then
    MsgBox "The active worksheet must be totally empty..." & vbLf & _
           "I see data on the currently active sheet", vbCritical
    Exit Sub
  End If

Rick, I think that check needs a check.