Extracting Data

[LSR]

Dear ALL,

I need guide to extract or write data in Excel. This topic might have been posted before or something similar but anyway, I need some guidance.
I know how to extract or write data in text (.txt) file and data (.dat) file but not in Excel.

Usually the code start with:
(setq file1 (open "/drill.txt" "r"))
(repeat X
(setq F1 (read-line file1))
)

I need in particular, location of Points (X,Y,Z) and Cirlce (X,Y). also maybe some attributes.
Can anyone show me how? i.e. data in every cell (A1; A2; C1... etc.)

Thank you,

LSR

[CADTutor]

If you format the extracted data as a .CSV file, you will be able to open it in Excel.

[fuccaro]

LSR

If you wish to use AutoLisp you dont have much options. I would suggest to use the CSV format to write/read data with AutoLisp routines. That means that you may open/manipulate the file in Excel but you must save it in CSV format before the AutoLisp routines can use it.
If you relly need to work with XLS files so you must use Visual Lisp or Visual Basic for Applications (VBA).
Maybe you will wish to see two AutoLisp routines I wrote:
THIS (http://cadtutor.net/forum/viewtopic.php?t=1785&)one will extract the coords in a CSV file.
And THIS (http://cadtutor.net/forum/viewtopic.php?t=2052&) is for extracting attributes.

[fuccaro]

Again: CADTutor is the fastest poster around here! :)

[LSR]

If you relly need to work with XLS files so you must use Visual Lisp or Visual Basic for Applications (VBA).

Yes, that is exactly what I want. I want to start in Excel (.xls) file rather than CVS format (Not that there is something wrong with this format).
I want to gather or collect all my data first in Excel then pass the data to Autocad. Or vice versa (but that's another topic for later)
Can you give me some hint? or show me some of your previous routines?

Thank you again FUCCARO and CT, awaiting your reply.

Regards,

LSR

[fuccaro]

LSR
Sorry, I use only AutoLisp. It can be done in VBA but you will need help from others.
But it is perfectly possible to collect/manipulate the data in Excel and to save the file in CSV format.

[Murph]

LSR,
What I have done in the past is to give each entity it's own row, then to put all the same info in the same column (i.e. X coordinate in A, Y in B, and so on). Once that is done it is just a matter of doing selection sets to get the items you want the information on and telling it where to put the info in Excel

Example Code:

Set objSelSet = vbdPowerSet("attributeextract")
intType(0) = 0
intType(1) = 2
varData(0) = "INSERT"
varData(1) = "*"
objSelSet.Select acSelectionSetAll, _
filtertype:=intType, filterdata:=varData
For Each objEnt In objSelSet
With objEnt
CP.cells(intRow, 11).Value = objEnt.Layer
If .HasAttributes Then
varAtts = .GetAttributes
For intCnt = LBound(varAtts) To UBound(varAtts)
CP.cells(intRow, intCnt + 1).NumberFormat = "@"
CP.cells(intRow, intCnt + 1).Value = varAtts(intCnt).TextString
CP.cells(intRow, intCnt + 12).NumberFormat = "@"
CP.cells(intRow, intCnt + 12).Value = varAtts(intCnt).Handle
Next intCnt
End If
varName = objEnt.Name
faX = objEnt.InsertionPoint(0)
faY = objEnt.InsertionPoint(1)
CP.cells(intRow, 7).Value = varName
If Left(varName, 2) = "Fa" Then
CP.cells(intRow, 8).Value = faX
CP.cells(intRow, 9).Value = faY
ElseIf Left(varName, 2) = "fa" Then
CP.cells(intRow, 8).Value = faX
CP.cells(intRow, 9).Value = faY
End If
End With
intRow = intRow + 1
Next objEnt


If you could give me an example of what exactly you are trying to do I can help you to build a VBA macro to accomplish what you need.

Murph

[LSR]

Hi guys,

In my original post above I've shown a partial code to read file from a text format.

Murph, here is an example.
I have a plate to be drill with many holes of different sizes and configuration such as threads, countersunk, tolerance to name a few.
Some of these holes are bored radially, some are rows & colums and others are random locations.
The location would be the XYZ and attributes would be the configuration.

The Excel files comes from our Customer thru Sales, to Engineering and goes to the shop floor and goes back again vice versa.
Changing format as you can imagine can cause problem sometimes and also Excel looks good for presentation.

Hope I'm clear with my example.
Thanks Murph.

LSR

[fuccaro]

Again: that is nothing wrong opening the CSV file in Excel. You get the full power in data manipulation/presentation. After the data are processed in Excel you may save them back in CSV format...

[Murph]

I have a plate to be drill with many holes of different sizes and configuration such as threads, countersunk, tolerance to name a few.
Some of these holes are bored radially, some are rows & colums and others are random locations.
The location would be the XYZ and attributes would be the configuration.


We can help you to insert a block at the XYZ coordinates and assign the attributes accordingly.

First question, do you want to use just one block for holes, letting the attributes explain the details OR do you want to use different blocks for different hole configurations?

Second question, are the coordinates in 3 seperate columns or in 1 column and seperated by commas?

Murph

[LSR]

Hello again,

Fuccaro: I agree with you totally 110 percent. I did it but the thing is I can write formulation in Excel on how to achived my datum unfortunately you can not do that with the other said format.

Murph: For the sake of simplicity just assume one block for all holes(actually, different hole sizes in rows). For location XYZ in 3 columns and attributes in another column. Once you have shown me the main code or how to extract these datum from Excel I could expand it myself to whatever I want to.

I really appreciate you guys your effort.

See ya,

LSR

[Murph]

scl = 1
Coord(0) = Range("A" & curRow).Value
Coord(1) = Range("B" & curRow).Value
Coord(2) = Range("C" & curRow).Value
insName = your block name here
varCoords = Coord
Set blockRefObj = ThisDrawing1.PaperSpace.InsertBlock(varCoords, insName, scl, scl, scl, 0)
varAtts = blockRefObj.GetAttributes
For intLoop = LBound(varAtts) To UBound(varAtts)
Set attRefObj = varAtts(intLoop)
attRefObj.TextString = cells(curRow, intLoop + 3).Value
'intLoop + 4 is gotten from the fact that intLoop will start at 1

' and assumes the attributes start in column D right after the coords
Next intLoop

This code goes into a new module in Excel VBA Editor. You will need to add a reference to your version of autocad (goto Tools menu, References, then select the appropriate version).
Write back with any questions.

Murph

[LSR]

Thank you, this is good enough for me.

LSR