How To Convert PXYZ Survey Data Into CAD Using Excel
We Show You How To Build A Tool In Excel To Add PXYZ Survey Into AutoCAD.
Article By: Tom Ayre
Last Update: May 2020
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.
What You Will Need
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.
Setting Up Your Spreadsheet
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.
Delete any existing data in the Point ID / X / Y / Z columns in your spreadsheet. After this, highlight all of your notepad data and copy / paste into the cell A2 in the spreadsheet you just built. This should load the data into rows but remain only in the A column.
We need to split the data so as the remaining columns are correctly populated.
To do this in Excel you need to highlight all of your data in Column A and then go to the “Data” Tab and then select the tool “Text to Columns”.
This will open up the following dialogue box.
Go through and select the following options:
Step 1 / 3 Window
- Select Delimited
- Go to next.
Step 2/3 Window
- In the delimiters box de select all and tick the comma tickbox.
- You should then see the preview box update and your columns move to their correct positions.
- Got to next.
Step 3/3 Window
- Simply select finish, don’t worry about any other settings.
- If it mentions an error about overwriting data then just accept it.
You should now see something like this in your own spreadsheet. The data is now in the correct columns and your formulas should still exist in the CAD Point column.
Continue by dragging down your formulas so as they cover to the last occupied row. In the example above I will drag them down to row number 11.
Highlight your CAD points in the CAD point column as per the image below. Once highlighted right click and select copy.
Open up AutoCAD and click in your command bar at the bottom of the page. Right click here and select paste. This should then automatically add your points to your CAD model along with the label (Point ID).
If you need to change the size of your text then refer back a couple of steps and replace the formula code “0.1” with what ever size you need!
Download Excel to CAD PXYZ Tool
This quick, 1 minute 20 second video will demonstrate the simplicity and ease of use for this tool.