Bring a CSV file into MAP 3D with data.

Have you ever received a text file of points with the locations and other data that goes with those points? Say you receive a GPS file of some hydrants that list the location as lat/lon and data about the hydrants. But the file is in a text format instead of a shp or dxf format. You can use the “Multiple” command with the point command to bring the points in but what about all those attributes that go with those hydrants?

“SIMPLE” Open the file in Excel and use the data connect for an ODBC connection. Here is how you can do this.

We start off with a text file either a CSV (comma separated) or tab delimited plain text. Depending on how it’s formatted as to how you open it in Excel, for my example I have a CSV file like so.

clip_image002

Notice the first line has the definition of each column or the label for each and the text is separated by commas. If you do not have the labels or definitions in the file that’s fine you can add them after we open it in Excel. Start by opening up the file in Excel, open Excel and go to file open and browse to the file. Excel should open the file just fine.

clip_image004

If you do not have the first row with a label or the description of the column you can add insert a blank row and enter them now. The next thing we need to do is create a named table range of the data. We do this by selecting all the data we need then from the menu Insert > Name > Define.

clip_image006
A dialog will open allowing you to create a name for the range. Give the range a name, it will default to what value is in cell A1, I find that is not very descriptive and causes a few problems down the line.

clip_image008

A few hints on creating the named table range, the help in AutoCAD Map 3D says you can press Ctrl-A to select the data cells but I found this to be a problem at times. First you are selecting EVERYTHING even empty cells from empty columns and rows. If there is an empty cell in the first row you may not be able to select the X-Y columns when you connect to the data in Map. So select the left top cells then scroll to the bottom of you data and shift-select the bottom right cell you need. If there is more data in the file than what you need then just select the data you need.

Next save the Excel spreadsheet as a Microsoft Excel 97/2003 & 5 Workbook with the xls file extension and close it.

clip_image010

The next step is creating an ODBC Data Source for the Excel file we just saved. Depending on your operating system as to how to get to the ODBC Data Source Administrator but with most of them you can get to it from the Windows Control Panel > Administrator tools.

clip_image012

Create a new Data Source by clicking on the “Add..” button in the dialog.

clip_image014

Select the Microsoft Excel Driver (*.xls), then click on finish. Provide a name for the data source, select the correct version then select the workbook to use by click on the “Select Workbook” button and browse to the Excel file you saved in the first step.

clip_image016

OK out of the dialog boxes and close the Administrator tools and any other dialog boxes that may still be open.

Open up AutoCAD Map3D and use the data connect to open the data connect palette. Select the Add ODBC Connection provider, then click on the file button and select the DSN name you created. Then click the connect button.

clip_image018

The login dialog box will appear, unless you placed restriction on the Excel file just click on “Login” with a blank user’s name and password. Back in the Data Connect palette you should now have the Excel file listed with the named table range you created as the FDO Schema. You can now select the named columns to use for the X & Y (& Z) insertion for the points. You do this by clicking in the blank space under each column. (You can drag the X & Y columns to make them wider if you need to.

clip_image020

After you click under the X column a drop down list box appears to allow you to select the column to use for the X & Y coordinates.

clip_image022

Select the columns that contain the X and Y coordinates in your Excel file. Then click on the “Add to Map” button.

Now you should have some points in your drawing with all the attributes that is in the text file.

clip_image024

Now if you need to use the points in another map or drawing export the ODBC layer to an SDF format file and you can archive the original text and Excel file, you don’t need them any longer.

About these ads

9 Responses to “Bring a CSV file into MAP 3D with data.”


  1. 1 asud09 05/19/2009 at 5:53 AM

    Hi,

    I can’t get this to work for me. It just keeps bringing up an error message and saying ‘no such file’. I must be doing something wrong, but I don’t have any ODBC experience, so I can’t say where I’m going wrong. Is there another way of doing it?

    I’m using Map 3D 2009. My xls file isn’t complex, it just consists of 3 columns, X, Y and number. All I want to do is show the X,Y as a point and the number as text. I have about 340 points with numbers and I don’t want to enter them by hand.

    Please can someone help me? Thanks

  2. 2 map3d 02/21/2009 at 9:01 PM

    Sinsonte,
    You can do a bulk copy of the SDF file to a SHP file. Create a new folder in your c: directory, then make a SHP connection to that folder. Then use the bulk copy function to create the shp files. The key to getting it to work is the folder must not have any files in it. Another method is use the FDO2FDO application from SL-king.com.

  3. 3 sinsonte 02/19/2009 at 2:11 PM

    Does anybody know of a way to make an ERSI shapefile from the resulting SDF?

  4. 4 dd4kids 12/02/2008 at 4:44 PM

    Thank you for the good description!

    I have done everything, but after clicking the “Add to Map” Button, the programm gives me the warning: RDBMS:[Microsoft][OSBC Excel Driver] The Microsoft Jet-Modul could not find the Objct “My_Field_Name” … (I have the germany version, the above message is translated)
    Any idea what I coud have done wrong?

  5. 5 cadgistech 07/09/2008 at 3:50 PM

    Here’s how I do it so that I don’t pollute (and never touch) the ODBC setup in Windows:

    As above, open the Excel or csv file and define a table range before the next steps
    (select Data, Insert, Name, ..)

    In Map/Civil 3D 2009
    – Data Connect -> Add ODBC Connection
    Name it
    Set Source Type to “Connection String”
    Paste in the following relevant line
    (complete the pathname\filename.??? to suit)
    Driver={Microsoft Excel Driver (*.xls)};DBQ=pathname\filename.xls
    or
    Driver={Microsoft Access Driver (*.mdb)};DBQ=pathname\filename.mdb
    (remember to use quotes if the pathname contains spaces)

    Then proceed as above with the definition of the x,y and such.

  6. 6 map3d 07/06/2008 at 5:06 PM

    Charliejack,
    This only works in version 2007 or higher.

  7. 7 map3d 07/06/2008 at 5:05 PM

    The square you see are the default point style used, once you connect to the point file and add it to a map you can highlight it in the Map Display Manager, Right click and “Style” this will allow you set a display style base on a number of criteria. The same thing for viewing the data/attributes, right click and select Data Table.

  8. 8 gilan25 07/05/2008 at 4:13 PM

    Hi!
    Maybe I am missing some points here but ODBC Connection just places some “square objects” in MAP based on first and second columns as x and y. But those objects are not blocks so what are those? Properties window just says MAPBULKFEATURE.
    What about information in other columns in the Excel file, is that a way to transfer those to MAP as attribute with a block?
    DATA TABLE showing in your last slide, how can I make it appeare?
    I am using MAP 3D 2007/8.

  9. 9 charliejack 05/15/2008 at 4:03 PM

    does this work with map 3d 2005 or only with map 3d 2008?

    thanks

    jack


Comments are currently closed.



Archives

Readers Locations

Top Rated posts

Chat room


Follow

Get every new post delivered to your Inbox.

Join 30 other followers

%d bloggers like this: