Connecting to XYZ file

[Mike.M.Carlson]

Hello-

I am looking for guidance as to the proper way to set up a ODBC connection to a simple XYZ excel file.  Once this connection is made, my hope is to do a simple meters to US Foot conversion for only the elevation data via an expression.  I am running WIN 10 64 bit.  I have downloaded the latest ODBC drivers from Microsoft  However, when I Data Connect to the .xlsx file via a System DSN for Excel files and then test the connection, nothing happens...I can't add data to the map and it doesn't show anything in the preview pane (only the headers for Class, Identity, X, Y, and Z).

Any help is greatly appreciated.

[Pointdump · Accepted solution]

Hi Mike,

Did you define your units in the X,Y, and Z columns, as in <<<This Video>>>.

Dave

[Mike.M.Carlson]

Thanks Dave!  The drop down was not intuitive for me...thanks for sharing video.  Everything is working.  Have a good night sir!

[Mike.M.Carlson]

Hey Dave-

I got it to connect, but when I look at the Data in Table View, I only see the Point ID, not the Northing, Easting, and Elevation columns?  I was hoping to add a calculation to the elevation data to convert from meters to US Foot.  X and Y are already in USFT.  Please see below:

[Pointdump]

Hi Mike,

Thanks for posting your data. I've never used an ODBC Connection, so I was eager to learn something new. But I'm stumbling at the point of defining the Data Source Name in the Windows Control Panel. Can't figure out how to do that.

I'm curious. If you have the data in spreadsheet form, why not use it directly?

Dave

[parkr4st · Accepted solution]

Mike

opened the xlsx file and inserted the z column  and not knowing for sure but multiplied your last column by 3.28084 so it at least looks like feet.

That becomes Z  in the PENZD

saved and closed

opened MS access, imported the xlsx and exported to .txt comma delimited. closed access.  (Access does it right as opposed as the crap shoot of excel)

opened the txt in notepad and grabbed the first 80 records and saved in the file in the zip.  lots of points takes awhile to process.

opened your dwg and map imported the txt to points as in the dwg in zip

mapexported the points to the sdf checking block attributes and properties x1 y1 z1

that is the sdf in the zip

avoids all the poor ODBC (One bad Data Connecting) scheme crap

If the files don't work, post back and I'll try again.

Dave P

[Pointdump]

Dave P,

"...all the poor ODBC (One bad Data Connecting) scheme crap"

Good. Then I can get on with my life after spending (wasting?) 2 hours this morning trying to figure out ODBC. I was able to stumble my way through defining a source name (don't know if I did it right), but I can't get past this dialogue box. Clicking Login does nothing:

Dave S

[parkr4st]

Dave

I gave up on obdc about the first time I tried it and it failed.  Just another of the loose ends Map3d AFAIK.

read this from Murph  https://map3d.wordpress.com/?s=excel

his last words in his article

Obvious you do not want to use 2009 forever otherwise you would not have updated, so now may be time to start looking at copying your data to SQL databases. And before users start complaining that Autodesk is not taking care of it’s long time users and leaving them out in the rain, I can ensure you that Autodesk along with other software developers are on Microsoft’s case trying to get ODBC drivers for 64 bit OS.

back in 2008/9 even the master said "  __ it"

Dave p

[O_Eckmann]

Hi,

Here is a screencast to connect XLSX via ODBC to MAP3D 2015.

Olivier

[Pointdump]

Olivier,

Wow, I wish I'd seen your ScreenCast before I wasted 2 hours this morning fumbling (Googling) around. Thank you!

Dave

[Mike.M.Carlson]

Thank you Olivier.  Thank you for preparing the screencast.  I was able to get that far too.  My question though is when you click the data table for the Lidar layer.  I only see the first "Point ID" column not the rest of the XYZ columns?  Do you see the same thing?

 

If it showed up, I can add a calculation to the Z column multiplying all the Z values by 3.2808 to convert from meters to feet.  This will save a lot of headache in Excel trying to add a formula to over 380,000 rows.

Thanks!

Mike

[Mike.M.Carlson · Accepted solution]

Hey Dave-

I needed to download the Microsoft Access Database Engine to get the drivers:

Edit**  there is updated one for 2016 (I pasted the older 2010 link):

https://www.microsoft.com/en-us/download/details.aspx?id=54920

Then you can follow the steps @O_Eckmann outlined, which worked for me. I was able to connect to the data, but once it connected, I did not get the X Y Z columns to show up in the Table View.

I was trying to bring this data in this route only to leverage the calculation functionality in the data table view once it is connected.  I would simply tag on a calculation in the Z column (multiplying 3.2808 by the Z values) to convert from Meters to feet.  This will let Civil 3D do the heavy lifting doing the calc for the 380,000 rows and saving me the effort of doing it in Excel.  This is easily done in ESRI ArcMap, but was trying to do it in map 3D...sigh.

Thanks again for attempting to help with this!

Mike

[Pointdump]

Mike,

"...headache in Excel trying to add a formula to over 380,000 rows"

Double-Click the cross instead of dragging it. That will fill down all the cells automatically.

I'll dig into your link for the Access Database Engine. Thank you.

Dave

[Mike.M.Carlson]

I also found that if you're running Microsoft Office 32bit and are running a 64 bit OS, ODBC connections get messed up.  You need to have Microsoft Office 64 bit installed and download the drivers here to get ODBC to work:

Edit**  I posted 2010 driver link by accident...there is a newer one for 2016:

https://www.microsoft.com/en-us/download/details.aspx?id=54920

[O_Eckmann · Accepted solution]

Hi,

X, Y and Z column are used to define geometry, so it doesn't appear as attribute data.

You can link column only for X and Y ans stay Z with <nothing> so Grid_code is read as attribute in the grid table

Olivier

[Pointdump]

Mike,

I went to download the MicroSquish Access Database Engine Driver, and I read this:

"Note: Before installing this download, you must first remove any previous versions of the Access Database Engine from your computer by using Add/Remove Programs in the Control Panel."

I have no idea how to find out if I have "previous versions of the Access Database Engine". Olivier, Dave P, and you are about 9 skill levels above me. (I do try like heck with my limited brainpower, but sometimes tenacity is not enough. This might be one of those times.)

I'll spend the day working on this. Maybe I can break through.

Dave

[O_Eckmann]

Hi,

here is a solution from Autodesk to install Runtime Access 64 bit when Office 32 bit is already installed.

https://knowledge.autodesk.com/support/autocad-map-3d/learn-explore/caas/screencast/Main/Details/713...

Olivier

[Pointdump]

Olivier,

"...install Runtime Access 64 bit when Office 32 bit is already installed."

I'm sure hoping my version of Office is 64 bit. It came already installed with my computer, new a month ago. This rabbit hole just keeps getting deeper and deeper. But I'm learning lots, and that's always a good thing.

Dave

[Mike.M.Carlson]

You can check by opening Excel.  Clicking File tab\Account\About Excel or watch .gif below 🙂

[Mike.M.Carlson · Accepted solution]

Thanks @olivier.eckmann!  Your last tip helped. 

Below is a screencast showing the full workflow for my situation using ODBC Excel Connection: