Often surveyed data from older survey equipment is exported a CSV (Comma Separated Value) file. Whilst very useful, it is often not very user friendly as you still need to get this data into AutoCAD.
Converting the data sometimes requires other programs or knowledge on how to manipulate the data in programs such as Microsoft Excel.
In this blog article, I will be showing you how to make a simple Microsoft Excel based convertor which will allow you to do a mass copy into AutoCAD. This will save you plenty of time in the future and make moving the data around a lot more reliable.
We also have a pre built Excel to CAD convertor available for download.
Let’s get started.
To get everything working you will need:
Microsoft Excel is the leading spreadsheet provider however Google Sheets is another option if you are looking to save money. Google Sheets is free and has most of the functionality that Microsoft Excel has.
In this guide we are assuming you have a minimal knowledge of how a spreadsheet program works. If you are totally new to Excel then we recommend learning through Youtube, books or a course. Knowing Microsoft Excel is so useful for a number of different tasks and jobs and what I term to be an essential program to know how to use.
Start by opening a blank spreadsheet and make 5 columns in your first sheet. Call these columns Point ID, X, Y,Z,CAD Point
In the cell E2 (under CAD Point text) paste the following excel code directly into the formula field.
=CONCATENATE(“point “,B2,”,”,C2,”,”,D2,” -text @ “,0.1,” 0 “,A2)
Now if we put in some example data as per the following we should see some code appear.
If the code in the CAD point E2 cell is showing as per the example above then you have correctly inserted it.
You can now drag down the cell which will apply the formula to each row allowing you to have a formula for multiple PXYZ points.
We now need to get our PXYZ (CSV / TXT) file into the spreadsheet so that the PXYZ data goes into their respective columns.
To do this start by opening your CSV text file in a notepad program such as the inbuilt Windows tool or a program such as Notepad++
Once open you should check your data carefully. You must make sure that the data is split by commas and that in that the format follows:
Point ID, X , Y , Z
If your data is not in this format then either manipulate the data in excel (not covered here) or re export the data so as the data is shown in the correct manner.
At this stage remove any other lines of data that aren’t applicable.