Use read-line to extract comma separated values

[ynotrobits]

Hello all,

 

I've recently come back to AutoCad after a few years absence and I'm surprised at how Lisp I've forgotten. I used to know how to do this:

 

I can't remember how to extract the comma separated values from a text string. If I use the following read-line formula:

 

(setq ReadText (open "C:\\MyFolder\\MyText" "r"))

 

(setq UseText(read-line ReadText))

 

(Close ReadText)

 

Which extracts the following text:

 

 

1,25.50,32.00,MyRoomName,PL1

2,25.50,46.00,MyRoomName,PL1

3,25.50,72.00,MyRoomName,PL12

 

What I can't remember how to do is isolate and assign the comma separated values to new variables.

 

As an FYI, in this particular case the value order is as follows from left to right:

 

1-----------------Object number (Object is always a rectangle)

25.50-------------Object width

32.00-------------Object Length

MyRoomName------Room name in which object is located

L1----------------Object material code

 

In other words, how do I break down the UseText variable into its component parts and then generate the five independent variables that I need to create additional formulas?

 

Thanks for looking

 

AJ

[Tharwat]

Welcome to forum .

 

E.g.

 

(setq a "1,25.50,32.00,MyRoomName,PL1")
(while
 (if (setq n (vl-string-search "," a 0))
   (setq a (vl-string-subst " " "," a))
 )
)

 

Result ...

 

"1 25.50 32.00 MyRoomName PL1"

[Lee Mac]

Consider the following functions:

 

http://lee-mac.com/stringtolist.html

 

http://lee-mac.com/readcsv.html

[ynotrobits]

-----Thank you

[jmerch]

Welcome to forum .

 

E.g.

 

(setq a "1,25.50,32.00,MyRoomName,PL1")
(while
 (if (setq n (vl-string-search "," a 0))
   (setq a (vl-string-subst " " "," a))
 )
)

 

Result ...

 

"1 25.50 32.00 MyRoomName PL1"

 

Tharwat, I am having a similar issue but am not sure where to insert your code. Once my file is open, here is my code:

(read-line f)
(while (setq row (read-line f))
   (mapfilter (strcat "#5113 = " row) sset)
   (executescript **SCRIPT HERE**)
   (princ)
)
(close f)

 

the "mapfilter" is a third party command. I need it to read my excel sheet which will have 2 columns. I just need it to read the first column and for each value, do the third party commands.

 

I can change the first 'while' to (while (setq row (list (read-line f))) but then I do not know where to remove the comma's in order for the 'mapfilter' to grab the data it needs and continue in the while loop.

 

TIA

[BIGAL]

Jmerch go back and read the posts by Lee & Tharwat they have both provided the solutions to the questions. If you use the to list option Readcsv.lsp then its easy to get the 1st value its just (nth 0 lst) this would be column 1.

[Tharwat]

Jmerch .

 

I don't know what that third party command do , but to separate a string as shown in my previous example and after reading an object file .

 

(setq f (open f "r"))
(while (setq str (read-line f))
 (while (if (setq n (vl-string-search "," str 0))
          (setq str (vl-string-subst " " "," str))
        )
 )
)
(close f)

 

And to get only the first string before a comma .

 

(setq f (open f "r"))
(while (setq str (read-line f))
 (if (setq p (vl-string-search "," str 0))
   (setq str (substr str 1 p))
 )
)
(close f)

 

Let me know if that is what you're after .

[jmerch]

@BIGAL, I don't see anything referencing (nth 0 lst) in this post. I am looking into it though.

 

@Tharwat, When I enter your second code, it partially works. In order to test, here's how I modified it (using alert to show me what data it's reading from the csv file)

 

(while (setq str (read-line f))
   (if (setq p (vl-string-search "," str 0))
       (setq str (substr str 1 p))
   )
   (alert str)
   (princ)
)
(close f)

 

This works and shows me each line it's reading. However, when I substitute my original code with mapfilter it does not work this way. Basically the data I'm reading from the csv is similar to an attribute and 'mapfilter' is like the ACAD filter. I'm wanting this to look at each cell in the first column and filter out any items with that attribute, if it's present, run a script (another third party command). Continue doing this until the list is done. So, when I have the 'alert' in the code, it's reading each line in the csv. So then I substitute that part with the 'mapfilter' and use strcat to compile what it filters for. But it doesn't work that way.

[Tharwat]

I can not tell anything about your functions or third party commands since that I have no idea about how it works and what does these commands do for you .

[jmerch]

I understand that. Think of it as a filter. If the data I was reading from the csv was a layer name, and using the 'alert' test goes through each layer listed in the csv file, why wouldn't filtering each layer work? It's not the third party functions that are holding it up, this works fine if my csv file just had 1 column of data and I didn't do the string-search. That's what I'm trying to figure out and didn't know if you see something I misplaced?

 

Thanks!

[Tharwat]

Just show an example of what you are after and then to upload aa example of your csv file .

 

How would the return value of read-line function is it reads tow columns and more ? I mean what is the separator between strings ? is it comma or semicolon ?

[jmerch]

Attached is a sample csv file. The current separator is a comma. I tried the (nth 0 list) method with no progress either. I changed the date format thinking the slashes had something to do with it, but that's not the case either.

 

The entire code is essentially what I've posted minus some typical error traps.

 

Test.csv

[Tharwat]

Okay , now forget about the two third parties and tell me what you are trying to do with the contents of the excel file ?

 

I may need to write a function to do instead of these fore-said functions ( if I could ) .

[jmerch]

I understand. I'm trying to read the data in the first column (aside from the header). The closest comparison I can suggest is a filter. I need to filter the drawing for an attribute that is equal to the data in that cell, and so on.

 

My LISP works fine If the csv file just had the one column of data, no problems.

[Tharwat]

This is more than enough I guess .

 

(while (setq str (read-line f))
   (if (setq p (vl-string-search "," str 0))
       (cond ((eq (setq str (substr str 1 p)) "test1") (Do something ......))
             ((eq str "test2") (Do somthing ......))
             )
     )
)

[jmerch]

So you're putting conditions in there based on actual text values, but it needs to vary to whatever str is. Does that make sense? For each value in the csv file, the LISP should filter it, then do something with it. Then move to the next value, same thing, and so on. Am I complicating it or am I not understanding what you're doing?

[Tharwat]

I am walking step by step with you cause I do not know why you keep your aim of the routine hidden , but I am still standing

 

If you have so many string to check with the one from the excel file , you can use member function to check if a string is a member in that list . What do you think ?

[jmerch]

I apologize if my intent seems hidden, it's due to the third party commands. Without you knowing them, it's pointless to explain them which is why I've been comparing them to basic CAD commands, such as filter.

 

The data in the csv file will change constantly. This LISP is to look at the file, and for each value in the cell of the first column it needs to search the drawing (filter) and when the filter has those objects selected, a script will be ran on them. Then the next value down, same thing, and so on until no more values in excel. I don't need it to search the drawing first to see if the value is present, I know it will be present.

 

The LISP worked fine when it was just a single column csv, but we had to add the second column so my issue is just trying to weed out the first column values and continue on.

 

Did I make it muddier?

[Tharwat]

 

The data in the csv file will change constantly. This LISP is to look at the file, and for each value in the cell of the first column it needs to search the drawing (filter) and when the filter has those objects selected, a script will be ran on them.

 

You can make a list of the strings from the Excel file the after that you can check for every string in list .

 

The values you put in the excel file are completely different than what you are after , right ? anyway that is your way and this is what I can offer up to point that I reached your aim of the codes .

[jmerch]

You can make a list of the strings from the Excel file the after that you can check for every string in list .

 

Initially that's what I was trying to do...(read-file f) will return "1,6/21/2014" for example. I just need the LISP to see the "1". I initially tried removing the comma's per your original code on this post to create a list, then was going to try (car list). Then I saw you cleaned it up a bit and thought I could do that skipping the removing of the comma and just telling LISP to look for the first value. That's where we're stuck.

 

The values you put in the excel file are completely different than what you are after , right ? anyway that is your way and this is what I can offer up to point that I reached your aim of the codes .

 

The values in excel are what I need to store and use later in the LISP, doing it for each line in the excel file.

 

Thanks for your efforts.