Excel guru's please comment

New Home Forum LowRider Advice – LowRider Excel guru's please comment

This topic contains 6 replies, has 7 voices, and was last updated by  thesfreader 1 week, 1 day ago.

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #117402

    Mark Selig
    Participant

    I have been toying with an idea.  I want to take my GCode one created into a text document and drop it into Excel.  I want to drop it into column B.  Then in Column A I want to put =IF B2 Z<0,”FAIL”,”PASS”) Then use conditional formatting to color the cells red for fail and green for pass.  I am just not sure how to write the If than to look at each line of the code to see if value of Z drops below 0.  I would also like to build in a little caution zone like if Z<.1 Fail, If Z is between .1 and 0 then caution and Z>0 then pass.  The caution could then be defined in the conditional formatting to be a yellow color.  Then I want to save the spread sheet as a blank and be able to drop in my GCode after I get it out of the post processor just as a second visual to help with Crashes.  This would be nothing more than one extra set of eyes for me to review to make sure I did not miss anything.

    Those with Excel here are my two questions.

    1. how could I take the GCode and copy it into Excel and have each line of text go into a different cell B2 and down from there?
    2. what would the if then statement look like so that it would read each Z value down the line and do this? Am I close?
    3. Is there anything else that would be a good thing to look for in the GCode prior to running it on my machine.

    The entire idea behind this is to help put an extra layer of safety in place to help me from crashing this machine into a work piece or driving the bit through a project.

    #117403

    Ryan
    Keymaster

    I am not that good with excel, but if you drop it in repetier and show travel moves you can view it from the side and quickly see if anything is too low.

    2 users thanked author for this post.
    #117410

    Jeffeb3
    Participant

    Gcode and excel won’t mix very well together. I haven’t done any of the visual basic programming in excel, but I did some very complicated stuff with the equations they give you. It is not going to be super easy.

    I would do this in python, and I wonder if there’s a good playground environment for you (I assume you’re in windows). But just doing some basic text parsing would be pretty easy in python. Something like:

    with open('input.gcode', 'r') as gcode:
        for line in gcode:
            words = line.upper().split()
    
            if len(words) == 0:
                continue
    
            if words[0] in ['G1', 'G0']:
                for word in words:
                    if word.startswith("Z"):
                        valueString = word[1:].strip()
                        if len(valueString) == 0:
                            continue
    
                        z_height = float(valueString)
    
                        if z_height < 0.0:
                            print("Error, Z is {}".format(z_height))
                        elif z_height < 0.1:
                            print("Caution, Z is {}".format(z_height))
                        else:
                            continue
    
    

    If you’ve ever wanted to learn some python, this would be a great little project to start with.

    OK, now you’ve read my solution, I can help with yours. You basically have to do the same thing though. You have to split up the command into words, and look at each word to see if it has a Z in it (or at the beginning) and then take the rest of the word and convert it to a number. Then you can search the numbers for values that bother you.

    So what I would do is import the file as a csv (excel will separate words into separate columns if you choose white space as a delimiter). Leave that raw data sheet alone, since it will be destroyed whenever there is a new gcode file.

    Then I would make an equation sheet that (for each cell) would be something like IF(starts with Z, substring from 1 to the end , “”). In the next set of columns, I would convert that string to a number. In the front page, I would make a cell for each column, and do the check on the number and apply conditional formatting.

    This in an interesting little problem. But yeah, Ryan’s solution is probably faster.

    1 user thanked author for this post.
    #117413

    Tim
    Participant

    Does whatever program you’re using to generate gcode not do simulation?

    1 user thanked author for this post.
    #117414

    K Cummins
    Participant

    It wouldn’t be that bad. I’ve debased myself in worse ways inside Excel… But it’s still using gas chromatography–mass spectrometry when a cheap smoke detector will do the job.

    1 user thanked author for this post.
    #117470

    Jamie
    Participant

    Here’s something I threw together. One thing that’s possibly interesting is the ability to use number filtering, for example you can filter for Z < -3 and then do a plot of the X/Y coordinates. But you have to be careful with the plots because if you have say 50,000 lines Excel gets sluggish.

    Note, if you get too creative with G92 you can still crash your tool and this program won’t detect it.

    Sub Macro1()
        myFile = Application.GetOpenFilename("Gcode Files (*.gcode; *.nc),*.gcode;*.nc")
        
        ActiveSheet.Cells.Clear
        
        Row = 1
        
        Xpos = 0
        Ypos = 0
        Zpos = 0
        
        Cells(Row, 1) = "Gcode"
        Cells(Row, 2) = "G or M command"
        Cells(Row, 3) = "X position"
        Cells(Row, 4) = "Y position"
        Cells(Row, 5) = "Z position"
        
        Open myFile For Input As #1
        Do Until EOF(1)
            Row = Row + 1
            Line Input #1, textline
            Cells(Row, 1) = textline
            
            Words = Split(UCase(textline))
            
            If UBound(Words) < 0 Then
                Cells(Row, 2) = "-"
                GoTo ContinueDo
            End If
            
            If Left(Words(0), 1) <> "G" And Left(Words(0), 1) <> "M" Then
                Cells(Row, 2) = "-"
                GoTo ContinueDo
            End If
                
            Cmd = Words(0)
            
            If Len(Cmd) = 2 And Left(Cmd, 1) = "G" Then
                Cmd = "G0" & Mid(Cmd, 2)
            End If
    
            Cells(Row, 2) = Cmd
            
            If Cmd = "G00" Or Cmd = "G01" Or Cmd = "G02" Or Cmd = "G03" Then
                For Each w In Words
                    If Left(w, 1) = "X" Then
                        Xpos = Val(Mid(w, 2))
                    ElseIf Left(w, 1) = "Y" Then
                        Ypos = Val(Mid(w, 2))
                    ElseIf Left(w, 1) = "Z" Then
                        Zpos = Val(Mid(w, 2))
                    End If
                Next
            End If
            
    ContinueDo:
            Cells(Row, 3) = Xpos
            Cells(Row, 4) = Ypos
            Cells(Row, 5) = Zpos
        Loop
        
        Close #1
    
    End Sub
    1 user thanked author for this post.
    #117481

    thesfreader
    Participant

    Also note that you should take into account whether you’re in absolute or relative mode (yep, and G92 too)

    1 user thanked author for this post.
Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic.