topic Re: How to import excel data into Map 3d? in AutoCAD Map 3D Forum

How to import excel data into Map 3d?

Anonymous — Mon, 19 Aug 2019 16:05:26 GMT

Hello,

I have the coordinates of trashcans throughout the campus. How do I import the coordinates in excel, to Map 3D??

Re: How to import excel data into Map 3d?

ChicagoLooper — Mon, 19 Aug 2019 17:16:35 GMT

In order to answer that question, you need to provide two things:

What are the coordinates of the trash cans referenced to? Are they State Plane? If yes, then what City & State? Are the coordinates in lat/long degrees? If yes, then referenced to WGS84 datum? Are the coordinates in UTM meters? If yes, then what zone?
What coordinate system of the campus? Is the campus using State Plane using feet? If yes, what City and State? If not on State Plane, then what is the coordinate system you plan to use when you create the map.

Typical Example:

My trash can coordinates are in Lat/Long degrees referenced to WGS84. My map will use State Plane Coordinate System (SPCS) Texas NAD83, South Central Zone, feet or TX83-SCF EPSG 2278.

Once you provide the two items listed above, a suitable and easy work flow can be given that will accomplish your goal. If you don't know the answers, then respond with 'I don't know' and upload a sample of the coordinates (or upload all the coordinates) in a spreadsheet or txt file.

Re: How to import excel data into Map 3d?

Anonymous — Mon, 19 Aug 2019 18:13:10 GMT

Hello,
Thanks for the reply. I have attached the images so that you can have a
look at it. We use State plan Coordinate System and the *Texas NAD83, South
Central Zone, Feet.*
*City - Houston*
*State - Texas*

Re: How to import excel data into Map 3d?

ChicagoLooper — Mon, 19 Aug 2019 18:16:19 GMT

No attachments. Please upload again. Thanks.

Re: How to import excel data into Map 3d?

Anonymous — Mon, 19 Aug 2019 18:29:54 GMT

Here are the attachments,

Re: How to import excel data into Map 3d?

ChicagoLooper — Wed, 30 Mar 2022 12:06:34 GMT

There is more than one way to do this.This is the Down-N-Dirty way to do it in Map 3D. It is fast and easy and despite the point coordinates using Lat/Long which have units of DEGREES, the points will geolocate accurately in modelspace even though you have assigned Texas State Plane, South Central Zone, which have units of FEET. The reason the points can geolocate is because Map 3D has the horsepower to read, understand and transform coordinates from degrees to feet. You may do this:

Make your Excel use only five columns, like shown. To avoid confusion, keep the column headings and make Row 1 like shown, using the '#' sign as a column heading prefix (if you don't use # sign as a prefix you'll have issues because Map 3D will consider the heading to be DATA, when in fact it's just a column label). The Point Number will serve as an index for the point file, it's merely numbering each row with a 'unique identifier.' The elevation is arbitrary. Since you didn't collect elevation values, use the constant value of 0. The description is the Recycle Bin ID. These five columns represent PNEZD format. Make sure you don't flip-flop columns B and C (if you do then you'll have PENZD instead).

1-Use only 5 columns. Make the columns look like this.

When your Excel is properly formatted, do a SAVE AS using CSV (Comma delimited) (*.csv) as the file extension. Map 3D can't read .xlsx. To make your file have a descriptive name, add '_PNEZD' to the end of the file name and before '.csv.'

2-Perform a SAVE AS on the Excel file and save using the extension 'csv.'

Go to Insert Tab=>Import Panel=>Map Import Icon and browse to your csv file, and double click it, or select it and click OK. After you make your settings like shown, click OK and your points will appear in model space. Zoom extents to view points.

3-Change Files of type to ASCII point file, then browse to your csv file. Select the csv and make settings like shown.

Depending on how close together your points are, you may need to change the Scale X, Y and Z in the Properties Palette to something viewable like 250, or whatever you think looks right. Select all point then change the scale in properties palette.

If you want to hide the PTNUM and ELEV attributes, use BATTMAN command. For Block, make sure you select Map_Survey_Point from the dropdown in the Attribute Manager window. First, select ELEV and click Edit.

4-Use BATTMAN command to hide the ELEV and PTNUM attributes.

5-To hide ELEV, check INVISIBLE then OK.

Check INVISIBLE then OK. Do the same for PTNUM, select it and make it invisible. Do not make DESC invisible, if you do your recycle bins won't have an ID label. Click OK to exit.

6-Go to Geolocation Tab=>Online Map Panel and turn on Bing Hybrid for an aerial basemap.

Re: How to import excel data into Map 3d?

Anonymous — Tue, 20 Aug 2019 20:13:46 GMT

Thanks for the information. I followed all the procedures. But when I try to import the points, all the points are at the same spot. The points overlap each other. I have increased the scale to 250. What should I do to get the points in their respective position. Also I'm not able to get the aerial map.

Re: How to import excel data into Map 3d?

ChicagoLooper — Tue, 20 Aug 2019 20:21:35 GMT

They are overlapping because the scale is too large (is the scale >10,000?). Try making the scale smaller. Once you have made the points an appropriate size, turn on Bing Hybrid to verify the Recycle Bin locations.

Re: How to import excel data into Map 3d?

Estanislao_Regueiro — Wed, 30 Mar 2022 10:59:38 GMT

Just what I was looking for a long time ago and well explained.

Thanks.

Re: How to import excel data into Map 3d?

kjbutler68SMF — Tue, 05 Sep 2023 13:22:26 GMT

worked like a charm. thanks. next question, how do i get it to insert a block instead of a map survey point? i tried changing the drop down but my newly added block was not listed.

Thanks

Re: How to import excel data into Map 3d?

kjbutler68SMF — Tue, 05 Sep 2023 13:24:30 GMT

@ChicagoLooper Worked like a charm. thanks! next question, how do i get it to insert a block instead of a map survey point? i tried changing the drop down but my newly added block was not listed.

Thanks

Re: How to import excel data into Map 3d?

ChicagoLooper — Tue, 05 Sep 2023 17:27:16 GMT

Hi @kjbutler68SMF

It's hard to say without a sample of your actual data AND your block. There are too many variables from case-to-case. And too many variables from block-to-block.

Can you upload your spreadsheet? (Or a sample of your spreadsheet, around 10 rows of your excel file.)
Can you upload your block? It would be helpful to 'see' your block. It would be helpful to know whether it has attributes and to see the attributes tags, aka attributes names.