2
$\begingroup$

I'm trying to help a student with a project. The task is to generate CNC G-Code from a Solidworks hole table export (CSV) using VBA.

The problem is the export format is going to be a difficult to parse. Samples include the following:

TAG,X LOC,Y LOC,SIZE
A1,15,50,<MOD-DIAM> 10 <HOLE-DEPTH> 10
B1,30,80,<MOD-DIAM> 8 THRU ALL

M10 - 6H THRU ALL
C2,134.50,18,<MOD-DIAM> 8.50 THRU ALL

<HOLE-SPOT><MOD-DIAM> 23.79 <HOLE-DEPTH> 7.50
D2,75,24.50,<MOD-DIAM> 13.50 THRU ALL

Each tool size seems to be assigned a letter and a sequence number. That's fair enough and leads to the hope that we could sort by first letter. The problem is that the metric tap sizes follow the same format. I'm sure there are other problems the more we dig.

Are we missing a trick here on the export or has anyone any ideas? I can't find any Solidworks documentation on the matter.


Solution

Based on Jim S's answer below it became clear that complex holes (tapped and counterbore, for example) are split by CR-CR-LF. This is easy enough to filter out in VBA.

enter image description here

Figure 1. A screen grab of the code as displayed by Notepad++ with View | Show Symbol | Show end of line turned on. These three lines should all be one.

Code snippet 1

Private Sub CommandButton1_Click()
    'From http://codevba.com/office/read_text_file_into_string_variable.htm#.XkHkHM77R1I
    Dim strFilename As String: strFilename = Application.ActiveWorkbook.Path & "\HoleTablecsv"
    Dim strFileContent As String
    Dim iFile As Integer: iFile = FreeFile
    Open strFilename For Input As #iFile
       strFileContent = Input(LOF(iFile), iFile)
    Close #iFile
    a = Replace(strFileContent, vbCr & vbCrLf, " ")
    Debug.Print a
End Sub
$\endgroup$
2
$\begingroup$

The problem is that the CSV export is including the new line characters that are part of the hole callout. So this:

A1,2.07,9.78,<MOD-DIAM> .257 THRU
5/16-18 UNC  THRU

should really look like this:

A1,2.07,9.78,<MOD-DIAM> .257 THRU 5/16-18 UNC  THRU

You can perform a string replace of any pair or new line characters with some delimiter of your own (say, {new-line}) to make parsing easier, and you can get data like this:

TAG,X LOC,Y LOC,SIZE
A1,2.07,9.78,<MOD-DIAM> .257 THRU{new-line}5/16-18 UNC  THRU
A2,2.53,6.28,<MOD-DIAM> .257 THRU{new-line}5/16-18 UNC  THRU
B1,4.72,5.03,<MOD-DIAM> .197 THRU{new-line}M6X1.0 - 6H THRU
B2,5.88,8.49,<MOD-DIAM> .197 THRU{new-line}M6X1.0 - 6H THRU
B3,7.74,6.57,<MOD-DIAM> .197 THRU{new-line}M6X1.0 - 6H THRU
C1,6.05,3.38,<MOD-DIAM> .266 THRU{new-line}<HOLE-SINK><MOD-DIAM> .531 X 82°
C2,7.09,9.74,<MOD-DIAM> .266 THRU{new-line}<HOLE-SINK><MOD-DIAM> .531 X 82°
C3,7.69,2.61,<MOD-DIAM> .266 THRU{new-line}<HOLE-SINK><MOD-DIAM> .531 X 82°
C4,9.63,8.89,<MOD-DIAM> .266 THRU{new-line}<HOLE-SINK><MOD-DIAM> .531 X 82°
$\endgroup$
1
  • $\begingroup$ A good parser generator should be able to handle this easily. $\endgroup$
    – joojaa
    Feb 10 '20 at 16:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.