Lines to Search in Excel

[ScoRm]

I am so sorry for asking, but I am desperate to find a faster way.

is it even possible to have lots of LINES (Dataextract in cad) and some lisp to manually search those extracted lines,

and when it sees that LINE in excel, u have manual input of values to save beside the searched line?

 

ok, its like this...

this drawing files have lines all over, and those lines represents sample cuts, those sample cuts have values (usually gold and silver).

now i need to dataextract those lines to put the coordinates in excel format so that we can put the sample cuts values (gold or silver)...

my problem is, its so much time consuming to manually search for a line, and then putting the golds or silver (is found) with so much LINES all over.

So I was thinking maybe I could ask for help. so that the lisp routine can search for the line i want, and put the values i need.

 

again, I am so sorry for asking...

Edited March 18, 2019 by ScoRm

[BIGAL]

So how do you know now what is a gold and silver line ? Is it to do with length orientation some sort of key to be searched on.

 

[ScoRm]

6 hours ago, BIGAL said:

So how do you know now what is a gold and silver line ? Is it to do with length orientation some sort of key to be searched on.

 

no, no key...

manually i am looking at their start points and endpoints(im searching for the line coordinate, not a key)

and to answer the first question... those lines are sample cuts and when they get samples, they test it for everything. meaning that line has various data in it. i only specify silver and gold.

so every single line i need to search will have silver and gold that i will manually input...

i need to visually check it, for me to assign weather those lines will be as single(wall samples) or as composite (face samples) lines.

that is why we use autocad to draw lines and "Dataextract" it to excel file.

Im wondering if autolisp could access excel, so in that case autolisp can get the startpoints and endpoints of a line being selected in autocad, and searching those coordinates in excel file "dataextracted" from that cad. and if the line is searched, autocad will prompt me for values to input (that will be the silvers and golds). and save it beside the row where the line is in...

[BIGAL]

Still totally confused may have to walk away.

[ScoRm]

7 hours ago, BIGAL said:

Still totally confused may have to walk away.

I understand... thank you for trying though. 
basically the lines if you use dataextract command, it will give you XYZ's lengths and angles of a line right?

all i need is for a lisp to search where the coordinate extracted is... dont bother about the silvers and golds, because that is my part, that is where i need to input.

 

This is what the lines looks like (the ones i will export into excel)

 

 

if i select a line, it will give show me the XYZ's of that line right?

I need to find that or search that in the excel extracted. for me to input the silvers and golds

 

 

manually it looks like this.(this is what i do and its time consuming) that is why i ask for help
to automate searching and inputing...

Edited March 21, 2019 by ScoRm

[rlx]

your pic's are not showing. But basically you want to find the row number in excel that contains a specific x y z? 

[ScoRm]

4 hours ago, rlx said:

your pic's are not showing. But basically you want to find the row number in excel that contains a specific x y z? 

YES! but more than that sir, i need do input the silver and gold values too beside it... 

So on the Row that contains the specific X Y Z, i will add gold and silver too. Thats it!

because it is kind of hard when the lines are too much, it is very time consuming

 

 

i will try to edit the pictures...

Edited March 21, 2019 by ScoRm

[rlx]

I think it can be done but also could take some time to make, not sure when I have some time to spare.  What would help is a real (example) of your drawing and of your csv file , either on the forum or by personal message in case you're afraid of possible gold diggers . How big is your drawing (how many lines are we talking here, hundreds , thousands?) and how big is your xls file. If its not extremely big I would first read the complete xls file to a list in lisp , add the data there and when done write the complete list back to a cvs file or maybe directly to a xls file. But csv would obviously the most easy way.

[rlx]

