Importing coordinates into AutoCAD: place the points or join them up

The survey team sends back a spreadsheet of two hundred points. They have to go onto the drawing to set out a boundary, a road centreline or a run of markers. Typing four hundred figures by hand costs a morning and is certain to go wrong somewhere.

Importing coordinates into AutoCAD is something the program cannot do: it does not read spreadsheets. This article publishes the Institute's lisp file for the job, with the source printed in full, and sets out the most dangerous failure of all — reading the file wrongly and saying nothing. For the whole workflow from survey data to drawing see our AutoCAD for Construction course.

Three routes from a csv file, solving three different problems

The first two produce no coordinates at all. An OLE table looks like a table but it is a picture: nothing to snap to, nothing to measure, nothing to draw from. Anyone setting out a boundary who starts there has taken a wrong turn at the first step.

The second route is worth knowing for what it really does. DATALINK creates a genuine AutoCAD table held to a spreadsheet by a data link; edit the file, update the link, and the table follows. Autodesk's page states: "A table can be linked to data in a Microsoft Excel (XLS, XLSX, or CSV) file." Right for a schedule, quite wrong for coordinates — a linked table is still a table, not geometry.

This article is about the third route. The reverse job, getting coordinates out of a drawing, is covered in our article on exporting point coordinates.

Download the Institute's file

The format is a plain text file, one point per line, either id,X,Y or just X,Y. From Excel: File → Save As → CSV. The file linked above prompts in English; a Vietnamese version is also published with the same command names.

;;; ==================================================================
;;;  NTD-IIC-EN.LSP  -  read coordinates from a CSV file into a drawing
;;;  Institute of Information Technology in Civil Engineering (IIC)
;;;  Hanoi University of Civil Engineering
;;;  https://iic.huce.edu.vn  -  Tel: 0989 427 809
;;; ------------------------------------------------------------------
;;;  Three commands:
;;;    NTD     read the file, place points and label each one
;;;    NTDP    read the file and join the points into a polyline
;;;    NTDK    read the file and only PRINT it, drawing nothing
;;;
;;;  Why this file is needed: AutoCAD cannot read a spreadsheet. The one
;;;  thing it offers is pasting an Excel range as an OLE object - which
;;;  looks like a table but is NOT coordinates: nothing to snap to,
;;;  nothing to measure. Getting 200 surveyed points out of a spreadsheet
;;;  otherwise means typing every pair by hand.
;;;
;;;  FILE FORMAT: a plain text file, one point per line.
;;;       id,X,Y            or          X,Y
;;;  From Excel: File > Save As > CSV (Comma delimited).
;;;
;;;  FIVE THINGS TO KNOW BEFORE RUNNING:
;;;    1. AXIS CONVENTION. Surveying calls the NORTHING X and the EASTING
;;;       Y, while a drawing calls the horizontal axis X. The command ASKS
;;;       rather than guessing. Getting it wrong turns the drawing 90
;;;       degrees.
;;;    2. THE DECIMAL MARK. A spreadsheet in a comma-decimal locale writes
;;;       figures as "2000,5". The command reads those too: a figure that
;;;       will not parse and holds a comma is retried with a full stop,
;;;       AND the number of figures converted is REPORTED at the end.
;;;       Nothing is changed quietly.
;;;    3. THE FIRST LINE is usually a column header. Any line that does
;;;       not parse as numbers is skipped, so the header need not be
;;;       deleted beforehand.
;;;    4. THE COLUMN SEPARATOR is chosen PER LINE: a line holding a
;;;       semicolon is split on semicolons, otherwise on commas. Splitting
;;;       on both at once is wrong: the line "1;2000,5;1000,5" would break
;;;       into five pieces and the command would take (2000, 5) as the
;;;       coordinate - this was measured during testing, and fixed.
;;;    5. The points created are POINT objects. To see them, set a point
;;;       style with DDPTYPE: the default style is a single pixel and is
;;;       all but invisible.
;;;  After running, the command PRINTS how many points were read and how
;;;  many lines were skipped.
;;;
;;;  Command names were checked before being chosen: the acad.pgp alias
;;;  file of the 2027 release has no NTD, NTDP or NTDK.
;;;
;;;  To load: type APPLOAD, pick this file, click Load.
;;;  Saved as UTF-8. If you edit it in Notepad, save it back as UTF-8.
;;; ==================================================================

(vl-load-com)

;;; ------------------------------------------------------------------
;;;  Split a line into a list of strings.
;;;
;;;  The separator is chosen PER LINE: a semicolon if the line holds one,
;;;  otherwise a comma. Never both at once - see note 4 above.
;;; ------------------------------------------------------------------
(defun iic-split (s / lst o c i sep)
  (setq sep (if (vl-string-search ";" s) ";" ","))
  (setq lst nil o "" i 1)
  (while (<= i (strlen s))
    (setq c (substr s i 1))
    (if (= c sep)
      (progn (setq lst (cons o lst)) (setq o ""))
      (setq o (strcat o c))
    )
    (setq i (1+ i))
  )
  (reverse (cons o lst))
)

;;; ------------------------------------------------------------------
;;;  Turn a string into a number, or nil when it is not one.
;;;
;;;  If it will not parse and holds a comma, try once more with the comma
;;;  replaced by a full stop - that is a spreadsheet from a comma-decimal
;;;  locale. Every such conversion is COUNTED and REPORTED at the end.
;;; ------------------------------------------------------------------
(defun iic-num (s / v)
  (if s
    (progn
      (setq s (vl-string-trim " \t\r\n\"" s))
      (cond
        ((= s "") nil)
        ((setq v (distof s 2)) v)
        ((vl-string-search "," s)
         (setq v (distof (vl-string-translate "," "." s) 2))
         (if v (progn (setq *IIC-COMMA* (1+ (if *IIC-COMMA* *IIC-COMMA* 0))) v))
        )
        (T nil)
      )
    )
  )
)

;;; ------------------------------------------------------------------
;;;  Read the file, returning (id x y) in DRAWING axes.
;;;  cv = "S" (surveying: the column after the id is the northing)
;;;       "D" (drawing:   the column after the id is the horizontal X)
;;;  Returns (list . number_of_skipped_lines)
;;; ------------------------------------------------------------------
(defun iic-read (file cv / f line o a b lst skip auto)
  (setq f (open file "r"))
  (if (null f)
    nil
    (progn
      (setq lst nil skip 0 auto 0 *IIC-COMMA* 0)
      (while (setq line (read-line f))
        (setq o (iic-split line))
        (cond
          ;; three columns or more: id , number , number
          ;; Parse each cell ONCE, inside the test. Calling iic-num twice
          ;; on the same cell would double the "commas converted" count and
          ;; the figure reported to the user would be wrong.
          ((and (>= (length o) 3)
                (setq a (iic-num (nth 1 o)))
                (setq b (iic-num (nth 2 o))))
           (setq lst (cons (list (vl-string-trim " \t\r\n\"" (nth 0 o))
                                 (if (= cv "S") b a)
                                 (if (= cv "S") a b)) lst))
          )
          ;; two columns: number , number
          ((and (= (length o) 2)
                (setq a (iic-num (nth 0 o)))
                (setq b (iic-num (nth 1 o))))
           (setq auto (1+ auto))
           (setq lst (cons (list (itoa auto)
                                 (if (= cv "S") b a)
                                 (if (= cv "S") a b)) lst))
          )
          (T (setq skip (1+ skip)))
        )
      )
      (close f)
      (cons (reverse lst) skip)
    )
  )
)

;;; ask for the file and the axis convention
(defun iic-ask (/ file cv r)
  (setq file (getfiled "Pick a coordinate file (CSV or TXT)" "" "csv;txt" 4))
  (if (null file)
    (progn (princ "\nNo file picked.") nil)
    (progn
      (initget "Surveying Drawing")
      (setq cv (getkword "\nAxis convention [Surveying/Drawing] <Surveying>: "))
      (if (null cv) (setq cv "Surveying"))
      (setq r (iic-read file (if (= cv "Surveying") "S" "D")))
      (if (null r)
        (progn (princ "\nCould not open the file.") nil)
        (progn
          (princ (strcat "\n  Points read   : " (itoa (length (car r)))))
          (princ (strcat "\n  Lines skipped : " (itoa (cdr r))))
          (if (> (cdr r) 0)
            (princ "\n  (a header line, a blank line, or a line that is not numeric)")
          )
          (if (and *IIC-COMMA* (> *IIC-COMMA* 0))
            (princ (strcat "\n  Converted a comma to a full stop in "
                           (itoa *IIC-COMMA*)
                           " figure(s) (a comma-decimal spreadsheet)."))
          )
          r
        )
      )
    )
  )
)

;;; ------------------------------------------------------------------
;;;  NTD - place points and label them
;;; ------------------------------------------------------------------
(defun c:NTD (/ r lst h o)
  (setq r (iic-ask))
  (if r
    (progn
      (setq lst (car r))
      (setq h (getvar "TEXTSIZE"))
      (if (or (null h) (<= h 0)) (setq h 2.5))
      (foreach o lst
        (entmake (list (cons 0 "POINT") (cons 10 (list (cadr o) (caddr o) 0.0))))
        (entmake (list (cons 0 "TEXT")
                       (cons 10 (list (+ (cadr o) (* h 0.5))
                                      (+ (caddr o) (* h 0.5)) 0.0))
                       (cons 40 h)
                       (cons 1 (car o))))
      )
      (princ (strcat "\n  Placed " (itoa (length lst)) " labelled point(s)."))
      (princ "\n  Seeing nothing? Run DDPTYPE and pick a visible point style.")
    )
  )
  (princ)
)

;;; ------------------------------------------------------------------
;;;  NTDP - join the points into a polyline
;;; ------------------------------------------------------------------
(defun c:NTDP (/ r lst shut dat o)
  (setq r (iic-ask))
  (if r
    (progn
      (setq lst (car r))
      (if (< (length lst) 2)
        (princ "\n  At least two points are needed.")
        (progn
          (initget "Yes No")
          (setq shut (getkword "\nClose the polyline [Yes/No] <No>: "))
          (setq dat (list (cons 0 "LWPOLYLINE")
                          (cons 100 "AcDbEntity")
                          (cons 100 "AcDbPolyline")
                          (cons 90 (length lst))
                          (cons 70 (if (= shut "Yes") 1 0))))
          (foreach o lst
            (setq dat (append dat (list (cons 10 (list (cadr o) (caddr o))))))
          )
          (entmake dat)
          (princ (strcat "\n  Joined " (itoa (length lst)) " point(s) into one polyline."))
        )
      )
    )
  )
  (princ)
)

;;; ------------------------------------------------------------------
;;;  NTDK - read and print only, drawing nothing
;;; ------------------------------------------------------------------
(defun c:NTDK (/ r lst o n)
  (setq r (iic-ask))
  (if r
    (progn
      (setq lst (car r) n 0)
      (princ "\n")
      (princ "\n  Id             X (drawing)      Y (drawing)")
      (princ "\n  ---------------------------------------------")
      (foreach o lst
        (setq n (1+ n))
        (if (<= n 20)
          (princ (strcat "\n  " (car o) "\t" (rtos (cadr o) 2 3)
                         "\t" (rtos (caddr o) 2 3)))
        )
      )
      (if (> n 20) (princ (strcat "\n  ... and " (itoa (- n 20)) " more")))
      (princ "\n")
    )
  )
  (princ)
)

(princ "\nNTD-IIC-EN loaded. Commands: NTD (points), NTDP (polyline), NTDK (read only).")
(princ)

The axis convention: what turns a drawing 90 degrees

