Create points from MS Excel

[Mikolaj.Biernacki]

I've made a shapefile with a attribute / data table for locations of boreholes. Within the data table there are columns; FeatID, ID, X, Y and NOTE.  Is it possible to lay out data in excel in the same format and import into the shapefiles so it plots the points from X and Y coordinates in the table as well as carry over the notes and IDs (which I'm hoping to set up to appear as labels for the plotted points). 

I know something similar is possible in ArcGIS through spatial join but can't figure it out in MAP 3D.

Any help would be much appreciated.  

[ChicagoLooper]

Hi @Mikolaj.Biernacki 

Yes, there is an 'easy' way, but it depends on a several factors:

• Is it possible to not include Feature ID in your Excel table? If you are planning of creating a brand new shapefile using Excel data then Map3D can create brand new FeatID's so copying a column that already contains existing FeatID's would be redundant. If creating a brand new shapefile, then it's perfectly OK to create brand new FeatID's too. A brand new shapefile does NOT need to preserve existing FeatID's since it's brand new.
• What is ID? Is it alpha? Is it numeric? Or does it contain both alpha and numeric? If it is numeric then you can do it the 'easy' way. If it contains characters then you can still do it but the 'easy' way will become much harder and take longer.
• Projected or geographic? Are your x and y coordinates projected such as meters or feet or are they geographic such as Lat/Long degrees? The easy way will allow you to use either projected or geographic coordinates when creating a shapefile. You just need to be aware of what they are.

Can you upload a small sample of your Excel data? A sample would provide a better understanding of your data and facilitate clear step-by-step instructions. 

[Mikolaj.Biernacki]

Hi, 

Thanks for your reply @ChicagoLooper 

1. FeatIDs do not have to be included and doesn't matter if they're created upon import or not. The way I was thinking about doing this was to set up a SHP. file with a predefined point symbology and label style, then just pasting or linking the data from Excel into Map 3D to create points, (these empty SHP. files would be contained in a folder which would be copied and pasted into the working /active job folder - sort of like a template) thus saving time by not having to set up the symbology and labels every time. 

2. The IDs of boreholes (and other point locations) are almost always alphanumeric - usually something such as BH01, BH02 etc. 

3. It'll be projected in metres (We use the the BNG OSGB1936 as our grid referencing system). 

I'm very new to MAP 3D so any help is / will be much appreciated. 

[ChicagoLooper]

When you create a POINT shapefile (a point feature class) you are creating a 'POINT' or a geospatial position. The point is the most rudimentary geographic object in a graphic program such as AutoCAD. It has no length and no area, absolutely NONE. It's simply a 'location.'

You can create a point feature class that defines a single point or multiple points and you may attach, or associate data, which are fields and it doesn't matter is it's alpha or numeric (the minimum is one field named FeatID), to each individual point. It's entirely possible to create a shapefile in Map3D. There are easy ways and there are hard ways. It depends on your data AND it also depends on what you want to do with that data. Oh, did I mention it depends on your data AND what you want to do with that data? 

It's up to the end user of the shapefile, not you, not anyone else, but the user himself that determines the symbology of each point. This means the user of your shapefile may symbolize your BHs with a circle and and a monitoring well (MW) with a triangle. Another user may break down those circular BHs into GREEN circles representing 'existing' BHs and ORANGE circles for 'proposed' BHs. The boreholes are still circles but they're also color coded at his discretion. 

Consider this, if you force all your BH's  to be circular symbols then another user won't be able to use green and orange to display existing and proposed samples. An end user may even go further and analyze the values in your shapefile database file (dbf) and change the symbol size. The new symbol size may be directly related to a dbf value such as PCE level. Map3D would analyze the PCE data and make large diameters to symbolize high levels, medium diameter to symbolize borderline levels and small diameter for detected levels that are non-threatening.

As you can see, the end user must have control over shapefile symbology. You, the author, only control point location and the corresponding data, whatever that data might be. The end user is entitled to Display the Data as he sees fit so it complies to the requirements of a given Report and thereby satisfy the reader.

Would you be willing to upload a sample of your spreadsheet?

[Mikolaj.Biernacki]

That's some valid points. 

Essentially what we do as a team is receive GPS coordinates of ground investigation locations. We then bring these into AutoCAD and produce very simple plans (although sometimes with tens or hundreds of locations across multiple plans) of these point features (occasionally we do plot site boundaries etc. - line features). We do not carry out any analysis or processing of these points - simply just display them. 

The only bit of processing if you can call it that would be filtering of the points based on the attributes - for example lets say we have 1000 boreholes and I only want to show boreholes with number between 250 and 300 and hide all of the remaining ones. Hiding of other exploratory locations wouldn't be a problem as the corresponding file can either be deleted or turned off. 

I understand you're point regarding not restricting the symbology of the point features. I did have to do that myself a few times where a plan had two types of boreholes with same symbol geometry but different color. However, most of the time these will remain the same hence why I suggested a copy and paste of the folder approach. If needed then additional SHP. files can be created if more unique symbols are necessary.

I've attached a zipped folder of the draft template, in there you can find the DWG project and an excel with few point locations. I still can't figure out a way to bring this data into the be ''automatically'' plotted when inserted into the data table / attribute table. 

[ChicagoLooper · Accepted solution]

If you know how to create a shapefile from your uploaded spreadsheet then the entire procedure can easily be done. You'll need to know how to use both MAPEXPORT and MAPIMPORT commands. When you bring your shapefile it will look like this.

1. Point shapefile using your spreadsheet. Your image was turned off and Bing Maps was substituted in its place.

Once you have a properly created shapefile, you may use the MAPIMPORT command import the shapefile. The MapImport command will 'convert' the shapefile points into plain vanilla AutoCAD point entities. If you know how, you can use an AutoCAD block to represent those imported entities.

The blocks in your uploaded zip file can be used BUT they are severely limited in use because they do NOT have attributes. They may look good when viewed as blocks with Mtext placed next to them but they are still non-attributed blocks.

Attributed blocks are powerful in that the attributes, when combined with MAPIMPORT, are able to CAPTURE data fields from an imported shapefile. This means TP1, TP2, TP3 through TP14 can be used to label and thereby identify each individual block. Because your blocks are non-attributed no labels or IDs can be associated with them so they'll end up as unidentifiable point locations.

Below is an image showing your block named Trial Pit with an attribute. Yes, an attribute was added to your existing block to make it more versatile. When the shapefile in image 1 above is imported using MAPIMPORT command, the command itself will insert each attributed block in its proper location and simultaneously label each block using the data from your spreadsheet.

2. MapImport command used to import the shapefile shown in image 1 above. You may use a block to represent each sample point.

 To perform the above you'll need to perform the following (and it helps to understand each procedure):

1. Create an attributed block. (Or add attributes to your existing blocks.)
2. Use MAPIMPORT command to add points with a properly formatted csv file to your drawing. The MapImport command will result in Map3D inserting MapSurveyPoints (a default block used by MapImport command) at your point locations.
3. Use MAPEXPORT command to export the newly inserted MapSurveyPoints to shapefile. You have just made a shapefile which you can share with your GIS colleagues. You're DONE.

OPTIONAL:

• You may continue the procedure and use MAPIMPORT again but this time import a shapefile instead of importing a csv file.
• When you MapImport a shapefile you can use your own blocks to represent each point.
• If you have a properly attributed block then you may label each block with its correct description. 

[Mikolaj.Biernacki]

Hi, 

Thanks for your in-depth explanation, its more clear now. However I still have some questions. 

1. Do you know of a tutorial how to create a SHP file from a spreadsheet? I can't find one which would be suitable for a

situation like this one. 

2. When you say that the blocks would have to be attributed in order to the labeling to work, do you mean that I would have to create a block for with an attribute for each one of these locations? As this is something I'm trying to avoid, and go the opposite way in order to automate the process.