another way to go would be to first attach your golden or silver label directly to the lines in AutoCad and then export the data... with xrecord or something (which I've never used until now).But it would be a good way to put them to use. I assume you have some kind of hybrid drawing with a image attached to it and have (painstakingly) put all the lines in. Just selecting the lines and putting them in layer 'gold' or 'silver' would perhaps be an option?

[BIGAL]

Like Rlx if you have the lines in excel as startpt endpt and gold then you can probably do what you want in excel and just create new lines in Autocad. It is very simple to make. No real coding just using simple excel Concatentate command and just copy down for every row. Copy a column to the Autocad command line all done. Much much easier than spending lots of time searching lists.

 

(setvar 'clayer "Gold")  Line XYZ1 XYZ2 repeated for each line.

(setvar 'clayer "Silver")  Line XYZ1 XYZ2

 

Post like 10 lines excel sample.

[ScoRm]

17 hours ago, rlx said:

I think it can be done but also could take some time to make, not sure when I have some time to spare.  What would help is a real (example) of your drawing and of your csv file , either on the forum or by personal message in case you're afraid of possible gold diggers . How big is your drawing (how many lines are we talking here, hundreds , thousands?) and how big is your xls file. If its not extremely big I would first read the complete xls file to a list in lisp , add the data there and when done write the complete list back to a cvs file or maybe directly to a xls file. But csv would obviously the most easy way.

Thank you for taking time on me sir.

about the lines, it really depends on the map, but most single files have thousands. less than 20K maybe usually 10K up

the xls file will depent on the extracted lines from CAD

 

 

i try to copy some of the map, and give a sample DWG. 
PS: 

the text are not encoded, I encoded it myself so that i will not include the image map here, because 1 image = 10mb 

Sample Test.dwg Sample Test.xls

[rlx]

ok, I'll post what I've made so far. Program is not yet finished, just to give you a preview. Think I will make a small change to the dialog for better workflow.

Things I want to know , are the start and end coordinates allways adjacent (column a b c d) or did you make it so just for the test? Its not really a big deal , its just for me how to read te cell values from excel , in one go (for example range A3:D3) or like for seperate cell adresses like A1 , B1 , K1, L1 whatever. Like I said it's no big deal , just for speed its simpeler to read 1 range than 2x2 or 4x1 if you understand what I mean.

 

 

 

 

In the version I've made so far it opens the excel file you select with button 'filename' , it reads the values from the column letters specified in the next part of the dialog. I have given them the default from your xls example A-D.

 

Now for the 'tricky' part. Your example is rather small but you mentioned 10 or 20k lines... that's a lot of excel rows. In order to speed things up I came up with the folowing idea (and I don't know if it will always work) : I read the 4 coordinates (which are stored as a string in your xls file) , I convert them back to a number and sum them to a single number. So now , if you have 20k of lines in your drawing , I build an index list of 20k (single) numbers. Else I would either build a list of 4 strings times 20k... you do the math. Or for every line search I would have to start at the first data row in excel and read every line untill I hit gold (or silver)....just some inside info into my brain...

 

Ok , when you have started goldfinger , xls will be opened automatically , index will be build. If xls is big this may or may not take some time. But when index has been build , assuming you are in the drawing with the hidden treasures , you can click button 'Select Line' and select a line (amazing no?). It will then do the same as it did with the index , sum all coordinates to a single nummer and then try to find it in the index. If it does , you'll know!

 

It doesn't add any data yet , just want to know if my approach is working or not.

 

RLX

GoldFinger.LSP

Edited March 23, 2019 by rlx

[ktbjx]

21 hours ago, rlx said:

ok, I'll post what I've made so far. Program is not yet finished, just to give you a preview. Think I will make a small change to the dialog for better workflow.

Things I want to know , are the start and end coordinates allways adjacent (column a b c d) or did you make it so just for the test? Its not really a big deal , its just for me how to read te cell values from excel , in one go (for example range A3:D3) or like for seperate cell adresses like A1 , B1 , K1, L1 whatever. Like I said it's no big deal , just for speed its simpeler to read 1 range than 2x2 or 4x1 if you understand what I mean.

 

 

 

 

In the version I've made so far it opens the excel file you select with button 'filename' , it reads the values from the column letters specified in the next part of the dialog. I have given them the default from your xls example A-D.

 

Now for the 'tricky' part. Your example is rather small but you mentioned 10 or 20k lines... that's a lot of excel rows. In order to speed things up I came up with the folowing idea (and I don't know if it will always work) : I read the 4 coordinates (which are stored as a string in your xls file) , I convert them back to a number and sum them to a single number. So now , if you have 20k of lines in your drawing , I build an index list of 20k (single) numbers. Else I would either build a list of 4 strings times 20k... you do the math. Or for every line search I would have to start at the first data row in excel and read every line untill I hit gold (or silver)....just some inside info into my brain...

 

Ok , when you have started goldfinger , xls will be opened automatically , index will be build. If xls is big this may or may not take some time. But when index has been build , assuming you are in the drawing with the hidden treasures , you can click button 'Select Line' and select a line (amazing no?). It will then do the same as it did with the index , sum all coordinates to a single nummer and then try to find it in the index. If it does , you'll know!

 

It doesn't add any data yet , just want to know if my approach is working or not.

 

RLX

GoldFinger.LSP 17.71 kB · 2 downloads

 

OMG I am so excited to try this first thing in the morning!
Thank you so much for helping me.
and your question above, about coordinated in ABCD... yes it is always like that

StartX StartY EndX EndY StartZ EndZ Length Angle

 

For the Searching, Just a Thought. What if you concatenate all StartX StartY EndX EndY so you will have a unique single ID, my concern is,

what if 2 totally different coordinates sums up with the same number?

 

you are so amazing!

Edited March 24, 2019 by ktbjx

[rlx]

well , the theory is (my still to be proven theory) that with four digits accuracy the sum of the digits will create a unique number (checksum). Of course I can make index algorithm selectable with a toggle so in case it does fail you can use the golden canon instead of the golden gun . I have the routine fully functioning now , including writing the data. Only considering a minor change to the dialog design and put the fields that need to be written to the excel file   (Au , Ag & ID)  in a seperate boxed row.

[rlx]

here is the latest version so far. You can test it with example drawing & xls file posted earlier by OP .

 

 

GoldFinger.LSP

[ktbjx]

8 minutes ago, rlx said:

here is the latest version so far. You can test it with example drawing & xls file posted earlier by OP .

 

 

GoldFinger.LSP 22.56 kB · 0 downloads

Can i Borrow your Code to modify for my needs in searching lines with excel too? I'v been looking for this.

[rlx]

49 minutes ago, ktbjx said:

Can i Borrow your Code to modify for my needs in searching lines with excel too? I'v been looking for this.

 

whatever makes you happy bro

[ktbjx]

1 hour ago, rlx said:

here is the latest version so far. You can test it with example drawing & xls file posted earlier by OP .

 

 

GoldFinger.LSP 22.56 kB · 1 download

error: no function definition: SAVE_DIALOG_DATA

 

the GUI opens and close

[rlx]

6 hours ago, ktbjx said:

error: no function definition: SAVE_DIALOG_DATA

 

the GUI opens and close

 

sorry , just had a major medical emergency at my parents house (something wrong with my mother) and I am just back from hospital (situation is stable for now).

 

probably routine is missing the following

(defun Save_Dialog_Data      (%tl) (mapcar '(lambda (x) (eval (car x))) %tl))
(defun Reset_Dialog_Data (%tl %rd) (mapcar '(lambda (x y) (set (car x) y)) %tl %rd))
(defun Set_Dialog_Tiles      (%tl) (mapcar '(lambda (x / v) (if (eq 'str (type (setq v (eval (car x))))) (set_tile (cadr x) v))) %tl))

 

hadn't noticed this because I have more routines that use these functions so they are always loaded one way or the other.

 

RLX

 

Edited March 24, 2019 by rlx

[ktbjx]

14 minutes ago, rlx said:

 

sorry , just had a major medical emergency at my parents house (something wrong with my mother) and I am just back from hospital (situation is stable for now).

 

probably routine is missing the following

(defun Save_Dialog_Data      (%tl) (mapcar '(lambda (x) (eval (car x))) %tl))
(defun Reset_Dialog_Data (%tl %rd) (mapcar '(lambda (x y) (set (car x) y)) %tl %rd))
(defun Set_Dialog_Tiles      (%tl) (mapcar '(lambda (x / v) (if (eq 'str (type (setq v (eval (car x))))) (set_tile (cadr x) v))) %tl))

 

hadn't noticed this because I have more routines that use these functions so they are always loaded one way or the other.

 

RLX

 

I added the code you gave, i dives to another error lol

 

error: no function definition: VOID

 

Quote

sorry , just had a major medical emergency at my parents house

 

OMG! is she okay??! I hope she's fine forget about us for a bit, take care of her!!