This has to be settled before anything is drawn, and it is why the command asks rather than guessing.

Surveying practice calls the northing X and the easting Y. A drawing calls the horizontal axis X and the vertical axis Y. So the column headed X in the survey team's table has to go into the drawing's Y.

Plenty of lisp files in circulation swap the axes silently. That is right for a cadastral plan and wrong for a table already written in drawing axes, and nothing warns you either way.

The command here asks [Surveying/Drawing] straight after the file is picked. Surveying is the default because it is the commoner case, and one wrong Enter is still undone with UNDO.

Checking surveyed points with a closed shape

The surest way to know whether a file was read correctly is to join the points up and measure the shape.

The test file holds six lines: one header, four coordinate lines, and one deliberately broken line. In surveying axes those four points must give a rectangle of 1000 × 800.

An exact match. And the four vertices landed exactly where they should: (1000, 2000), (2000, 2000), (2000, 2800), (1000, 2800) — so the axis swap did its job.

The header line is skipped automatically, so there is no need to delete it first.

A closed shape is the right test precisely because it checks two things at once. The point count alone says nothing about whether the coordinates landed where they belonged; a perimeter and an area only come out right when every vertex is correct and in the right order. On a real job, taking one known boundary from the survey and checking its area against the figure on the survey report takes a minute and settles the whole import.

The decimal mark: comma-decimal spreadsheets

This breaks more imports than anything else, and it is where this file differs from those in circulation.

A spreadsheet in a comma-decimal locale — most of continental Europe, Vietnam, much of Latin America — exports CSV like this: columns separated by semicolons, decimals written with commas. A line reads 1;2000,5;1000,5.

The Institute's file reads that correctly:

What matters is that it reports how many figures it converted rather than fixing them quietly. If you believe your file uses full stops and the command says it converted forty figures, something needs looking at.

The worst trap is not a skipped line but a silent misread

While this file was being tested, an earlier draft split columns on commas and semicolons at once. That sounds convenient, but given 1;2000,5;1000,5 it broke the line into five pieces: 1, 2000, 5, 1000, 5. The command took the first two as a coordinate and placed the point at (2000, 5) instead of (1000.5, 2000.5).

The point landed nearly two thousand units from where it belonged, with no warning of any kind. Looking only at the number of points read, everything appeared perfect.

The released file fixes it: the column separator is chosen per line — a line holding a semicolon is split on semicolons, otherwise on commas.

The general lesson for any lisp that reads data: a skipped line can be noticed, a silent misread cannot. The only self-check worth trusting is to join the points up and compare a perimeter or an area against a figure you already know. Measuring is covered in our article on measuring areas in AutoCAD.

How to run it, step by step

Importing coordinates into AutoCAD takes seconds once the file is right, so the checking step is where the effort belongs. Step three is worth making a habit. NTDK draws nothing at all: it prints the coordinate list and the two summary figures. Reading those before drawing catches a format problem immediately, rather than after two hundred points have landed in the wrong places.

If the points are placed and nothing appears on screen, nothing is wrong: AutoCAD's default point style is a single pixel and is all but invisible. Run DDPTYPE and pick a visible style — covered in our article on point styles in AutoCAD.

Five things people say that are not true

Frequently asked questions

What format should the spreadsheet be saved in?

CSV: File → Save As → CSV. A plain .txt file with one point per line works too.

How should the columns be arranged?

id,X,Y, or just X,Y. Without an id column the command numbers the points 1, 2, 3.

Are my coordinates in surveying or drawing axes?

A table from a survey team is almost always surveying, with the northing as X. Choose Surveying at the second prompt.

What if my file uses commas for decimals?

It is read correctly. The command retries with a full stop and reports how many figures it converted.

The points are placed but I cannot see them.

The default point style is all but invisible. Run DDPTYPE and choose another.

How do I know the import was correct?

Run NTDK first to read without drawing. Or join the points into a closed polyline and compare the perimeter and area with figures you already know.