how to import data from csv file
@thegreatkarthik67 wrote:
(command "_layer" "n" "_3D_poly" "")
(command "_layer" "c" "82" "_3D_poly" "")
(command "_layer" "s" "_3D_poly" "")
(command "_3DPOLY" "3835007,3178626,21298" "3835007,3178626,20200" "3835007,3185013,20200" "3817101,3185013,20200" "")
(tag "cable-1")
these 5 lines I kept for example, like this there are more than 1000 lines (basically commands).
What does the CSV file content look like thegreatkarthik6?
To be clear you are wanting to retrieve data from CSV and not the other way around?
@thegreatkarthik67 wrote:
Hi, @pbejse
Thank you.
Please note each line will be in cells ( A1, A2, A3, A4, A5 - one by one) like below,,,,
mk
Are you saying that this whole thing here -- > (command "_layer" "n" "_3D_poly" "") is in cell A1 and this (command "_layer" "c" "82" "_3D_poly" "") in cell 2?
The CSV in tab or comma delimited? or there is no CSV yet and you are looking for a a lisp program that will read data from the CSV?
And is that all there is to it? create a layer and assign a color and make current and the next is draw the 3dpolyine?
To take it literally , without changing any of your preferred command sequence. [oops]
REMOVED-- see next reply
Use layer "Make" instead of "New", that way you dont need to "set" the newly created layer, "|Make" does the same thing for you.
@thegreatkarthik67 wrote:
Hi, @pbejse thank you.
I got error ** Error: no function definition: _DELIMITER **
mk
I know mk, i wrote that code before you posted you reply and example csv. Im re-writing it now, I was to suggest a format using one row per action.
Question, its an odd way of storing data for reading, is that the final format you have or it's not set in stone yet?
Question, its an odd way of storing data for reading, is that the final format you have or it's not set in stone yet?
No, we can modify the format as want. as its excel data I will manage to get from source.
1. Lisp has to get data from csv
2. I have to create certain layer - Max 3 layers (1 for 3D polyline, 1 for Text1, 1 for Text2)
3. Drawing 3D polyline from coordinate
4. Adding tag (data) to drawn polyline
if you the lisp (original) it does above steps one by one.
You are free to modify the format.
Thank you very much
mk
@thegreatkarthik67 wrote:
Question, its an odd way of storing data for reading, is that the final format you have or it's not set in stone yet?
No, we can modify the format as want. as its excel data I will manage to get from source.
1. Lisp has to get data from csv
2. I have to create certain layer - Max 3 layers (1 for 3D polyline, 1 for Text1, 1 for Text2)
3. Drawing 3D polyline from coordinate
4. Adding tag (data) to drawn polyline
mk
So you are building the data using excel ad saving the file as CSV, ✔ .....GOT that
You mentioned on your previous post "like this there are more than 1000 lines" Is that all in one CSV file? or items 1, 2 ,3 & 4 are the one only content of any CSV? or it might be something else entirely? as per the code i posted , the contents will be evaluated literally, but we need to change the format a bit though.
Are you okay with that? Also, always in excel? or notepad and save as CSV will be enough for you? This is actually important. as i am suggesting to use TAB delimited csv file instead of comma.
thegreatkarthik67 wrote:
(command "_layer" "n" "_3D_poly" "") (command "_layer" "c" "82" "_3D_poly" "") (command "_layer" "s" "_3D_poly" "") (command "_3DPOLY" "3835007,3178626,21298" "3835007,3178626,20200" "3835007,3185013,20200" "3817101,3185013,20200" "") (tag "cable-1")
Based on the data format in post 3 of 11, vla-sendcommand is all you need. Try below. I have italicized the changes I made.
(defun c:mk (/ *error* adoc osm tag file1)
(defun *error* (msg)
(if osm
(setvar 'osmode osm))
(cond ((not msg))
((member msg '("Function cancelled" "quit / exit abort")))
((princ (strcat "\n** Error: " msg " ** "))))
(princ))
(setq osm (getvar 'osmode))
(setq adoc (vla-get-activedocument (vlax-get-acad-object)))
(setvar 'osmode 0)
(if (not (tblsearch "APPID" "Ishwinder_LineTag"))
(regapp "Ishwinder_LineTag"))
(defun tag (name)
(entmod
(append (entget (entlast))
(list (list -3 (list "Ishwinder_LineTag" '(1002 . "{") (cons 1000 name) '(1002 . "}")))))))
(setq file1 (open (getfiled "Select csv file" "" "csv" 0) "r"))
(while (setq lin (read-line file1)) (vla-sendcommand adoc lin))
(*error* nil)
(princ))
Apologies to the OP, I have to step out for a while.
My main concern is about "reading" the current format of the the attached csv file. Input in Excel and saveas .CSV will have an effect on the output from the read-line function.
"(command ""_layer"" ""n"" ""_3D_poly"" """")" is the result from the example posted at post#5. The thing i'm wondering about is the way the how the users extract the data and how it is compose in Excel. if its in the "proper" format (command "_layer" "n" "_3D_poly" "") then (eval (read ,,,)) will do the same as vla-sendcommand.
Actually these data will be extracted from different source file, hence I want to retrieve (use) the same data from csv.
...
working on excel then converting to csv....
From what i understand, the contents are basically commands to be executed on-call from the lisp program. If so, why not use scripts ( .scr ), but then again, the question keeps coming back to how the data is created.
Hope the OP will clear this up before i proceed.
pBe
@thegreatkarthik67 wrote:
Hi @pbejse,
I would like to clarify the following,
From the source file, I will get 3D poly line coordinates only then I used to add text before those coordinate "command.."3D poly... etc (using concatenate function in excel). and tag for that 3D polyline, regarding layer I will add manually i.e. one new layer for this 3D polyline which to be drawn...
I know this is long way, I was using this method only.. main thing is to sort out this....
Maybe that is why the values ended looking like that on csv format. The coordinates are probably generated from a surveying tool.
How's about leaving the coordinates as it appears on the source file and post that here. We will write a short program to read and perhaps convert the values to an acceptable format where Autocad API can understand. This would ease the burden of typing additional text on the source file.
You gave us quite a scare when you said there are more than 1000 of this lines, If it all refers to the coordinates and tag we can easily work out something to sort this out for you.
@thegreatkarthik67 wrote:
....
If still you have doubt I will upload video of already worked file... kindly let me know
...
mk
Yeah why not, Might as well.
pBe
@thegreatkarthik67 wrote:
Hi @pbejse,
....I have enclosed adding tag (i.e. cable name) to 3D polyline lisp for your reference. this lisp need to be loaded first.
thanks very much,
mk
I think i have just the right tool for this, retrieving data directly from the excel file. taking the CSV out of the play.
Pretty sure Ranjit.Singh will work out any quirks on the code at post#11 and get it up and running for you while i'm working on a different approach
Later mk
Listen, I did say forget about CSV did I?
I take that back, We are still going to use CSV but from first tab "3D_poly_working" so the concatenation will be done within the lisp code.
Reading directly from Excel takes a lot longer that i expected.
This code will read the CSV file from the attached files. I copied the values from "Final for CSV - to run LISP" tab and paste it on notepad then save the file as third tab.CSV. Keeping in mind this is after you do all this:
...From the source file, I will get 3D poly line coordinates only then I used to add text before those coordinate "command.."3D poly... etc (using concatenate function in excel). and tag for that 3D polyline, regarding layer I will add manually i.e. one new layer for this 3D polyline which to be drawn...
Run this lisp and use third Tab.CSV as source: (approximately 10 seconds)
(defun c:i3DPL ( / *error* osm tag)
(defun *error* (msg)
(if osm
(setvar 'OSMODE osm)
)
(cond ((not msg))
((member msg '("Function cancelled" "quit / exit abort")))
((princ (strcat "\n** Error: " msg " ** ")))
)
(princ)
)
(setq osm (getvar 'OSMODE))
(setvar 'OSMODE 0)
(if (not (tblsearch "APPID" "Ishwinder_LineTag"))
(regapp "Ishwinder_LineTag")
)
(defun tag (name)
(entmod (append (entget (entlast)) (list (list -3 (list "Ishwinder_LineTag" '(1002 . "{") (cons 1000 name) '(1002 . "}"))))))
)
(if (setq theCSVSoure (getfiled "Select CSV file" (getvar 'dwgprefix) "csv" 16))
(progn
(setq opencsvfile (open theCSVSoure "r"))
(while (setq RowContent (read-line opencsvfile))
(Eval (read RowContent)))
(close opencsvfile)
)
) (*error* nil)
(princ "Type i3DPL to run the lisp.")
(princ)
)
command: i3DPL
And now for the other code
I open the excel file and save the "3D_poly_working" as CSV (Comman Delimited) first tab.csv and nothing else. The program will read the
first tab.csv ignoring rows 1 to 7 and columns W onward.
Run this lisp and use first tab.csv as source: (approximately 10 seconds)
(defun c:FirstTabCSV (/ _delimiter _coordinates tag theCSVSoure allTheDataNeeded openedCSVFile a b)
(defun _delimiter (str md / d l str)
(if (listp str)
(apply 'strcat
(cons (car str)
(mapcar '(lambda (r) (strcat (chr md) r))
(cdr str)
)
)
)
(progn
(while (setq d (vl-string-position md str nil T))
(setq l (cons (substr str (+ 2 d)) l)
str (substr str 1 d)
)
)
(cons str l)
)
)
)
(defun _coordinates (lst indx)
(mapcar '(lambda (l) (distof (nth l lst)))
(list indx (1+ indx) (+ 2 indx))))
(if (not (tblsearch "APPID" "Ishwinder_LineTag"))
(regapp "Ishwinder_LineTag")
)
(defun tag (name)
(entmod (append (entget (entlast)) (list (list -3 (list "Ishwinder_LineTag" '(1002 . "{") (cons 1000 name) '(1002 . "}"))))))
)
(foreach layers '(("_End2_Text" 43)
("_Inst_Text" 33)
("_3D_poly" 82)
("_JB_Text" 54))
(if (not (tblsearch "Layer" (car layers)))
(entmake (list (cons 0 "LAYER")
(cons 100 "AcDbSymbolTableRecord")
(cons 100 "AcDbLayerTableRecord")
(cons 2 (car layers))
(cons 62 (cadr layers))
(cons 70 0))))
)
(if (setq allTheDataNeeded nil
theCSVSoure (getfiled "Select CSV file" (getvar 'dwgprefix) "csv" 16))
(progn
(setq openedCSVFile (open theCSVSoure "r"))
(while (setq a (read-line openedCSVFile))
(setq b (_delimiter a 44))
(if (vl-every '(lambda (n)
(numberp (read (nth n b)))) '(2 3 4))
(setq allTheDataNeeded (cons b allTheDataNeeded)))
)
(close openedCSVFile)
(setvar 'clayer "_3D_poly")
(foreach data allTheDataNeeded
(command "_3DPOLY")
(foreach pt (list (setq pt1 (_coordinates data 7))
(_coordinates data 10)
(_coordinates data 13)
(_coordinates data 16)(_coordinates data 19))
(command "_non" pt))
(command "")
(tag (cadr data))
(entmakex
(list (cons 0 "TEXT")
(cons 10 pt1)
(cons 40 67.5)
(cons 8 "_Inst_Text")
(cons 1 (strcat (cadr data) "_" (nth 4 data)))))
(entmakex
(list (cons 0 "TEXT")
(cons 10 (list (Car pt1)(+ 67.5 (cadr pt1)) (caddr pt1)))
(cons 40 54.0)
(cons 8 "_End2_Text")
(cons 1 (strcat (cadr data) "_" (nth 4 data)))))
)
)
)
(princ)
)
command: FirstTabCSV
We may need to modify the code later, I;m not sure about the source for TEXT at "_JB_Text" layer .
HTH
