Make a Query

[Luís Augusto]

Hi guys.

I wonder if there is some function in autolisp that works similar to an SQL query selection.

 

I need to make a query to a file with more or less 500 products. I will pass as parameter search the part number and wish the return of your description.

 

Example table:

First column belongs to Part Numbers

Second column belongs Descriptions

 

Using SQL maybe I might do:

"SELECT [Description] FROM tablesample WHERE ((([PartNumber]) =" "PN1234" "));"

 

Another question.

What is the best file type for better performance?

I've been reading something about the function "read file", this is the best method?

 

Thank you.

[MSasu]

What is that file that you look to interogate? Is an ASCII file? Or is a DWG, case when will be useful to specify what the "products" are (entity type).

[Luís Augusto]

First, thanks for the reply.

Sorry I was not clear in my question.

When I refer to what file to use, I'm looking for answers about the best method.

Until now, I could see that it is possible to read files (. Txt or. Csv) by the function "read file", but for me to do what I need, I have to write my own function.

Is there a more direct way?

[Lee Mac]

Here is an SQLite for AutoLISP utility which may be useful:

 

http://www.theswamp.org/index.php?topic=28286.0

 

[ requires Swamp membership to view ]

[BIGAL]

To search a simple text file ec with multiple columns is not that hard there is various ways to do it. The important part is how is the information seperated, is it by spaces or comma or one I did purely by vertical columns.

 

The routines are freely available what you need to do is post a small sample of the data files.

[Luís Augusto]

Here is an SQLite for AutoLISP utility which may be useful:

 

http://www.theswamp.org/index.php?topic=28286.0

 

[ requires Swamp membership to view ]

 

Many thanks for the reply Lee.

Good to know that there is that possibility, but I think it is a very long step for me now. I marked the page as favorite for when I'm a little more evolved, attempting a connection.

 

In the meantime, I read a bit more about the "read file" function and I believe I need to be enough.

I'll try to find some code using the read function that is able to meet my need. I will use a file .csv as a database.

 

In my research, I stumbled to your code ReadCSV-V1-3.

I found wonderful, but still do not know to extract information from lists to mimic a query. The program differentiate the decimal separator, was the icing on the cake, perfect. In my country we use the ";" semicolon.

 

Any help in this direction is welcome.

 

Thank you very much.

[BIGAL]

Still post a sample file !

 

Once you make a list of each line using say Lee's program then its a case of just comparing the returned answer with the search value.

 

A B C

A F R

 

If the 1st value is "A" use the line

If the 2nd value is "F" use the line

[pBe]

This looks interesting.

 

Maybe a dialog box for data entry?

[Luís Augusto]

Still post a sample file !

 

Once you make a list of each line using say Lee's program then its a case of just comparing the returned answer with the search value.

 

Thanks for proposing to teach BIGAL.

 

I'm needing to do this kind of queries often. Put attached an example file.

I would like to pass as parameter, a variable whose value is your primary key, then set another variable with the value of the part description.

 

Thanks in advance.

 

This is the list that provides the function of Lee.

 

Sample.csv

 

(
   ("PRIMARY_KEY" "PART_DESCRIPTION")
   ("AM0.35" "DESCRIPTION AM0.35")
   ("AZ0.35" "DESCRIPTION AZ0.35")
   ("BR0.35" "DESCRIPTION BR0.35")
   ("CZ0.35" "DESCRIPTION CZ0.35")
   ("LR0.35" "DESCRIPTION LR0.35")
   ("MR0.35" "DESCRIPTION MR0.35")
   ("PR0.35" "DESCRIPTION PR0.35")
   ("RS0.35" "DESCRIPTION RS0.35")
   ("RX0.35" "DESCRIPTION RX0.35")
   ("VD0.35" "DESCRIPTION VD0.35")
)

[Luís Augusto]

This looks interesting.

 

Maybe a dialog box for data entry?

 

Sorry not to have answered before PBE.

In fact, the value being delivered to query this moment, I would have been informed before. Let me explain better.

The primary key in this case would be two concatenated strings. These strings are informed by a initget so that the user has no chance of being wrong in a Typing.

Last result in the list above have VD0.35, but is actually "VD" "0.35".

[Luís Augusto]

( tutorial ) Reading a file using AutoLISP by Mark

« on: January 22, 2008, 04:25:41 pm »

 

http://www.theswamp.org/index.php?topic=21047.0

 

[ requires Swamp membership to view ]

[Luís Augusto]

Hello guys, I need help with this issue.

See the code below is working but I am not satisfied.

 

(defun c:query ()
 (setq fo (open "[color="red"]C:\\Users\\Augusto\\Desktop\\Sample.csv[/color]" "r"))
 (setq cod (strcase (getstring t "\nEnter code: "))) ;"AM0.35" or "CZ0.35" ....
 (while (setq aline (read-line fo))
   (if	(wcmatch aline (strcat "*" cod))
       (setq cod (substr aline (+ 2 (strlen cod))))
); if
  ); while
 (alert (strcat "Found a match: " cod "\n"))
 (princ)

 (close fo)
)

To test: Sample.csv

 

I would like to learn how to create a function that did not need to stay accessing the file all the time.

I'm days trying to find a way to retrieve a list inside another list, whose part of its value is known.

 

The "ReadCSV-V1-3" code written by Lee Mac, delivery to the variable "data", a list with the values ​​from the file, but can not find a way to use it.

 

My idea is so. At the beginning of the code I read the external file and store in a variable named "table". This variable will not be cleared when you exit the program, since it is a new execution of the code, I check if it is clean or not. That way no need to be constantly accessing the file.

 

I'm very interested in learning this subject.

Thank you very much.

[BIGAL]

I would not use variable "table" may get a reserved word problem, any way answer to your request.

 

(if (= partslist nil)
(progn
(setq fo (open "C:\\Users\\Augusto\\Desktop\\Sample.csv" "r"))
(while (setq partslist (cons (read-line fo) partslist)))
)
(close fo)
(alert "partslist now available use nth to retrieve")
)
)

[Luís Augusto]

It is this part that I'm having trouble.

The function nth (nth) as far as I understood it, need to know what position you are on the list.

Given this difficulty I advanced my studies for vl-functions.

I found the vl-member-if function.

I thought of using the vl-member-if to bring the list to the first position and following the nth function (nth) 0? Is it going to work?

 

Or have another solution?

 

Thank you.

[hmsilva]

Hi Luís,

if I understood correctly, perhaps something like this...

Requires Lee Mac's LM:readcsv routine.

(defun c:test (/ ANS FILE STR)
 (if (not *mylist*)
   (if (setq file "C:\\Users\\Augusto\\Desktop\\Sample.csv")
     (setq *mylist* (LM:readcsv file))
     (princ "\nSample.csv was not found!")
   )
 )
 (cond (*mylist*
 (if (setq str (getstring "\nEnter code: "))
   (progn
     (foreach x *mylist*
       (if (wcmatch (car x) str)
  (setq ans (cadr x))
       )
     )
     (if ans
       (alert (strcat "Found a match: \n" ans "\n"))
       (alert "\nMatch not faund!!")
     )
   )
 )
)
 )
 (princ)
)

 

If you have any questions, PM me in Portugês...

HTH

Henrique

[Luís Augusto]

Perfect!

 

Thank you Henrique, you perfectly understood my need.

Your code is very easy to understand. Thank you for sharing your knowledge with me.

 

Best Regards, Luís Augusto.

[hmsilva]

You're welcome, Luís Augusto

Glad I could help

 

 

Henrique

[Lee Mac]

Hi Luís,

if I understood correctly, perhaps something like this...

Requires Lee Mac's LM:readcsv routine.

 

Good implementation Henrique -

 

I have a few minor suggestions, if you don't mind [the following is untested]:

(defun c:test ( / ans str )
   (if (or *mylist* (setq *mylist* (LM:readcsv "C:\\Users\\Augusto\\Desktop\\Sample.csv")))
       (if (/= "" (setq str (getstring t "\nEnter code <exit>: ")))
           (if (setq ans (vl-member-if '(lambda ( x ) (wcmatch (car x) str)) *mylist*))
               (alert (strcat "Found a match:\n" (car ans)))
               (alert "Match not found.")
           )
       )
       (princ "\nSample.csv not found.")
   )
   (princ)
)

Since:

 

(setq file "C:\\Users\\Augusto\\Desktop\\Sample.csv")

 

and

(setq str (getstring "\nEnter code: "))

 

will always return non-nil values, and vl-member-if seems more appropriate for this task.

 

Lee

[hmsilva]

Good implementation Henrique -

 

...

Thank you, Lee

...

I have a few minor suggestions, if you don't mind...

Nice modification!

 

Henrique

[Luís Augusto]

Thank all for contributing to my learning.

 

Regards,

Luis Augusto