Any Way to Improve Processing G Code with Excel?

[keithorr]

I have a need to convert text to code. I can do it with Excel, but I can only do one line at at time.
I am using a kinematics program to produce six axis joint angles for a robot arm. The information represents a point to point move and is usually around 50 lines of text as follows:

0.000000 0.000000 0.000000 0.000000 0.000000 0.000000
-0.000000 7.819982 14.789516 0.000002 6.969540 0.000003
0.000000 16.114470 30.837702 -0.000001 14.723211 -0.000001
0.000000 23.386688 45.481628 -0.000000 22.094950 -0.000000
-0.000000 29.612301 59.122925 0.000000 29.510695 -0.000001
-0.000000 33.830772 70.922791 0.000001 37.153328 -0.000000
-0.000000 33.830772 70.999458 0.000001 37.168625 -0.000000

I can copy and paste one line at a time into excel, then copy and paste the result into another text file for use as g code. I am using Excel to round down to four places, add the axis letter and recalculate axis z,b,c:

X 0.0000 Y 7.8200 Z 22.6095 A 0.0000 B 6.9695 C 0.1882
X 0.0000 Y 16.1145 Z 46.9522 A 0.0000 B 14.7232 C 0.3975
X 0.0000 Y 23.3867 Z 68.8683 A 0.0000 B 22.0950 C 0.5966
X 0.0000 Y 29.6123 Z 88.7352 A 0.0000 B 29.5107 C 0.7968
X 0.0000 Y 33.8308 Z 104.7536 A 0.0000 B 37.1533 C 1.0031
X 0.0000 Y 33.8308 Z 104.8303 A 0.0000 B 37.1686 C 1.0036

Very slow moving one line at a time. I don't know what terms to google. I would like a more automated method of converting blocks of space delimited text.

Any clues?
Thanks

[murphy625]

Not sure what your trying to do but Microsoft Access is very good at manipulating data.

Murph

[Kiwi]

If you can copy the whole file, paste into notepad or wordpad. Then import into excel as a delimiter, space, which will put the coords into separate cells.

Kiwi

[HuFlungDung]

Keith,
You need the services of someone who can write a visual basic script for you. If the programs are always short like you said, visual basic will likely run fast enough to do the job.

Are the equations you use always exactly the same format, or is there variation required for that aspect?

[keithorr]

Thanks for the reply.
I did find scripting utilities online based on both VB and C++. I tried a demo version of one this afternoon and it looks like it will do the job.

[Kiwi]

keithorr

If you paste all text data into MSWord this Macro will convert to NC.
(If unsure how to run please advise.)

The "C" NC needs adjusted as I'm not sure how you value is calculated.
If you let me know I will alter as required.

Kiwi

Converted From
-0.000000 7.819982 14.789516 0.000002 6.969540 0.000003
0.000000 16.114470 30.837702 -0.000001 14.723211 -0.000001
0.000000 23.386688 45.481628 -0.000000 22.094950 -0.000000
-0.000000 29.612301 59.122925 0.000000 29.510695 -0.000001
-0.000000 33.830772 70.922791 0.000001 37.153328 -0.000000
-0.000000 33.830772 70.999458 0.000001 37.168625 -0.000000

To this with Macro
X0.0000 Y7.8200 Z22.6095 A0.0000 B6.9695 C0.0000
X0.0000 Y16.1145 Z46.9522 A0.0000 B14.7232 C0.0000
X0.0000 Y23.3867 Z68.8683 A0.0000 B22.0950 C0.0000
X0.0000 Y29.6123 Z88.7352 A0.0000 B29.5107 C0.0000
X0.0000 Y33.8308 Z104.7536 A0.0000 B37.1533 C0.0000
X0.0000 Y33.8308 Z104.8302 A0.0000 B37.1686 C0.0000

=======================================================
Sub Data2GCode()
'
' Macro recorded 16/8/2005 by Kiwi

numLines = 6 'Number of Lines
EOLine = 80

ActiveDocument.Characters.First.Select

For Line = 1 To numLines

XFlag = 1

For charCount = 1 To EOLine
Selection.Characters(1).Select
MyChar = Selection.Characters(1)

If Asc(MyChar) = 13 Then
LineCount = charCount
charCount = EOLine 'End of Line
End If

If CFlag = 1 Then strC = strC & MyChar
If CFlag = 1 And MyChar = " " Then CFlag = 0

If BFlag = 1 Then strB = strB & MyChar
If BFlag = 1 And MyChar = " " Then BFlag = 0: CFlag = 1

If AFlag = 1 Then strA = strA & MyChar
If AFlag = 1 And MyChar = " " Then AFlag = 0: BFlag = 1

If ZFlag = 1 Then strZ = strZ & MyChar
If ZFlag = 1 And MyChar = " " Then ZFlag = 0: AFlag = 1

If YFlag = 1 Then strY = strY & MyChar
If YFlag = 1 And MyChar = " " Then YFlag = 0: ZFlag = 1

If XFlag = 1 Then strX = strX & MyChar
If XFlag = 1 And MyChar = " " Then XFlag = 0: YFlag = 1

If Asc(MyChar) = 13 Then

X = Int(Val(Trim(strX)) * 10000 + 0.5) / 10000
Y = Int(Val(Trim(strY)) * 10000 + 0.5) / 10000
Z = Val(Trim(strY)) + Val(Trim(strZ))
Z = Int(Z * 10000 + 0.5) / 10000
A = Int(Val(Trim(strA)) * 10000 + 0.5) / 10000
B = Int(Val(Trim(strB)) * 10000 + 0.5) / 10000
C = Int(Val(Trim(strC)) * 10000 + 0.5) / 10000

strData = "X" & Format(X, "####0.0000") & " Y" & Format(Y, "####0.0000") & " Z" & Format(Z, "####0.0000") & " A" & Format(A, "####0.0000") & " B" & Format(B, "####0.0000") & " C" & Format(C, "####0.0000") & Chr(10) & Chr(13)

For BS = 1 To LineCount
Selection.TypeBackspace
Next BS

Selection.Font.Color = wdColorBlue
Selection.TypeText Text:=strData
Selection.TypeBackspace
Selection.MoveLeft Unit:=wdCharacter, Count:=1
strData = ""
X = 1: Y = 0: Z = 0: A = 0: B = 0: C = 0
strX = "": strY = "": strZ = "": strA = "": strB = "": strC = ""
XFlag = 1
charCount = EOLine 'End of Line
End If 'If Asc(MyChar) = 13 Then

Selection.MoveRight Unit:=wdCharacter, Count:=1
Next charCount

Next Line

End Sub
====================================

[ger21]

Originally Posted by keithorr Thanks for the reply. I did find scripting utilities online based on both VB and C++. I tried a demo version of one this afternoon and it looks like it will do the job.

Use VBA, it's built into Excel. You can also record what you do as a macro, and play it again, which might work for you.

[keithorr]

Ger21,
WTF???
Do I have a lot to learn!

How you do that? I can start to understand what you have done, but not knowing the specific syntax for math operations, I can't modify what you've done to rejigger the C axis. I'll be search the net just as soon.

Very much appreciated. I was just going to use a macro recorder to duplicate my keyboard strokes-ha!

This looks like the way I need to adjust my thinking cap. Of course I don't know any of the syntax so I'll need to start reading.

The formula is:

X=x, Y=y, Z=y+z, A=a, B=b+(a*.0144), C=c+(a*.02299)+(b*.02299)

In Excel, 15.6183 16.11447 30.837702 3.429 14.723211 26.7743

is converted to:X 15.6183 Y 15.6183 Z 46.9522 A 3.4290 B 14.7575 C 27.1916


I think an improvement for later might be to learn how to enter this in CamSoft G Code directly, which might be an incremental step to learning how to embed the kinematics completely into CamSoft and use XYZ fixed and euler angles for input.

Thanks for the reply.

[DieGuy]

linux shell, put it in a file named cnv.sh and run as ./cnv.sh <input_file_name> <output_file_name>

#!/bin/sh
cat $1 | while read line
do
    F1=`echo $line | cut -f1 -d" "`
    F2=`echo $line | cut -f2 -d" "`
    F3=`echo $line | cut -f3 -d" "`
    F4=`echo $line | cut -f4 -d" "`
    F5=`echo $line | cut -f5 -d" "`
    F6=`echo $line | cut -f6 -d" "`
    echo "X $F1 Y $F2 Z $F3 A $F4 B $F5 C $F6" >> $2
done

That is what Unix was written for originally, text proccessing.

Edit:
For you windows boys, you can get Cygwin to do the work for you.

[Karl_T]

Do you have a level of Camsoft where you define your own Gcodes? If so, this looks like a piece of cake to just write a custom Gcode and be done with it. Come up with a G# and six parameter letters you aren't using and Gcode lines like so:

G123 M15.6183 N16.11447 O30.837702 P3.429 Q14.723211 R26.7743

In G123, do your math:
\999=m
\998=n
\997= {m+n}

and so on.

Then put your motion move in the Gcode after you've calculated everything.

Karl

[RotarySMP]

If you can open that text file in excel with the delimiter set to space, then all you will need to do is insert extra columns, type the Axis letters and formulars in the top line, and then drag fill the lines down.

[keithorr]

Well,

I couldn't get the VB example to replicate the results listed in the earlier post.

I do have a macro that works, not as elegant as the idea of the vb macro but...

Thanks for all the suggestions.